Aggregating Data Query Help Needed

[MissyEd]

Hi all

I am trying to create a report that sums the daily data we currently have by week. I have 3 tables:

1. OFFICE_DETAILS
OFFICE_NUMBER e.g 1, 2, 3
AREA_MANAGER e.g J.Hammon, M.Seline, U.Glock
REGION_NAME e.g Greater Manchester, Lancashire

2. EXPENDITURE_LIST
PAID_DATE e.g 12/01/01, 13/01/01
OFFICE_NUMBER e.g 1, 2, 3
PAID_AMOUNT e.g £10, £25.50, £18

3. WEEK_NUMBERS
WEEK_NUMBER e.g 18,19
WEEK_ENDS e.g 14/01/01, 21/01/01

I have to create a report that lists the amount spent grouped by week. So it would look something like this:

Month: Jan/2001
Area Mgr Office Wk1 Wk2 Wk3 Wk4 Wk5 Total
xxx xxx 9.99 9.99 9.99 9.99 9.99 99.99
Grand Total 9.99 9.99 9.99 9.99 9.99 99.99

I created a select qry to link tables 1 and 2, but am stumped on how to link table no 3 and summarise. Any ideas ? Missy Ed
Looking to exchange ideas and tips on VB and MS Access development as well as office 97 development. Drop me a line: msedbbw@hotmail.com

 

[MichaelRed]

I don't see any reason for table three.

All it holds is 'information' which is readily calculated. Although the Week_Number is obviously the week number for a project as opposed to the Julian calendar, You can still calculate the number of weeks from the start date of the project to any given date. So, it would appear that - given the project start - this table is not really useful, making the joining of it to the remainder of your query un-necessary.

Also, why your weeks end on Wednesday is a bit obscure, however this also may be accomodated, by setting the "first day of week", if you really need it to be this way.

So, I would just add a weeknumber calculated column to the query you already have (the join of tables 1 & 2) and then create the aggregats query based on this.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MissyEd]

Thanks for the help Michael. I've got it working OK now

The week_ends dates are actually Sundays (forgot that the Americans have dates the other way round again!) and represent the end of a financial week. Basically, all paid outs have to be grouped by financial week, but you were right in saying the table is not necessary.

One down, and 17 more reports to go Missy Ed
Looking to exchange ideas and tips on VB and MS Access development as well as office 97 development. Drop me a line: msedbbw@hotmail.com