Compare results of data import programmatically

[str8trini]

I am importing data from four different sources and I want to validate that the data for all sources is for the same time period before continuing with my updates. I created summary queries and grouped by the date formatted as a month so there is only one record in the query output for each that I can then compare. But I would like to add the comparison check in the code that I am calling after the data is imported to make further updates but if they all don't match then I would exit the code. Is there a way I can do this using VBA code? Thanks in advance for the help!

[June7]

I suppose some complex VBA procedure could do that. Or you could run Find Unmatched queries and view them to evaluate the output.

[str8trini]

I think I may have posted to the incorrect forum and I have re-posted to the Modules forum instead. Thanks!

[June7]

Do not post duplicate threads. I deleted the duplicate thread. This forum is okay.

[str8trini]

Thank you...I edited my post as to what I am trying to achieve and I don't believe your suggestion will work. I am doing this for someone else to run and I need it to work automatically using a VBA procedure. I think I have done this before but it has been a few years and several versions since I have worked with Access and things are slowly coming back to me. Will I be able to get assistance on this forum?

[June7]

If someone else has something to offer, they will provide whatever assistance they can.

One approach may be to use domain aggregate functions (DLookup, DCount, DSum, etc) to do an inquiry with the same criteria on each table or query and see if they return equivalent results.

[str8trini]

If someone else has something to offer, they will provide whatever assistance they can.

One approach may be to use domain aggregate functions (DLookup, DCount, DSum, etc) to do an inquiry with the same criteria on each table or query and see if they return equivalent results.

Just wanted to update my question to Resolved. As I said previously, I had not worked with Access for a few years so it took some work but I was able to resolve my issue. I thought I would add the code I was able to put together to this thread if it can help someone in the future. It may be elementary but it works. Thanks!

Public Function UpdateDownload()
On Error GoTo UpdateDownload_Err
DoCmd.SetWarnings False

'Compare downloaded data to ensure same month for all data
Dim rsQA As New ADODB.Recordset, rsHR As New ADODB.Recordset, rsLP As New ADODB.Recordset, rsRJ As New ADODB.Recordset
Dim sqlQA As String, sqlHR As String, sqlLP As String, sqlRJ As String
Dim strQA As String, strHR As String, strLP As String, strRJ As String

sqlQA = "SELECT Format([Evaluation Creation Date],'mmm yyyy') AS QA" & _
" FROM [tmpQA_Calls]" & _
" GROUP BY Format([Evaluation Creation Date],'mmm yyyy')"

Set rsQA = New ADODB.Recordset
With rsQA
If rsQA.State = adStateOpen Then
.Close
End If
.Source = sqlQA
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open
End With
strQA = rsQA!QA

sqlHR = "SELECT Format([F1],'mmm yyyy') AS HR" & _
" FROM tmpDownloadHR" & _
" GROUP BY Format([F1],'mmm yyyy')" & _
" HAVING (((Format([F1],'mmm yyyy'))<>'MTD DATE'))"

Set rsHR = New ADODB.Recordset
With rsHR
If rsHR.State = adStateOpen Then
.Close
End If
.Source = sqlHR
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open
End With
strHR = rsHR!HR

sqlLP = "SELECT Format([MTD Date],'mmm yyyy') AS LP" & _
" FROM tmpDownLoadLP" & _
" GROUP BY Format([MTD Date],'mmm yyyy')"
Set rsLP = New ADODB.Recordset
With rsLP
If rsLP.State = adStateOpen Then
.Close
End If
.Source = sqlLP
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open
End With
strLP = rsLP!LP

sqlRJ = "SELECT Format([RequestDate],'mmm yyyy') AS RJ" & _
" FROM tmpLtrRejects" & _
" GROUP BY Format([RequestDate],'mmm yyyy')"
Set rsRJ = New ADODB.Recordset
With rsRJ
If rsRJ.State = adStateOpen Then
.Close
End If
.Source = sqlRJ
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.Open
End With
strRJ = rsRJ!RJ

