Query to display missing records in a time period

[JohnCM]

All,

I have a database that pulls down information from a website. There should be one record per day per customer. Due to some reason a customer may not have a record for a day or multiple days while the other customers have a record.

I need to create a query to show me these "Holes" where a customer should have a record but does not. I want to display any customers who are missing a record and what day(s) is missing. The customer list is dynamic with customers leaving and starting at different times. I have a separate table to indicate which customers are currently active.

Ideas?

I feel that I can do an unmatched item query if I had a table that had each day in the period. Without making a table with just dates in it how can I accomplish this in SQL or query design?

Also, would this approach work to show the customer and the day missing or is a loop of some kind needed to evaluate each customer for missing records?

If this is not the right approach to this please suggest another way.

Thanks,

John Martinez

 

[SydneyGeek]

A table of just dates is a good starting point. You can create one from just the current data set, by selecting unique dates from the main table and converting it to make-table query. Let's say tblDates is the output.
You'll need two queries.
first add the active client table and tblDates to a new query. Put the client name and date fields into the query. Because there is no join between the tables you will get all possible client/date combinations.
save this query, start a new query with the main table and the first query.
Join (mismatch) on both client name and date. That should give you the missing records.

Denis

 

[JohnCM]

Thank you Denis,

I choose to gather the dates by using a Select distinct query of the records I currently have. This provides two advantages in that I do not have to create a separate sub to continually update the tblDate table and the second being that I will get a list of dates inclusive of like data.

I created an unmatched query to then look for missing dates which is working well but it appears that I have to loop through the recordset with each active customer. I don't know the sql to determine missing records by date by customer in one sql statement. If this can be done it would help my report tremendously. The current workaround I have is to use VBA and loop through the recordset providing each customer number to a second recordset and then write the results to a table. This is slower than I would like because of having to loop a second time through a second recordset to then write an insert into sql statement to populate the table.

I realize that is a bit wordy so I have attached the VBA, which is unfinished, to show my work. If there is a way to speed this program through use of another method I would appreciate it.

My first choice would be to have a single query statement to display the missing records of all active customers and my second choice would be to be able to execute a query that would take all of the result from the second recordset and insert them into the tempTable.

Thanks,
John Martinez

Option Compare Database

Sub MissingTelemetry()
    'Will convert to a function. Passing the Start Date and end date from a form.
    Dim EDate As Date
    Dim SDate As Date
    
    Dim MeterNum As String
    
    Dim SqlSDate As String
    Dim SqlUnmatched As String
    Dim SqlMeters As String
    
    Dim RstCust As Recordset
    Dim RstUnmatched As Recordset
    Dim RstSDate As Recordset
    
    Dim Tbl As String
    
    Tbl = "tblTempMissingGasDay"
    
    'Clear table of records
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * From " & Tbl & " ;"
    DoCmd.SetWarnings True
    
    'Get LastDay of Telemetry Info
    EDate = DMax("[GasDay]", "tblTelemetry")
    
    SqlMeters = "SELECT tblAccountMeters.MeterNbr " & _
                "FROM tblAccountMeters INNER JOIN tblAccounts ON tblAccountMeters.CPR = tblAccounts.CPR " & _
                "WHERE (((tblAccounts.Status)=-1));"

    Set RstCust = CurrentDb.OpenRecordset(SqlMeters, dbOpenSnapshot)
        
    If RstCust.RecordCount = 0 Then
        MsgBox "No Records", vbOKOnly
        GoTo CommExit
    Else
        RstCust.MoveFirst
        Do While Not RstCust.EOF
               
            'SqlSDate = "SELECT tblAccounts.StartDate " & _
                              "FROM tblAccountMeters INNER JOIN tblAccounts ON tblAccountMeters.CPR = tblAccounts.CPR " & _
                              "WHERE (((tblAccountMeters.MeterNbr)= '" & RstCust!meternbr & "'));"
            'Set RstSDate = CurrentDb.OpenRecordset(SqlSDate, dbOpenSnapshot)
            'Will not work to pass the startdate this way.  The start date is by CPR and not Meternumber which differ.
            
            'SDate = DMin("[GasDay]", "tblTelemetry", "[MeterNbr] = '" & RstCust!meternbr & "'")
            SDate = #7/1/2010# ' Hard Coding the Start Date. Will pass in with the function
            'Provides all of the missing days for a particular meter in the time period requested.
            SqlUnmatched = "SELECT tDates.GasDay " & _
                       "FROM (SELECT DISTINCT tblTelemetry.GasDay " & _
                        "FROM tblTelemetry) as tDates LEFT JOIN (SELECT tblTelemetry.MeterNbr, tblTelemetry.GasDay " & _
                        "FROM tblTelemetry " & _
                        "WHERE (((tblTelemetry.MeterNbr)= '" & RstCust!meternbr & "'))) as TelMin ON tDates.GasDay = telmin.GasDay " & _
                        "WHERE (((tDates.GasDay) Between #" & SDate & "# And #" & EDate & "#) AND ((TelMin.GasDay) Is Null));"

            Set RstUnmatched = CurrentDb.OpenRecordset(SqlUnmatched, dbOpenSnapshot)
        'place2holder = #7/10/2010#
            If RstUnmatched.RecordCount > 0 Then
                '****This loop is slower than I would like.  Replace with a single bit of code to paste all of RstUnmatched into table.  Have not seen code to do this.
                Do While Not RstUnmatched.EOF
                    sqlinsert = "INSERT INTO " & Tbl & "(MeterNumber, GasDay) " & _
                                "Select " & RstCust!meternbr & ", #" & RstUnmatched!gasday & "#"
                    With DoCmd
                        .SetWarnings False
                        .RunSQL sqlinsert
                        .SetWarnings True
                    End With
                    
                    
                    RstUnmatched.MoveNext
                Loop
            End If
            RstCust.MoveNext
        Loop
    End If
