How to get MS Access to group by date column

Question

I'm having difficulty grouping by date in Microsoft Access. Any guidance would be great. Thank you.

Fake Table - schedules:

Date	Location	Appointment ID	People
1/3/23	East	98765	3
1/4/23	West	983746	2
1/3/23	East	09382	5

Query I'm using:

SELECT schedules.Date, schedules.Location, COUNT(schedules.[Appointment ID]) AS [Appointment Count], SUM(schedules.[People]) AS [People Served]
FROM schedules
GROUP BY schedules.Date, schedules.Location;

I'd like the following table:

Date	Location	Appointment Count	People Served
1/3/23	East	2	8
1/4/23	West	1	2

BUT my code won't allow grouping on the date for whatever reason. It's a short date without the time. With my code, the first table is returned back to me. If I omit the date, it will group by location.

Answer 1

Base your groups on DateValue(<your Date/Time field>) to group all records from the same day together regardless of their time component.

SELECT
    DateValue(s.Date),
    s.Location,
    COUNT(s.[Appointment ID]) AS [Appointment Count],
    SUM(s.[People]) AS [People Served]
FROM schedules AS s
GROUP BY DateValue(s.Date), s.Location;