Monthly Select

  • Thread starter Thread starter Peter Carlson
  • Start date Start date
P

Peter Carlson

I have a query that returns a simple list like so:

Name, Total

Now I'd like to expand that query to do the same by month. I need to
query to look like two different things:

Month, Name, Total
Jan...
Feb...
etc

and
Name, Total, Jan, Feb, Mar...

The query for the first report looks like:
SELECT last, first, middle, count(IP.Actions1) AS total,
Format(I.AlarmDate,"mmm") as Month
FROM ((People AS P LEFT JOIN Incidents_Personnel AS IP ON
IP.Person=P.id) LEFT JOIN xPersonnel_Status AS S ON P.status=S.id) LEFT
JOIN Incidents AS I ON I.id=IP.IncidentNumber
WHERE P.member=1 And S.active=1 And I.AlarmDate Between #1/1/2006# And
#12/31/2006#
GROUP BY Month, last, first, middle
ORDER BY Month, last, first, middle;

But the Format() clause gives me "You tried to execute a query that does
not include the specified expresion 'Format..' as part of an aggregate
function.

And I have no idea on the 2 query at all.

Thanks!

Peter
 
You need to Group By and Order By Format(I.AlarmDate,"mmm") instead of month.

For the second problem, look into crosstab queries.
 
Back
Top