Count query help

[Opal]

I am using Access 2003 and I am having a problem
counting by date the number of instances in a table:

I am using the following SQL:

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;

The results I am getting are:

Date ACount BCount
4/27/09 586 270
4/24/09 584 269
4/23/09 583 269
.....etc

What I want is:

Date ACount BCount
4/27/09 2 1
4/24/09 1 0
4/23/09 1 1
.....etc

Could someone please help me get this query right?
Thank you!

 

[Michel Walsh]

TRANSFORM COUNT(AbsenceCategory)
SELECT [date]
FROM historyTimeAndAttendance
WHERE absenceCategory="SUPR ONLINE"
GROUP BY [date]
PIVOT [shift] & "Count"




Vanderghast, Access MVP

 

[KARL DEWEY]

Try this --
SELECT HistoryTimeAndAttendance.Date,
Sum(IIF([Shift] = "A" And [AbsenceCategory] = "SUPR ONLINE",1,0) AS ACount,
Sumt(IIF([Shift] = "B" And [AbsenceCategory] = "SUPR ONLINE",1,0) AS BCount
FROM HistoryTimeAndAttendance
GROUP BY HistoryTimeAndAttendance.Date
ORDER BY HistoryTimeAndAttendance.Date;

 

[Opal]

Thank you Michel....I could not get my head around
the cross tab query. I thought that was the way I
needed to go, but could not get it to work right so
I tried the DCount. This works just as I wanted it
to. Thank you, again!