By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

Find Missing Dates in Query

newnewbie
P: 54
I have an Access database that I upload a data extract intoto daily. I want to create a query that will give me a list of dates that no data can be found for. E.g. there are no records created on the weekends, hence weekend dates will be in the list generated by this query.

By creating such a query I want to ensure integrity of my data, e.i.looking at this query from time to time will give me an idea that on such and such date I might have forgotten to upload new data.

Table is called Claims SharePoint Data

The date field is EntryDate. The dates that data is missing will not be in the table, e.g. there are no weekend dates in the table. The weekend dates have to turn up in the query results.

I am kind of new with access, so I am stumped. Looed in Help and could not find anything like that...

Thanks,

Lena
Jan 5 '07 #1
Share this Question
Share on Google+
13 Replies


nico5038
Expert 2.5K+
P: 3,072
To achieve this using a query you'll need to create a table with all dates in the range (period) you want to match.
Having such a table will allow you to create a query that's JOINing the original table with the tblDates and by making it an outer join (LEFT or RIGHT), the missing dates can be detected.

Another solution could be to process the rows from VBA code and detect the missing ones.

What do you prefer ?

Nic;o)
Jan 6 '07 #2

100+
P: 1,646
I have an Access database that I upload a data extract intoto daily. I want to create a query that will give me a list of dates that no data can be found for. E.g. there are no records created on the weekends, hence weekend dates will be in the list generated by this query.

By creating such a query I want to ensure integrity of my data, e.i.looking at this query from time to time will give me an idea that on such and such date I might have forgotten to upload new data.

Table is called Claims SharePoint Data

The date field is EntryDate. The dates that data is missing will not be in the table, e.g. there are no weekend dates in the table. The weekend dates have to turn up in the query results.

I am kind of new with access, so I am stumped. Looed in Help and could not find anything like that...

Thanks,

Lena
Nico is correct. You cannot search a database for what is not there unless you have an example of what you are searching for. In this case the temp table gives you a value to search for:
Expand|Select|Wrap|Line Numbers
  1. SELECT [TheDate] FROM [MyDateTable]
  2. WHERE [TheDate] BETWEEN 'startdate' AND 'enddate'
  3. AND [TheDate] NOT IN (
  4. SELECT [DateColumn] FROM [Claims SharePoint Data]
  5. WHERE [DateColumn] BETWEEN 'startdate' AND 'enddate')
Jan 6 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
If you create the date range in the temp table ([MyDateTable] in this example) to match the date range required then you won't need the WHERE clause.
Expand|Select|Wrap|Line Numbers
  1. SELECT [TheDate]
  2. FROM [MyDateTable] LEFT JOIN [Claims SharePoint Data]
  3.   ON [MyDateTable].[TheDate]=[Claims SharePoint Data].[EntryDate]
  4. WHERE [TheDate] Between DMin('[EntryDate]','[Claims SharePoint Data]')
  5.                     And DMax('[EntryDate]','[Claims SharePoint Data]')
Don't forget though, there is also an option to do it in VBA as Nico said.
You need to decide what suits you best.
Jan 7 '07 #4

ADezii
Expert 5K+
P: 8,638
If you create the date range in the temp table ([MyDateTable] in this example) to match the date range required then you won't need the WHERE clause.
Expand|Select|Wrap|Line Numbers
  1. SELECT [TheDate]
  2. FROM [MyDateTable] LEFT JOIN [Claims SharePoint Data]
  3.   ON [MyDateTable].[TheDate]=[Claims SharePoint Data].[EntryDate]
  4. WHERE [TheDate] Between DMin('[EntryDate]','[Claims SharePoint Data]')
  5.                     And DMax('[EntryDate]','[Claims SharePoint Data]')
Don't forget though, there is also an option to do it in VBA as Nico said.
You need to decide what suits you best.
'Please let me know if I am Over The Top on this but a list of Dates within a User Specified Range can be placed in an Array and then cross-referenced with values in the [EntryDate] Field to find missing Dates. The Output can be directed in several ways. I included only the code that populates the Array just in case I went tooooo far:
Expand|Select|Wrap|Line Numbers
  1. Dim dteStart As Date, intDays As Integer
  2. Dim dteEnd As Date, intDaysInRange As Integer
  3. Dim adteDateRange() As Date
  4.  
  5. dteStart = #1/15/2007#
  6. dteEnd = #3/18/2007#
  7.  
  8. intDaysInRange = DateDiff("d", dteStart, dteEnd) + 1
  9.  
  10. ReDim adteDateRange(1 To intDaysInRange)
  11.  
  12. adteDateRange(1) = dteStart
  13.  
  14. For intDays = 2 To intDaysInRange
  15.   adteDateRange(intDays) = DateAdd("d", intDays - 1, dteStart)
  16. Next intDays
Jan 8 '07 #5

nico5038
Expert 2.5K+
P: 3,072
Cross referencing the array with the records will need a recordset processing loop. In such a loop the detection of missing dates is already relatively easy by storing and comparing the value of the previous record date with the current record. In general a recordset processing loop is rather slow and a query solution much faster. By using the prebuild temp all date table (that can be re-used) a query solution will not only be much faster, but also require no code.

