Weekly Totals

[Chookz]

Hi All,
Im very new to Access so please forgive my ignorance.
I have been working on converting my excel databases into access.
I'm trying to create a query that will give me weekly total amounts for individuals. The query would look like the following - columns separated by ;

Person; WeekBeginning; Amount
Person A; 1/1/14; $1000
Person B; 1/1/14; $1200
etc.

For the Person column I have a table with all people and their personal details (DOB, location etc). Each person is assigned an ID. This table is called Staff.
For WeekBeginning I have a table that contains each day of the year in one column and the week beginning date in another column (although not sure if this is the best way).
For the Amount column I have 3 separate tables where people have earned amounts. These tables have the persons name, ID, amount and PaymentDate. For simplicity let's call these tables Earnings1, Earnings2 & Earnings3.

So I want to run a query that states the persons name (i guess from the staff table), the week beginning (so the week beginning for each payment date from Earnings 1, 2 & 3 tables) and the sum of amount for that week (from Earnings 1, 2 & 3 tables).

So ive tried to make the query and different relationships but keep getting errors such as ambiguous outer joins or incorrect data.
So given that im very new to Access, I am wondering if anyone can assist me with the best way to setup my relationships and arrange/normalise the data to make this and future queries run smooth? I have an excel background so am ok with the calculations to get the sums of the amounts, but am struggling with the DB setup. Any guidelines, info you can give would be much appreciated

I'm happy to make new tables and/or queries for dates or staff or whatever you think needs doing.
I hope that's enough info,

Cheers

 

[ranman256]

You seem to have it...join person tbl to payments and sum. But why did you do an outer join? A regular inner join will sum the hours without error.

 

[Chookz]

You seem to have it...join person tbl to payments and sum. But why did you do an outer join? A regular inner join will sum the hours without error.

But I need sum of payments per week. Even if they received 0 payments that week, I still need the week beginning to show up with a 0. So im not sure the best way to link the person table, the 3 earnings table and a date/week beginning table. In the earnings table the payments are only listed for the day of payment, not the week. Does that make sense?
eg. Earnings1 table might show J.Smith; 17/9/14; $100
Earnings2 table might show J.Smith; 18/9/14; $150
Earnings3 table shows no data from J.Smith

So I need my query to show:
J.Smith; 15/9/14 (the week beginning date); $250