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

[magicdds-]

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

 

[magicdds-]

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