SUM OR TOTAL Q

[Guest]

If I have a table for the month of June that has:
Citation #, Officer, Date, Time, Race, Sex, Search
I want to run a query that will group the officers and give me a total of
citations written by the officer, then give me a total for race and sex
(example for smith)
June 2006
Officer race sex Total Citations Written Total Race Total Sex
Smith m w 22 5
5
Smith m b 10
10
Smith f w 5
5
Smith f b 2
2

 

[Jeff L]

In your query design, output the officer name, race, sex,
DCount("Officer","YourTableName"), Race, Sex.

Now click View, Totals. You should now see a Totals row down where
your fields are. In the first three columns, put Group By. In the
DCount column, Expresssion. In the last two, put count. That should
give you the results you are looking for.

Hope that helps!

 

[Guest]

Hopefully you don't have a table per month. That would be very bad. The Total
Citations Written can come from a report if you use sorting and grouping on
the Officer. Also it seems to me that Race and Sex will always have the same
number in each row.

SELECT Officer, Race, Sex, Count(Race), Count(Sex)
FROM YourTable
WHERE Month(YourTable.Date) = 6
AND Year(YourTable.Date) = Year(NOW())
GROUP BY Officer, Race, Sex
ORDER BY Officer, Race, Sex;

 

[Guest]

Sum() might work better than Count().