Having Problems with ambiguous outer joins

[drgn_str]

I have 3 tables:
T1:Client Info- Fields: FamilyID, other fields related to client info

two other tables are linked to the Client Info table

​

T2:Payment_Adjustment- Fields: FamilyID, Amount, other fields related to payments.

​

T3:Charges -Fields: FamilyID, FTFee, PTFee, FTDaysCharged, PTDaysCharged, other fields related to charges.

They are all linked by FamilyID via multiple Payment_Adjustment and Charges table to every one Client Info table.

In a Query, I am trying to combine the tables to get a Client_Balance by using this formula:ClientBalance: Sum(([Charges]![FTFee]*[Charges]![FTDaysCharged])+([Charges]![PTFee]*[Charges]![PTDaysCharged])-[Payment_Adjustment]![Amount])

I don't have any data in the database yet, so I don't know if that is causing the problem, or if it is the formula, or I am just trying to do too much at once. Every time I try to run the query as a test I get the following error: The SQL statement could not be executed because it contains ambiguous outer joins.

I have spent two days trying to solve this with no luck. Thanks in advance for any advice you have.

 

[vbaInet]

The problem is the joins between the tables. If you have a Left or Right Join, the join preceding that must follow in the same direction. Look at the lines linking the tables, those arrows must point towards the same direction.

 

[drgn_str]

I guess I still don't understand joining then.

I have the Client Info table as the parent table and both the Charges and Payment_Adjustment tables as child tables and both of them are pointing towards the parent table using FamilyID as the link.

Should I have them set up in a more linear format such as Payment_Adjustment linked to Charges and then have Charges linked to Client Info?

 

[boblarson]

Post a screenshot of your query where the tables and the links are shown.

 

[drgn_str]

It is a one-on-one relationship but when I go into Joint Type I have selected the 2nd option for each of the child tables: "Include all records form 'Charges' and only those records from 'Client Info' where the joined fields are equal."

I had tried to do a one to many relationship but Access will not give me that option. I think because I have the FamilyID in each table and using that as a link?

 

[boblarson]

Okay, the problem is that you have the links going the wrong way. They should be coming from the clientInfo table and pointing to the other tables.

 

[drgn_str]

Okay so I need to have the Join Type: "Include all records from 'Client Info' and only those records from 'Charges' where the joined field are equal."?

 

[boblarson]

Okay so I need to have the Join Type: "Include all records from 'Client Info' and only those records from 'Charges' where the joined field are equal."?

Yes, that is correct.

 

[drgn_str]

Thank you!!!!!!!!!!!

 

[boblarson]

I suppose I should also include the WHY for you so you can learn for the future as well.

The reason why you want the outer joins to begin with is if you want to include something in the table where the link is pointing away from (in other words, select all from this table) and then you want any information from the other table or tables that matches up for that something in the first table (and only records that match in other table).

So, if there is no data for that person, for instance, you will still get that person shown where they wouldn't if it was a straight INNER JOIN.