Suppose I have the following LoginInfo table.
LoginInfo
| UserID (Short Text) | LoginTime (Date/Time) |
|---|---|
| ... | ... |
| UserA | 2024/03/31 ... |
| UserA | 2024/03/31 11:22:23 |
| UserA | 2024/03/31 19:23:59 |
| UserB | 2024/03/01 09:13:53 |
| UserC | 2024/04/01 08:23:02 |
| UserA | 2024/04/02 ... |
| ... | ... |
I want to tally up the number of unique UserID (active users) for each day for the month of March, 2024 (2024/03/01 - 2024/03/31). How would I write an MS Access SQL to do that in an ascending order. In other words the end result will be something like
ActiveUserMar2024
| ActiveUsers (Integer) | LogDate (Short Text) |
|---|---|
| 20,500 | 2024/03/01 |
| 18,348 | 2024/03/02 |
| ... | ... |
| 125,121 | 2024/03/31 |
Also it seems that Date/Time data type for MS Access does not support milliseconds (SSS in YYYY/MM/DD HH:mm:ss.SSS)
Return DISTINCT date and user then aggregate that data. Use a nested subquery. For the data sample given, there is 1 unique user per day.
SELECT Count(*) AS ActiveUsers, LogDate
FROM (
SELECT DISTINCT UserID, DateValue(LoginTime) AS LogDate FROM LoginInfo)
WHERE Format(LogDate,"yyyymm")=202403
GROUP BY LogDate;