From time to time, I deal with data that is supposed to be taken on a daily basis. But there are times when there are gaps in the data. And that is a problem because I need to know how many data gaps I have and how long each gaps lasts. In a spreadsheet, it's simple enough to look for the data gaps albeit much harder to create a list of the gaps and the length for a large amount of data. However, I was wondering how I'd do it in an Access database. Well, after a little bit of thought, this is what I came up with.
Let's say we have some data that looks like this:
| Date |
|---|
| 1/1/2018 |
| 1/2/2018 |
| 1/3/2018 |
| 1/5/2018 |
| 1/7/2018 |
| 1/10/2018 |
Let's also say that we are looking at the data that we have from 12/1/2017 to 1/31/2018 (Let's assume these start and end dates are selected from an unbound form field). That means we have the following data gaps:
| Date Gaps |
|---|
| 12/1/2017 - 12/31/2017 |
| 1/4/2018 - 1/4/2018 |
| 1/6/2018 - 1/6/2018 |
| 1/8/2018 - 1/9/2018 |
| 1/11/2018 - 1/31/2018 |
The conceptual steps are as follows:
- Find gap (if any) between our start data and the start of our data within the time period selects. This done by finding the minimum date in our date query that >= 'Start Date'. If the date found is the same, there is no gap at the beginning.
- For each data record within our time period, find the minimum date >= record date. Calculate the difference between the record and next date.
- Find gap (if any) between our end data and the end of our data within the time period selects. This done by finding the maximum date that <= 'Start Date'. If the date found is the same, there is no gap at the end.
- Get the records step 2 with a gap > 1, and add the records from Step 1 & 3 if the gap is > 0 (a gap of 1 means we are missing the start and end date)
The way we find the dates for step 1 and 3 is simple. We sort the data from ascendingly for step 1 and descendingly for step 3, and select the first record which means we don't need to use any sort of MIN or MAX function or subquery. However, for step 2, we need to use a correlated subquery as a query field.
dtNext: (SELECT Min(a.dtDate)
FROM tblDates AS a
WHERE ((a.dtDate)>tbldates.dtDate))
You can use the same concept for any frequency besides just a daily frequency. If your data is supposed to have have 1 minute gaps, you can calculate the gaps using DateDiff() and set your own criteria for step 4.