Count by user & month

[Daniel Egan]

Hello,

I have long data of the sort:


User Date
A 30/01/2009
A 30/02/2009
A 30/03/2008
B 30/02/2009
B 30/01/2009
C 30/03/2009

I need to get a count of User by the month, i..e.
01 02 03
A 1 1 1
B 0 1 0
C 0 0 1
When I run the query, it appears to count all users, not just A/B/C,


Help!

 

[MGFoster]

Hello,

I have long data of the sort:


User Date
A 30/01/2009
A 30/02/2009
A 30/03/2008
B 30/02/2009
B 30/01/2009
C 30/03/2009

I need to get a count of User by the month, i..e.
01 02 03
A 1 1 1
B 0 1 0
C 0 0 1
When I run the query, it appears to count all users, not just A/B/C,

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It would help if you posted your query.

I'll assume you used a crosstab query like this:

TRANSFORM COUNT([Date]) As theValue
SELECT User
FROM table_name
GROUP BY User
PIVOT Format(Month([Date]),"00")

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSj0PxYechKqOuFEgEQLFFQCg0gf7eBYVO1EItgSncjqbjDIydKoAoMJT
uLR2io4Im22zdM0+U6hwEM5y
=tmur
-----END PGP SIGNATURE-----