1

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
dat.a.tho
  • 59
  • 7
  • Field is formatted to display as Short Date, however, time component could still be in the actual value. Format property does not alter data. Remove the format from field in table and see what values show. Grouping by date works for me. Date is a reserved word. Would be better not to use reserved words as names. Also, advise not to use spaces in naming convention. – June7 Feb 09 '23 at 02:40
  • @June7 Thanks! I'll remove the formatting. It's not actually named 'Date', just used that here for an example. I agree with the naming convention unfortunately my boss does not. thx – dat.a.tho Feb 09 '23 at 14:09
  • Why does boss care about field names? You make labels show whatever you want. – June7 Feb 09 '23 at 19:51
  • @June7 I was under the impression that the columns in the access database have to match exactly to new excel files that are coming in otherwise they won't import correctly. – dat.a.tho Feb 10 '23 at 17:36
  • You didn't mention before that data was Excel import. However, Format setting should not have anything to do with import process. – June7 Feb 10 '23 at 18:58

1 Answers1

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;
HansUp
  • 95,961
  • 11
  • 77
  • 135