Summarizing with 1-many relationship files

[dkaib]

I have 4 files that have a 1-many relationship.

The 1-side of the relationship is a file called Events with the primary key being Event ID and Event Date.

There are 3 files in the many relationship.

Event Contributions: Event ID, Event Date, Contribution ID, Contribution Amount.

Event Receipts: Event ID, Event Date, Receipt ID, Receipt Amount.

Event Expense: Event ID, Event Date, Expense ID, Expense Amount.

I’m trying to create a single query that will summarize by Event ID and Date, Total Contributions, Total Receipts, Totals Expenses and calculate Profit/Loss.

The problem is I’m getting the wrong totals when summarizing and I think it has to do with that fact that each of the many-side files may not contain a record for that Event ID and Event Date.

Also, all the Event ID/Event Date Combinations are not being displayed unless there is a record in each of the many-side files

If I change the Join Properties to #2 include all records from the Events file and only those records from the many-side file where the joined fields equal, I get the following message when trying to execute the query:

The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in you SQL statement.

I have no idea what direction the above statement is pointing me.

Any help would be greatly appreciated.

Thanks In Advance,
Dan

 

[Jon K]

Double-click on each of the six joining lines to make sure that you have included all records from the Events table.


To arrive at the correct Profit/Loss, you may also need to use the Nz() function in the three Sums to convert any null values to zeroes. The SQL statement of the query should look like this:-

SELECT Events.[Event ID], Events.[Event Date],
Sum(Nz([Event Contributions].[Contribution Amount])) AS [Total Contribution],
Sum(Nz([Event Receipts].[Receipt Amount])) AS [Total Receipt],
Sum(Nz([Event Expense].[Expense Amount])) AS [Total Expense],
[Total Contribution]+[Total Receipt]-[Total Expense] AS [Profit/Loss]
FROM ((Events LEFT JOIN [Event Contributions] ON (Events.[Event Date] = [Event Contributions].[Event Date]) AND (Events.[Event ID] = [Event Contributions].[Event ID])) LEFT JOIN [Event Expense] ON (Events.[Event Date] = [Event Expense].[Event Date]) AND (Events.[Event ID] = [Event Expense].[Event ID])) LEFT JOIN [Event Receipts] ON (Events.[Event Date] = [Event Receipts].[Event Date]) AND (Events.[Event ID] = [Event Receipts].[Event ID])
GROUP BY Events.[Event ID], Events.[Event Date];

 

[Jon K]

Yes, Pat's right.

The single query gives incorrect results. As the Contribution, Receipt and Expense tables link to each other besides linking to the Event table, duplicate records are produced. Hence the three Sums are incorrect.

 

[dkaib]

Thanks Jon and Pat for the replies.

I'm new to Access so my problem now is where or how do you join the 3 many-side queries to the Events table?

Is it done through Queries, Tables or Relationships?

Thanks,
Dan

 

[dcruz]

Pat and Jon have the right idea. Each of your three queries must contain a unique value. It appears that the Event ID is unique to each of these instances. So if you have the Event ID displayed in each of the three queries, you can then join them based on that value.

Donna

 

[dkaib]

Thanks Pat, Jon and Donna for all your help.

That did the trick.

Dan