Weekly Totals

Chookz

Board Regular
Joined
May 9, 2011
Messages
95
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,173
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

Board Regular
Joined
May 9, 2011
Messages
95
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,597
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top