Multiple Aggregate Functions

[Public]

Hi,
I am facing a problem of creating multiple aggregate functions in the same
query. What I want is like to get the number of courses for a certain month
and I have a field that gives me the date of a course. So what I am doing now
is creating a separate query for each month. For example for Jan I have like
Select Expr1: Count(CourseID) FROM Courses WHERE month(coursedate)=1. And
then another query for the Feb, Mar etc. Then I am combining all these months
into a single query and from this queryI am creating a report.

With this solution I am facing two problems:

1) I have to create a query for each and every month and then combine them
2) I want to show in the report, all the courses with their months, grouped
by year and I am not able to do so, since each month is coming from a
different query. The solution that have now, is to show the user a list of
years, he selects one and I am taking that year in query and I genrate report
based on that. (So I am only showing one year at a time)

Any ideas to solve any of these problems?

Regards

 

[Michel Walsh]

You can use a crosstab (for a query) PIVOT on the month to get 12 new fields
(one per month) and GROUP on the years.

Vanderghast, Access MVP

 

[Public]

I didn't get your point. Could you please explain what do you mean by
crosstab and PIVOT?

Regards

 

[John Spencer]

Group on the date, but format it.

Field: YrMonth: Format([NameOfTable].[SomeDateField],"yyyymm")
Table: <<Blank>>
Total: Group By

IF you want to specify a range of dates then add another column with the date
Field: SomeDateField
Table: NameofTable
Total: WHERE
Criteria: Between #2007/01/01# and #2007/12/31#

If you want to display the Date in month name fashion then add another field
FIELD: ShowMonth: Format([NameOfTable].[SomeDateField],"mmmm yyyy")
Table: <<Blank>>
Total: Group By


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Michel Walsh]

TRANSFORM COUNT(*) ' crosstab, COUNT(*) = value in the 'cells'
SELECT Year(CourseDate) ' we will groups on those
FROM courses
GROUP BY Year(CourseDate) ' groups (making rows)
PIVOT Month(CourseDate) ' pivot : make a new column for each
value the expression will take




Vanderghast, Access MVP