Get all customers listed in a query, even with zero balance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

magicdds- - 20 Apr 2008 17:39 GMT

I have a form based on a query. The query is made from the following tables:
Customers
Orders
Payers
ChargePerPayer

Each customer (Customers) has a number of people who might pay part of the
bill for an item the customer purchases (Payers).

Each order, placed by a customer, in the Orders table, has a number of
Payers from the Payers table. Those payers that will be paying towards a
particlar order have a charge listed in the ChargePerPayer table.

The query that I have now is when I specify a specific OrderID#, those
payers with a record in the ChargePerPayer table are returned, with a column
showing what each payers charge is (FeePart).

What I need to happen is that for a specific OrderID#, all payers that are
associated with the customer that placed that order need to be returned.
Those that have a record in the ChargePerPayer table should show the amount
that payer will be paying(FeePart). If however, there is not a record for
that payer, for this orderID, in the ChargePerPayer table, then FeePart
should return "0".

I can't figure out how to get all the payers listed and return 0 for those
with no record. Can anyone help me figure this out?

Thanks
Mark

Reply to this Message

Marshall Barton - 20 Apr 2008 18:50 GMT

>I have a form based on a query. The query is made from the following tables:
>Customers
[quoted text clipped - 22 lines]
>I can't figure out how to get all the payers listed and return 0 for those
>with no record. Can anyone help me figure this out?

Select the connecting line between the payer's table and the
charges table. Right click and select Join Properties in
the popup menu. Choose the Show all records in the payers
tabl and any matching records from the charges table.

That will leave Null values in the fee field. This is
normally sufficient, but if you really need to display a 0
then change that field in the query from FeePart to
Nz(FeePart , 0)

Signature

Marsh
MVP [MS Access]

Reply to this Message

magicdds- - 20 Apr 2008 20:50 GMT

Marshall,

My joins look as follows:

1 to many 1 to many
Customers ------------------Charges------------------------ChargePerPayer
|
|
|-----------------------Payers-----------------------------|
1 to many 1 to 1

There is currently no join between the Charges table and the Payers table.
If I make a join on CustomerID between the Charges table and the Payers
table, the query gives me the same results (Results: 2 of the 4 payers for
the customer are listed in column 1, the chargeperpayer is listed in column
2, the 2 payers for this customer who don't have to pay anything for this
order are not listed at all.)

If I then change the join that I added to show all records in the payers
table and matching records in the charges table, I get an error message about
ambiguous outer joins.

Any ideas on how to fix this?

Thanks
Mark

> >I have a form based on a query. The query is made from the following tables:
> >Customers
[quoted text clipped - 32 lines]
> then change that field in the query from FeePart to
> Nz(FeePart , 0)

Reply to this Message

Marshall Barton - 21 Apr 2008 16:37 GMT

>My joins look as follows:
>
[quoted text clipped - 15 lines]
>table and matching records in the charges table, I get an error message about
>ambiguous outer joins.

I don't fully understand those relationships. What is the
dangling 1-1 relationship from all payers to ??

Seems to me that the customers and players need to be inner
joined before anything else.

I think you also need to inner join the charge per payer and
charges.

Then you should be able to outer join those two datasets to
get what you want.

Because there are some restrictions on nesting inner and
outer joins, you will probably need to use two or three
separate queries.

Signature

Marsh
MVP [MS Access]

Reply to this Message