query to identify non-represented days in a date/time column.

pma111
pma111 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
First, create a tiny query:

SELECT DISTINCT Abs([id] Mod 10) AS N
FROM MSysObjects;

Open in new window

Save this as Ten.

Then create this query:

PARAMETERS 
    [DateStart] DateTime, 
    [DateEnd] DateTime;
SELECT 
    [Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000 AS Id, 
    [DateStart] AS DateStart, 
    [DateEnd] AS DateEnd,  DateAdd("d",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000,[DateStart]) AS [Date]
FROM 
    Ten AS Ten_0, 
    Ten AS Ten_1, 
    Ten AS Ten_2, 
    Ten AS Ten_3, 
    Ten AS Ten_4, 
    Ten AS Ten_5, 
    Ten AS Ten_6, 
    Ten AS Ten_7
WHERE    (((DateAdd("d",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000+[Ten_7].[N]*10000000,[DateStart])) <= [DateEnd]) 
AND ((Ten_0.N)<=DateDiff("d",[DateStart],[DateEnd])\1) 
AND ((Ten_1.N)<=DateDiff("d",[DateStart],[DateEnd])\10) 
AND ((Ten_2.N)<=DateDiff("d",[DateStart],[DateEnd])\100) 
AND ((Ten_3.N)<=DateDiff("d",[DateStart],[DateEnd])\1000) 
AND ((Ten_4.N)<=DateDiff("d",[DateStart],[DateEnd])\10000) 
AND ((Ten_5.N)<=DateDiff("d",[DateStart],[DateEnd])\100000) 
AND ((Ten_6.N)<=DateDiff("d",[DateStart],[DateEnd])\1000000) 
AND ((Ten_7.N)<=DateDiff("d",[DateStart],[DateEnd])\10000000));

Open in new window

Save it as DatesRange.

Next, extract the meeting dates:

Select Distinct DateValue([Timestamp]) As [Date] From YourTimestampTable

Open in new window


Save this as MeetingDates.

Finally, create a query using these queries:

SELECT 
    DatesRange.Date
FROM 
    DatesRange 
LEFT JOIN 
    MeetingDates 
    ON DatesRange.Date = MeetingDates.Date
WHERE 
    MeetingDates.Date Is Null;

Open in new window

This will list your dates with no meetings.

Author

Commented:
works great.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Thanks!
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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:

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]

Open in new window


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

Open in new window

Of course you will need to change "yourTable" to the name of your table, "yourDateField" to the name of your date/time field, and "ID" to the name of any field in your table which will always contain data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today