If strQA <> strHR Then
MsgBox "Downloaded files are not for the same Evaluation month!"
GoTo UpdateDownload_Exit
End If

If strQA <> strLP Then
MsgBox "Downloaded files are not for the same Evaluation month!"
GoTo UpdateDownload_Exit
End If

If strQA <> strRJ Then
MsgBox "Downloaded files are not for the same Evaluation month!"
GoTo UpdateDownload_Exit
End If

'Delete data from temporary tables
DoCmd.RunSQL "DELETE tmpQA_Summ.* FROM tmpQA_Summ"
DoCmd.RunSQL "DELETE tmpLtrRejSumm.* FROM tmpLtrRejSumm"
DoCmd.RunSQL "DELETE tmpAgentPerfHR.* FROM tmpAgentPerfHR"
DoCmd.RunSQL "DELETE tmpAgentPerfLP.* FROM tmpAgentPerfLP"

'Delete Data from temporary ranking tables
DoCmd.RunSQL "DELETE tmpRankingHR_ACPH.* FROM tmpRankingHR_ACPH"
DoCmd.RunSQL "DELETE tmpRankingHR_CPH.* FROM tmpRankingHR_CPH"
DoCmd.RunSQL "DELETE tmpRankingHR_HT.* FROM tmpRankingHR_HT"
DoCmd.RunSQL "DELETE tmpRankingHR_PKR.* FROM tmpRankingHR_PKR"
DoCmd.RunSQL "DELETE tmpRankingHR_QA.* FROM tmpRankingHR_QA"
DoCmd.RunSQL "DELETE tmpRankingLP_ACPH.* FROM tmpRankingLP_ACPH"
DoCmd.RunSQL "DELETE tmpRankingLP_Dlq.* FROM tmpRankingLP_Dlq"
DoCmd.RunSQL "DELETE tmpRankingLP_GCL.* FROM tmpRankingLP_GCL"
DoCmd.RunSQL "DELETE tmpRankingLP_QA.* FROM tmpRankingLP_QA"

'Update Downloaded files
DoCmd.OpenQuery "qry1A_LtrRejSumm"
DoCmd.OpenQuery "qry1B_QA_CallsSumm"
DoCmd.OpenQuery "qry1C_AddToPerfHR"
DoCmd.OpenQuery "qry1D_AddToPerfLP"

'Update HR Ranking Tables
DoCmd.OpenQuery "qry2A_Ranking_ACPH_HR"
DoCmd.OpenQuery "qry2A_Ranking_CPH_HR"
DoCmd.OpenQuery "qry2A_Ranking_HT_HR"
DoCmd.OpenQuery "qry2A_Ranking_PKR_HR"
DoCmd.OpenQuery "qry2A_Ranking_QA_HR"

'Update LP Ranking Tables
DoCmd.OpenQuery "qry3A_Ranking_ACPH_LP"
DoCmd.OpenQuery "qry3A_Ranking_Dlq_LP"
DoCmd.OpenQuery "qry3A_Ranking_GCL_LP"
DoCmd.OpenQuery "qry3A_Ranking_QA_LP"

'Finalize Performance tables
DoCmd.OpenQuery "qry2B_PerformanceHR"
DoCmd.OpenQuery "qry3B_PerformanceLP"

UpdateDownload_Exit:
DoCmd.SetWarnings True
Set rsQA = Nothing
Set rsLP = Nothing
Set rsHR = Nothing
Set rsRJ = Nothing
'DoCmd.Echo True
Exit Function

UpdateDownload_Err:
MsgBox Error$
Resume UpdateDownload_Exit
End Function

[ItsMe]

If you have performance issues with your current solution, you might want to consider creating a query that joins on the fields you wish to compare and add an Is Null criteria. June made the suggestion in post #2 to use the Unmatched Query Wizard. I use this wizard to help with the proper syntax.