query with grouping and subqueries not working

[BillR]

Imagine data from a camera looking at a highway, and when a vehicle goes by it records 2 fields: the datetime and whether the camera thinks it's a car, truck or something else, say "CAR", "TRK" or "OTH". What query would give me a date, the total number of vehicles and counts for each type for that date? I'm trying to get the subgroups using subqueries and can't get the syntax working. Thanks in advance.

[pbaldy]

Sounds like a totals query. Start a new query and add the date field and the type field twice. On the ribbon, click on the Totals icon, looks like an "E". Change the Group By to Count for one of the type fields. Oh, and wrap the date/time field in the DateValue() function to return just the date.

[Micron]

With that, I think you'll only get a count of the types by date, not a count of the total for a date. The type will break it out into groups but I think the OP wants a total count by date. You should be able to get the counts of type for a date using a Totals query as suggested. Then add that query to a second, joined to the table by date and count the date. Such as

SELECT tblAutoTypes.TheDate, tblAutoTypes.TheType, Count(tblAutoTypes.TheType) AS CountOfTheType
FROM tblAutoTypes
GROUP BY tblAutoTypes.TheDate, tblAutoTypes.TheType;

SELECT Query16.TheDate, Query16.TheType, Query16.CountOfTheType, Count(tblAutoTypes.TheDate) AS CountOfTheDate
FROM Query16 INNER JOIN tblAutoTypes ON Query16.TheDate = tblAutoTypes.TheDate
GROUP BY Query16.TheDate, Query16.TheType, Query16.CountOfTheType;

The first query is just named Query16.

[BillR]

Thanks! I'll give them a try.