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

[nico5038]

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)

[willakawill]

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:

SELECT [TheDate] FROM [MyDateTable]
WHERE [TheDate] BETWEEN 'startdate' AND 'enddate'
AND [TheDate] NOT IN (
SELECT [DateColumn] FROM [Claims SharePoint Data]
WHERE [DateColumn] BETWEEN 'startdate' AND 'enddate')

[NeoPa]

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.

SELECT [TheDate]
FROM [MyDateTable] LEFT JOIN [Claims SharePoint Data]
  ON [MyDateTable].[TheDate]=[Claims SharePoint Data].[EntryDate]
WHERE [TheDate] Between DMin('[EntryDate]','[Claims SharePoint Data]')
                    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.

[ADezii]

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.

SELECT [TheDate]
FROM [MyDateTable] LEFT JOIN [Claims SharePoint Data]
  ON [MyDateTable].[TheDate]=[Claims SharePoint Data].[EntryDate]
WHERE [TheDate] Between DMin('[EntryDate]','[Claims SharePoint Data]')
                    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:

Dim dteStart As Date, intDays As Integer
Dim dteEnd As Date, intDaysInRange As Integer
Dim adteDateRange() As Date

dteStart = #1/15/2007#
dteEnd = #3/18/2007#

intDaysInRange = DateDiff("d", dteStart, dteEnd) + 1

ReDim adteDateRange(1 To intDaysInRange)

adteDateRange(1) = dteStart

For intDays = 2 To intDaysInRange
  adteDateRange(intDays) = DateAdd("d", intDays - 1, dteStart)
Next intDays

[nico5038]

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)

[NeoPa]

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.

[nico5038]

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)

[ADezii]

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.

[ADezii]

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.

[newnewbie]

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....W here am I wrong?

 SELECT [TheDate]
FROM Dates LEFT JOIN [Claims SharePoint Data Date Corrected] ON Dates.TheDate=[Claims SharePoint Data Date Corrected].Expr1
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

[NeoPa]

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.

[newnewbie]

Sorry my request is so dragged out (i am travelling...)

Here's the SQL:

SELECT [Claims SharePoint Data].ID,
       [Claims SharePoint Data].Name,
       [Claims SharePoint Data].Title,
       [Claims SharePoint Data].Client,
       [Claims SharePoint Data].Customer,
       [Claims SharePoint Data].ClientRefNumber,
       [Claims SharePoint Data].CustomerRefNumber,
       Format([EntryDate],"mm/dd/yyyy") AS Expr1,
       [Claims SharePoint Data].DocumentClaimDate,
       [Claims SharePoint Data].ReceivedDate,
       [Claims SharePoint Data].ClaimAmount,
       [Claims SharePoint Data].DocumentType,
       [Claims SharePoint Data].AdGroup,
       [Claims SharePoint Data].DeductionID,
       [Claims SharePoint Data].IndexUser,
       [Claims SharePoint Data].[Created By],
       [Claims SharePoint Data].[Modified By],
       [Claims SharePoint Data].Modified,
       [Claims SharePoint Data].Created
FROM [Claims SharePoint Data];

Date Format:

Expr1: Format([EntryDate],"mm/dd/yyyy")

[NeoPa]

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]; [DocumentClaimDa te]; [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?