Pau Lo
asked on
query to identify non-represented days in a date/time column.
I have an extract of data in an access database which essentially represents an access control system, and one of the fields if formatted in date/time, i.e. 03/06/2019 08:27:02 - these represent the times an employee swiped into and out of one of our buildings. What I need is a way of getting a unique list of days where there are no entries whatsoever in the data at all in between a date range of 01/06/2019-31/07/2019 only. So for example if there were entries in the date for 04/06/2019 then that wont be returned in the results as there are entries on that day, but if there are no entries at all for 05/06/2019 then I need that to be returned in my results along with any other unique dates that have no records whatsoever in the table. Can you think of a way of achieving this with a query? I don't want anymore though than the 'missing days' between the range specified if at all possible.
n/b at present its in an access table but we could just as easy export to excel and use that if it will be easier to get the desired result.
n/b at present its in an access table but we could just as easy export to excel and use that if it will be easier to get the desired result.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks!
I generally have a table (tbl_Numbers) in each of my databases. This table contains a single field (lng_number) and 10 records (the values 0-9), and is similar to what you get from Gustav's first query of mSysObjects.
I think I would start out with:
This would get you a list of the days between the start and end dates of your range. Then, you could use that as a subquery to identify the missing dates.
I think I would start out with:
PARAMETERS [CheckStart] DateTime, [CheckEnd] DateTime;
SELECT DISTINCT DateAdd("d", lng_Number, DateValue([yourDateField])) as DateRange
FROM yourTable, tbl_Numbers
WHERE DateAdd("d", lng_Number, DateValue([yourDateField])) >= [CheckStart]
AND DateAdd("d", lng_Number, DateValue([yourDateField])) <= [CheckEnd]This would get you a list of the days between the start and end dates of your range. Then, you could use that as a subquery to identify the missing dates.
PARAMETERS [CheckStart] DateTime, [CheckEnd] DateTime;
SELECT DR.DateRange as MissingDates
FROM (
SELECT DISTINCT DateAdd("d", lng_Number, DateValue([yourDateField])) as DateRange
FROM yourTable, tbl_Numbers
WHERE DateAdd("d", lng_Number, DateValue([yourDateField])) >= [CheckStart]
AND DateAdd("d", lng_Number, DateValue([yourDateField])) <= [CheckEnd]
) as DR
LEFT JOIN yourTable on DR.DateRange = DateValue([yourDateField])
WHERE yourTable.ID IS NULL
ASKER