Nic;o)
Jan 8 '07 #6

NeoPa
Expert Mod 15k+
P: 31,494
I agree with Nico here (very well explained btw).
However, every idea contributed triggers further ideas in the readers so it is certainly not wasted ADezii, and it's good experience for all involved.
Jan 8 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Thanks NeoPa, I always value the discussion about solutions and idea's like an array are indeed worthy to be investigated as possible solution.
So indeed the array comment is valued by me as I always try to widen my view first as much as possible before narrowing it down to the solution. All discussion arguments are taken in account and they will reveal the strong and weak points of the solution chosen.
There's no such thing as a silver bullit solution ,but it's invaluable to know the weaknesses of the used solution on forehand so you can anticipate on that.

Nic;o)
Jan 8 '07 #8

ADezii
Expert 5K+
P: 8,638
Cross referencing the array with the records will need a recordset processing loop. In such a loop the detection of missing dates is already relatively easy by storing and comparing the value of the previous record date with the current record. In general a recordset processing loop is rather slow and a query solution much faster. By using the prebuild temp all date table (that can be re-used) a query solution will not only be much faster, but also require no code.

Nic;o)
Thanks for the advice, it was graciously accepted.
Jan 8 '07 #9

ADezii
Expert 5K+
P: 8,638
I agree with Nico here (very well explained btw).
However, every idea contributed triggers further ideas in the readers so it is certainly not wasted ADezii, and it's good experience for all involved.
Thanks for the learning experience from both you and nico5038.
Jan 8 '07 #10

newnewbie
P: 54
Hi,
Thank you all for all your inputs. I have never worked with VBA, I do not know where to go to paste it (I tried a couple of times and it did not work, so I'd rather go with the other option.

Now, I modified the code a little bit:

[Claims SharePoint Data Date Corrected] is the name of the query I ran on original table to format the original date that had a time stamp on it and was acting weird. The date field name that was corrected in Expr1 now. My date table is called Dates and the filed there is TheDate.

Now, I run it and Access tells me there is a type mismatch in expression....Where am I wrong?

Expand|Select|Wrap|Line Numbers
  1.  SELECT [TheDate]
  2. FROM Dates LEFT JOIN [Claims SharePoint Data Date Corrected] ON Dates.TheDate=[Claims SharePoint Data Date Corrected].Expr1
  3. WHERE [TheDate] Between DMin('[Expr1]','[Claims SharePoint Data Date Corrected]') And DMax('[Expr1]','[Claims SharePoint Data Date Corrected]') 
I would try VBA if somebody could walk me through as to where to go to paste it....and that might be a little too much :)

I will definitely take the tutorials here once they are up...

Thank you,
Lena
Jan 11 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
I've checked your SQL and it seems fine to me (if a little clumsy in the naming department).
Now we need two things from you :
  1. The SQL of your query [Claims SharePoint Data Date Corrected].
  2. The field types of Dates.TheDate and [Claims SharePoint Data Date Corrected].Expr1
The idea used here depends on the fields both being of type Date/Time.
Jan 12 '07 #12

newnewbie
P: 54
Sorry my request is so dragged out (i am travelling...)

Here's the SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Claims SharePoint Data].ID,
  2.        [Claims SharePoint Data].Name,
  3.        [Claims SharePoint Data].Title,
  4.        [Claims SharePoint Data].Client,
  5.        [Claims SharePoint Data].Customer,
  6.        [Claims SharePoint Data].ClientRefNumber,
  7.        [Claims SharePoint Data].CustomerRefNumber,
  8.        Format([EntryDate],"mm/dd/yyyy") AS Expr1,
  9.        [Claims SharePoint Data].DocumentClaimDate,
  10.        [Claims SharePoint Data].ReceivedDate,
  11.        [Claims SharePoint Data].ClaimAmount,
  12.        [Claims SharePoint Data].DocumentType,
  13.        [Claims SharePoint Data].AdGroup,
  14.        [Claims SharePoint Data].DeductionID,
  15.        [Claims SharePoint Data].IndexUser,
  16.        [Claims SharePoint Data].[Created By],
  17.        [Claims SharePoint Data].[Modified By],
  18.        [Claims SharePoint Data].Modified,
  19.        [Claims SharePoint Data].Created
  20. FROM [Claims SharePoint Data];
Date Format:

Expr1: Format([EntryDate],"mm/dd/yyyy")
Jan 30 '07 #13

NeoPa
Expert Mod 15k+
P: 31,494
Firstly, you're nearly there it seems. Unfortunately, your're trying to compare apples to pears.
I assume that the [Dates].[TheDate] field is Date/Time?
Are the following fields in the [Claims SharePoint Data] table all Date/Time fields?
[EntryDate]; [DocumentClaimDate]; [ReceivedDate]; [Modified]; [Created].

If you can answer these questions I can give you some SQL to use to return the results you need.
In your SQL to return the dates (using [Claims SharePoint Data Date Corrected] - see your post #11) you simply return a date in the results, whether or not the date matches a record from [Claims SharePoint Data]. Is this what you require ? Or do you want the record from [Claims SharePoint Data] too?
Jan 30 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.