Count query

[Opal]

I am trying to create a query in Access 2003 and just
can't quite get it where I want it.....

SELECT HistoryTimeAndAttendance.Date, DCount
("AbsenceCategory","HistoryTimeAndAttendance",
"[Shift] = 'A' And [AbsenceCategory] = 'SUPR ONLINE'") AS ACount,
DCount("AbsenceCategory","HistoryTimeAndAttendance",
"[Shift] = 'B' And [AbsenceCategory] = 'SUPR ONLINE'") AS BCount
FROM HistoryTimeAndAttendance
GROUP BY HistoryTimeAndAttendance.Date, DCount
("AbsenceCategory","HistoryTimeAndAttendance",
"[Shift] = 'A' And [AbsenceCategory] = 'SUPR ONLINE'"), DCount
("AbsenceCategory","HistoryTimeAndAttendance",
"[Shift] = 'B' And [AbsenceCategory] = 'SUPR ONLINE'")
ORDER BY HistoryTimeAndAttendance.Date;

But my results are not what I want....

Date: ACount BCount
4/21/09 586 270
4/20/09 586 270
4/17/09 586 270
.....


Basicall that is the total count in the table,
I need to count by date..... so my results
should look like:

Date: ACount BCount
4/21/09 1 0
4/20/09 2 1
4/17/09 1 0
.....

What am I missing...? I always have trouble
counting in a query.... Thank you!

 

[MGFoster]

I am trying to create a query in Access 2003 and just
can't quite get it where I want it.....

SELECT HistoryTimeAndAttendance.Date, DCount
("AbsenceCategory","HistoryTimeAndAttendance",
"[Shift] = 'A' And [AbsenceCategory] = 'SUPR ONLINE'") AS ACount,
DCount("AbsenceCategory","HistoryTimeAndAttendance",
"[Shift] = 'B' And [AbsenceCategory] = 'SUPR ONLINE'") AS BCount
FROM HistoryTimeAndAttendance
GROUP BY HistoryTimeAndAttendance.Date, DCount
("AbsenceCategory","HistoryTimeAndAttendance",
"[Shift] = 'A' And [AbsenceCategory] = 'SUPR ONLINE'"), DCount
("AbsenceCategory","HistoryTimeAndAttendance",
"[Shift] = 'B' And [AbsenceCategory] = 'SUPR ONLINE'")
ORDER BY HistoryTimeAndAttendance.Date;

But my results are not what I want....

Date: ACount BCount
4/21/09 586 270
4/20/09 586 270
4/17/09 586 270
....


Basicall that is the total count in the table,
I need to count by date..... so my results
should look like:

Date: ACount BCount
4/21/09 1 0
4/20/09 2 1
4/17/09 1 0
....

What am I missing...? I always have trouble
counting in a query.... Thank you!

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

Try this:

SELECT [Date],
COUNT(IIf(Shift='A',1,0)) As ACount,
COUNT(IIf(Shift='B',1,0) As BCount,
FROM HistoryTimeAndAttendance
WHERE Shift IN ('A', 'B')
AND AbsenceCategory='SUPR ONLINE'
GROUP BY [Date]
ORDER BY [Date];

BTW, the column Date should be changed to some more descriptive name,
since Date is also a VBA function and could be confusing. That's why I
put square brackets around it.

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/AwUBSfC9EYechKqOuFEgEQJ2RACfUzEazhNasJpFsRtHOmf9i5NoCH8AoKDw
OdC8Q9eohi6O2nSKcUdarxqs
=AUMY
-----END PGP SIGNATURE-----