Find unequal records in Query (1 Viewer)

[123dstreet]

Hi all! I've searched postings but cannot find what I need.

I have a table "Complete Parts List", and I have created a temporary table "TempParts". I've coded so that when the DB is opened, the Complete Parts List is copied and pasted into the TempParts table (which has the same fields and structure as Complete Parts List).

This table gets copied first thing in the morning, at the end of the day the Complete Parts List table will most likely have changed records in it, but the TempParts table will be the same. So I would like to compare the 2 tables and find only those records that are not identical, and running a Find Unmatched query isn't working. Any suggestions would be great! Thanks!

 

[jdraw]

Look in access query wizard for Unmatched Query

 

[123dstreet]

ok so I figured out where to change the WHERE condition in the SQL to give me the results I needed:

SELECT [Complete parts list].[Part #], [Complete parts list].Qty1
FROM [Complete parts list] LEFT JOIN TempParts ON ([Complete parts list].[Qty1] [COLOR=red]<>[/COLOR] TempParts.[Qty1]) AND ([Complete parts list].[Part #] = TempParts.[Part #])
WHERE (((TempParts.Qty1)[COLOR=red]<>[/COLOR][Complete Parts List].[Qty1]));

New problem; in this find unmatched query I have 2 tables: "Complete Parts List" & "TempParts". I need the find unmatched query to find multiple unmatched

Complete Parts List: TempParts:
PK for both tables is Part#

Qty1 Qty1​

Qty2 Qty2
Qty3 Qty3
Qty4 Qty4
Qty5 Qty5

Cost1 Cost1
Cost2 Cost2
Cost3 Cost3
Cost4 Cost4
Cost5 Cost5

I was able to make it work by using the wizard, but it only allowed me to use ONE field as criteria, I need to be able to run this query and find if ANY of these 10 fields are different. Any help would be GREAT!!!

 

[jdraw]

Perhaps you could describe the whole process -- what gets changed when and what is the significance of the changes from Start of Day to End of Day?

What level of detail are you looking for?
You could have a numeric PK - record it at start of Day; add new records with PK +1;
and subtract DMax(pk at end of day) - PK at start of Day to get new records.

Or you could timestamp each record that gets added or updated during the day. Record the Dmax(Timestamp at start of Day) and in the end of Day processing, identify all records with Timestamp > Timestamp at start of Day.

We need some context to see what would apply -- until then we're just guessing at your requirements.

 

[123dstreet]

Ok so here is what I've done:

When the DB is opened, the Complete Parts List table is opened, and all records are copied. Then the temporary table ("TempParts") is opened, and all of the records are pasted into this table.

Throughout the day, the records in the Complete Parts List will change. So when the DB is closed, I run a query to find ANY differences in ANY records between Complete Parts List and TempParts tables, those differences are sent to a report, and then saved to the hard drive as PDF files.

The main goal of this function is to track any changes to the Complete Parts List table, as there are multiple forms in the program that allow the users to change inventory levels, but unfortunately not all of them leave a paper trail, which causes balancing issues when every transaction is not accounted for.

I seem to be having only one more problem.. In the file extension name, how do I include the current database user name in there with all of the other information? I've tried putting & CurrentUser() & but cannot get it to work. Any suggestions would be appreciated!

Private Sub Form_Close()
On Error GoTo Err_Form_Close
Dim ReportFolder As String
Dim ReportFile As String
Dim stDocName As String
Dim stReport As String
Refresh
 
ReportFolder = DLookup("[WO Report Folder]", "Setup", "[ID]=4")
stReport = "Inventory Change Report"
stDocName = "Complete parts list Without Matching TempParts"
 
If Right(ReportFolder, 1) = "\" Then GoTo Report2 Else GoTo Report1
Report1:
ReportFile = ReportFolder & "\" & "Inventory Change" & " " & Format(Date, "mmm dd, yyyy") & " " & "at " & Format(Now, "(hh;nn AMPM)") & " " & ".pdf "
GoTo Continue1
Report2:
ReportFile = ReportFolder & "Inventory Change" & " " & Format(Date, "mmm dd, yyyy") & " " & "at " & Format(Now, "(hh;nn AMPM)") & " " & ".pdf "
GoTo Continue1
Continue1:
DoCmd.OpenQuery stDocName, acViewNormal
DoCmd.OpenReport "Inventory Report", acViewPreview
DoCmd.OpenReport stReport, acViewPreview
DoCmd.Close acQuery, stDocName
DoCmd.OutputTo acOutputReport, "Inventory Change Report", acFormatPDF, ReportFile
DoCmd.Close acReport, "Inventory Report"
DoCmd.Close acReport, stReport
Exit Sub
Exit_Form_Close:
    Exit Sub
Err_Form_Close:
    MsgBox Err.Description
    Resume Exit_Form_Close
 
 
End Sub