Query Complexity 1

[kylebellamy]

I need to sort data in Access 2003 so that I get the following breakdown:
Section Month Total

There are 12 sections and I need a monthly breakdown for each one. I've tried to puzzle this out for myself but I can't seem to get it. The only other thought I had was to generate each month's worth and then display them all somehow in a report or something but I haven't had teh chance to persue that line of thought.

Any help would be greatly appreciated!

http://www.kb-design-studios.com

 

[Spenney]

Take a look at 'Group By' in the help files.

I guess a query something like this is what you're after..

SELECT yourtable.section, yourtable.month, Sum(yourtable.value) AS Total
FROM yourtable
GROUP BY yourtable.section, yourtable.month;

 

[kuau]

Dear Kyle:

Not exactly sure what you mean, but I did a monthly total select query by grouping on the date column [PayDate] using this as the definition of the date field:

Month: Format([PayDate],"yyyy/mm")

then using "Sum" on the amount columns.

Hope this helps. kuau

 

[kylebellamy]

I've been trying to use the group by statement but the output is not correct (generally it's nothing at all). Or what I get is this:

Section Month Total
Technology 8/12/2005 3
Technology 8/15/2005 2

And so on. What it should look like is this:

Section Month Total
Technology January 32
February 85
August 134
HR January 35

etc, etc

http://www.kb-design-studios.com

 

[kuau]

Month: Format([PayDate],"yyyy/mm")

produces:
2006/01 32
2006/02 85
2006/03 67

This will give you the totals you want in the query and then you use the query as the data source for a report where you can format the dates to display as January, February etc.

Group by section as well and it should give you your report.

 

[kylebellamy]

Brilliant! That's what I needed.

http://www.kb-design-studios.com