retrieving sum of various sales and expenses from multiple tables, using date as a criteria

[beanie]

Hello,
I am new to MSA2013 and have difficulties in creating the query that does what I want.

I want to retrieve a sum of sales and expenses that I have recorded in various tables.

The tables that I used are as of below

1) Primary table
Table name: USERS
This table contains following:
a) a unique key reference (user ID)
b) Last name of the user
c) First name of the user

2) Secondary table I
a) Link to the unique key reference of the USER ID in the Primary table
b) Date field with short date (YYYY/MM/DD)
c) Payments received (Currency)

3) Secondary table II
a) Link to the unique key reference of the USER ID in the Primary table
b) Date field with short date (YYYY/MM/DD)
c) Expenses (Currency)

4) Secondary table III
a) Link to the unique key reference of the USER ID in the Primary table
b) Date field with short date (YYYY/MM/DD)
c) Donations received (Currency)

The secondary tables (I & II & III) are used to record payments received, Expenses and Donations, that a certain user has been linked to.

The query that I want to create shall retrieve only the sum of each secondary table that match a certain date. The query shall not list users that have received payments, recorded expenses or received donations on a certain date. The query shall only return the sum of each secondary table that matches a certain date.

For examples below

Secondary Table I
Date USER ID Payment received
2015/03/01 1 10.00
2015/03/01 4 15.00
2015/03/10 7 20.00

Secondary Table II
Date USER ID Expenses
2015/03/01 2 5.00
2015/03/01 8 10.00
2015/03/05 12 30.00

Secondary Table III
Date USER ID Donations
2015/03/01 3 20.00
2015/03/05 6 5.00
2015/03/10 9 15.00



So when the query is created following should happen.
1) I start the query and a dialog box pops up "Please enter a date"
2) I enter for example, 2015/03/01, then the query should return below

Payments received 25.00
Expenses 15.00
Donations 20.00
SUM 60.00

If I would enter 2015/03/05 the result should be:
Payments received 0.00
Expenses 30.00
Donations 5.00
SUM 35.00

Could anyone please instruct me on how I should create this query in MSA? I have tried many times, but the result returned is not correct. The query seems to return correct totals only when all three tables contains a value on the entered date, or the query returns an empty result when one of the table does not have a match for the requested date (but the two other has).

I would be so happy and grateful for any help that I can received from your smart users out there on this forum.

[June7]

Options:

1. queries that summarize the secondary tables then join those queries to the primary table.

2. form/subforms or report/subreports

[beanie]

Hi Thank you very much for your reply.

As for option one, I have now succesfully created queries, but I do not know how to join those queries to the primary table. How do I do this?

[June7]

Use queries same as tables in other queries.

What are those queries? Are they aggregates that group on user and date? Join on both user ID and date fields - compound link.