Counting in Queries

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Hi All,

I've got to pull counts of records based on multiple criteria from a table.
The table is not normalized due to constraints imposed by our client. The
table looks something like this:

REGION CENTER DATE1
r1 c1 1/24/04
r2 c1 2/24/04
r2 c2 3/24/04
r2 c2 3/25/04
etc...

What I need to do is to populate a report that will count the number of
records for a particular month and display the totals grouped by REGION,
CENTER, then MONTH, like so:

r1
c1
January 1
February 0
March 0
....

r2
c1
January 0
February 1
March 0

r2
c2
January 0
February 0
March 2

Any tips on where to start? I'm using Access 2000.

Thanks,

Tony
 
Hi,


SELECT Region, Center, COUNT(*)
FROM table
GROUP BY Region, Center



Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

Thanks for the response. I've got that part without any problems, including
adding criteria of Month([DATE1])=1 to count the number of records for
January. What I really need to do is to extend this to the balance of the
year. Do you think I'll need 12 queries, one for each month, or is there a
more economical way to get the totals out?

Thanks,

Tony
 
Hi Michel,

Just wanted to let you know that I think I've got it figured out. I used
Access's wizard to create a cross-tab query and everything came out as I
wanted. So, it looks like I'm on my way.

Again, thanks for your response & assistance.

Take Care,

Tony
 
One UNTESTED QUERY

SELECT REGION, Center, Format(Date1,"mmmm yyyy") as MonthYear, Count(*) as CountEm
FROM Table
GROUP BY REGION, Center, Format(Date1,"mmmm yyyy")
ORDER BY REGION, Center, Format(Date1,"YYYYMM")

Hi Michel,

Thanks for the response. I've got that part without any problems, including
adding criteria of Month([DATE1])=1 to count the number of records for
January. What I really need to do is to extend this to the balance of the
year. Do you think I'll need 12 queries, one for each month, or is there a
more economical way to get the totals out?

Thanks,

Tony

Michel Walsh said:
Hi,


SELECT Region, Center, COUNT(*)
FROM table
GROUP BY Region, Center



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Back
Top