468,761 Members | 1,824 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,761 developers. It's quick & easy.

Find Missing Dates in Query

newnewbie
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
13 11788
nico5038
3,079 Expert 2GB
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
willakawill
1,646 1GB
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
32,130 Expert Mod 16PB
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
8,800 Expert 8TB
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
3,079 Expert 2GB
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
32,130 Expert Mod 16PB
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
3,079 Expert 2GB
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
8,800 Expert 8TB
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
8,800 Expert 8TB
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
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
32,130 Expert Mod 16PB
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
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
32,130 Expert Mod 16PB
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.

Similar topics

4 posts views Thread by SQLJunkie | last post: by
2 posts views Thread by Keith | last post: by
17 posts views Thread by Justin Emlay | last post: by
67 posts views Thread by PC Datasheet | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.