Query and dates problem

[liamcorkhill]

Hello.

I have a database which should be filled in daily. Unfortunately, as it is being completed by people several hundered miles away (on naval vessels) sometimes they dont fill / send in the data.
I need to create a query which gives me a list of dates for which data has NOT been entered, and I cant seem to find a way of doing it. Any advice / suggestions would be much appreciated.

Regards,
Liam Corkhill

 

[AArt]

You could run a query each day that checks for data yesterday, using the criteria Date() - 1. You'll need a master table with all the users, and join it with the data table, then do a query using the "Find unmatched" wizard.
Looking back for all the dates is a little tricky, how I would approach it depends on the size of the problem. A crosstab query with Dates and users could help you spot the "holes' in the data. I might export it to Excel and play with it in there using pivot tables. If its a huge Database, I guess I'd write some code to cycle through each record and check the dates.
Is there a record for each date, with just the data missing? or is the entire record missing if the entry wasn't made?

 

[gwinn7]

Here is a solution

Private Function GetUnusedDates(TableName As String, _
FieldName As String, _
BegDate As Date, _
Optional EndDate As Date) As String

Dim strUnusedDates As String
Dim dteUnusedDate As Date
Dim iDayCount As Integer
Dim rc As Recordset

iDayCount = 1
If CDbl(EndDate) = 0 Then EndDate = Format(Now(), "mm/dd/yyyy&quot
Set rc = CurrentDb.OpenRecordset( _
"SELECT DISTINCT " & FieldName & " FROM " & TableName & " WHERE " & FieldName & " between #" & BegDate & "# AND #" & EndDate & "# ORDER BY " & FieldName & ";&quot

If Not rc.BOF Then
rc.MoveFirst
dteUnusedDate = BegDate

Do Until dteUnusedDate = EndDate
If Not dteUnusedDate = rc.Fields(FieldName) Then
strUnusedDates = strUnusedDates & dteUnusedDate & ";"
dteUnusedDate = DateAdd("d", 1, dteUnusedDate)
Else
rc.MoveNext
If rc.EOF Then rc.MovePrevious
dteUnusedDate = DateAdd("d", 1, dteUnusedDate)
End If
Loop
End If

Set rc = Nothing
GetUnusedDates = strUnusedDates

End Function

I tested this function and it works pretty good.

Gary
gwinn7

 

[MichaelRed]

A slightly different approach. Generate a NEW table. It should have two fields: ChkDt As Date, Used as Yes/No.

Hill the table with (sequential) dates from the start date of the exercise to Now.

Do a the standard "NotMatched" query between the two tables. Use the "Unmatched" query results to update the Used field in the new table.

If you only need a "quick and dirty", just use the "unmatched" query directly. If there is "more to the story", use the table, where the Used field is false.

If i needed to do this chore 'regularly', I would add a bit to the process and wrap it in a procedure.

[tab]Add a query to clear the 'used' field to false for all records.

[tab]add a routine which checked the max date in the new table. If it is not ~ Now(), then append new records with the missing dates.

Autorun the unmatched query and the update query

[tab]generate a small report listing the missing records/dates.

All for a price - but of course!

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[liamcorkhill]

My apologies for the delay in the response to your messages. I have not had an internet connection for the last few days.

Many thanks for your responses. I am going to resume work on this project now and I will try them out

I will let you know how it goes.

Regards

Liam Corkhill