CommExit:
    RstCust.Close
    RstSDate.Close
    RstUnmatched.Close
    RstCust = Nothing
    RstSDate = Nothing
    RstUnmatched = Nothing
End Sub

 

[SydneyGeek]

Hi John,

The reason I suggested the query with the active clients is that you get all valid client/date combinations without looping. That allows you to extract the missing records with 2 queries, and a minimal amount of VBA to run the queries. If you want to you can make the second query into a make-table, so you end up with a separate dataset. The whole thing runs in one routine;

Sub MissingData()
    Dim sSQLDays As String
    Dim sSQLMissing As String
    
    'First create a table with all valid clientID/date combinations
    sSQLDays = "SELECT DISTINCT tblTelemetry.[GasDay], " _
        & "tblActiveClients.ClientID " _
        & "INTO tblCombinations " _
        & "FROM tblTelemetry, tblActiveClients "
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQLDays
    DoCmd.SetWarnings True
    
    'now show all missing combinations
'    sSQLMissing = "SELECT tblCombinations.GasDay, tblCombinations.ClientID " _
'        & "FROM tblCombinations LEFT JOIN tblTelemetry " _
'        & "ON (tblCombinations.ClientID = tblTelemetry.ClientID) " _
'        & "AND (tblCombinations.GasDay = tblTelemetry.GasDay) " _
'        & "WHERE (((tblTelemetry.GasDay) Is Null));"
'    Debug.Print sSQLMissing
    
    DoCmd.OpenQuery "qryMissing"
End Sub

The commented code creates the SQL for the mismatch query. Uncomment it first time you run the code, stop when it generates the error about the missing query, copy the SQL into a blank query and save it as qryMissing.
Next time you should get the missing records.

I simplified the queries down to minimal fields but hopefully you will see how to adapt it.

Denis

 

[JohnCM]

Ah, I get it now. The first query creates a cartesian product of dates and active account numbers. Then look for missing data.

Never would have thought of that for the first query. Good call.

John Martinez

 

[bs0d]

A table of just dates is a good starting point. You can create one from just the current data set, by selecting unique dates from the main table and converting it to make-table query. Let's say tblDates is the output.
You'll need two queries.
first add the active client table and tblDates to a new query. Put the client name and date fields into the query. Because there is no join between the tables you will get all possible client/date combinations.
save this query, start a new query with the main table and the first query.
Join (mismatch) on both client name and date. That should give you the missing records.

Denis

I have a similar issue, except I'd like to display the missing dates (with blank data) as a part of the query results. In working on this, I assumed a dates table would be the way to go, but I'm having an issue with setting up the query. My dates table goes from 1/1/1900 - 1/1/2100. I thought I could do a left join (to include all dates) on the dates in my data table. However, this isn't working (dates are still missing). I thought this might be due to the condition of the query- as I'm querying for a specific property. Also, I need to limit the min and max dates to the actual min and max of the selected property. Could you help explain how i can setup this query?
Or, I could use a VBA loop to add missing dates, but I was thinking this might be inefficient or a slower process.

Thanks

 

[SydneyGeek]

If you include the data field from the main date table in the output, you will see which dates are missing. Just having the null fields from the data table will only show blanks, because that is what the left join lets you see. So, something like:

tblDates.RefDate
tblMain.OrderDate (will be null is you have asked for nulls from a left join)
tblMain.OtherFields (whatever... they will be null too)

The SQL (untested, writing this freehand) would be something like:

SELECT tblDates.RefDate, tblMain.OrderDate, tblMain.OrderVolume
  FROM tblDates LEFT JOIN tblMain ON tblDates.RefDate = tblMain.OrderDate
 WHERE tblMain.OrderDate is null;

Denis

 

[bs0d]

Thanks for the reply Denis. I don't think we're on the same page.

Let's assume I have daily data for entities in a table "DailyData". Some entities have missing dates. I would like to create a query, which would display the daily data for a specific entity, including the dates which no data exists.

Sample data in DailyData:
date--------------entity-------value
1/1/2016---------123---------50
1/2/2016---------123---------52
1/3/2016---------123---------51
1/15/2016--------123---------53
1/16/2016--------123---------50
...
So assuming the data above, I need my query to include the data in the table for the entity, as well as 1/4/16 through 1/14/16, even though it doesn't exist in the DailyData table.

I created a dates table including all dates from 1/1/1900 through 1/1/2100. I thought this can be used to fill the blanks in "DailyData" table via query for specific entities. Note that I also would need to limit the query to the min and max dates of the entity I'm querying, so it wouldn't return all dates from year 1900 to 2100.

Thanks!

 

[bs0d]

Even testing the left join with the dates table and the table with daily readings isn't quite working like I'd expect it to. Left join, right join... still returns the same records (with missing dates).

 

[xenou]

It should work. Post your query.