Crosstab totals

[Lobster1071]

I have looked around this forum and can't seem to find exactly what I want, but if anyone can link a thread instead of rehashing what has already been said, please do so...

I have what I think is the simplest of crosstab queries set up, and would simply like the horizontal totals of the values printed. This is ultimately going to be in a report, so if it has to be done there (maybe with a DCount function) that is fine too.

I have the Crosstab query set up fine, I am just having a problem with totals to the right. The totals simply calulate the meeting status of the people throughout the year.

Here is basically the situation and what is needed. Names are in the rows, months in the colums, and meeting codes (A, E, or P)

Name...| January | February | March | A total | E total | P total
--------|--------|----------|-------|--------|---------|-------
John.....|....P.....|.....E........|.....P.....|....0.....|.....1.....|.....2.....
Mike.....|....E.....|.....P........|.....E.....|.....0....|.....2.....|.....1.....
Harry....|....P.....|.....A.......|.....A.....|.....2.....|.....0.....|.....1.....
Paul.... |....E.....|.....E........|.....A.....|.....1.....|.....2.....|.....0.....


The current code I have is here:

TRANSFORM First([Meeting Report1].[Meeting Code]) AS [FirstOfMeeting Code]
SELECT [Meeting Report1].Name, Count([Meeting Report1].[Meeting Code]) AS [Total Of Meeting Code]
FROM [Meeting Report1]
GROUP BY [Meeting Report1].Name
PIVOT [Meeting Report1].[Meeting Month];

The current "Total Of Meeting Code" field gives the total number of codes found throughout the year, which will be 12 for every person (which is what I don't want). What is needed is the total number of A's, E's, and P's for each person.

Anyone with a simple solution? (either in a query or report)

Thanks.

 

[apr pillai]

TRANSFORM First([Meeting Report1].[Meeting Code]) AS FirstOfCode
SELECT [Meeting Report1].Name, Sum(IIf([Meeting Code]='P',1,0)) AS PTotal, Sum(IIf([Meeting Code]='E',1,0)) AS ETotal, Sum(IIf([Meeting Code]='A',1,0)) AS ATotal
FROM [Meeting Report1]
GROUP BY [Meeting Report1].Name
PIVOT [Meeting Report1].[Meeting Month];

 

[Lobster1071]

That's amazing. It looks so easy, but yet I never would have figured that out.

It was exactly what I needed, and works perfectly.

Thank you so much!