Comparing rows from two tables to find out changes

[shoro]

Hi there,

I would really appreciate if someone could help me with this problem. I am new to Access so kind of struggling to find solution.
I regularly receive two data files in Excel format - the files have four fields in common. The idea is to convert the files in Access tables so as to be able to compare and determine if any changes appear in any field of a record. This way I want to be able to provide end-user a list of all the records where changes have taken place for validation purpose.
Thank you,

 

[DavidAtWork]

Do the files have a unique identifier which will be a mechanism to match a record in file 1 to the same record in file 2. Perhaps you could post a sample of the 2 files.
If there is a way of matching the records, then it will be a case of going through each record in both files, field by field using String Compare function to establish a change has been made.
David

 

[shoro]

Thanks for your reply, David! Yes, there is a unique identifier common between both the files, but I am not sure how to compare them field by field. Is there a code that I could use. I will post the sample files later today.
Really appreciate your help.

 

[shoro]

Hi David, here's the sample of the kind of file - it has two tables before and after and I want to able to validate that whatever is "before" table is correct against whatever appears in table "after".
Thanks,

 

[recyan]

Have faced this problem a few times in the past & used to do it in somewhat below crude manner :
Just check if it gives some guidelines :

SELECT 
	tablebefore.Name, 
	tablebefore.Transit, 
	tableafter.Transit, 
	IIf([tablebefore.Transit]<>[tableafter.Transit],"NotOK","OK") AS CheckTransit, 
	tablebefore.Amount, 
	tableafter.Amount, 
	IIf([tablebefore.Amount]<>[tableafter.Amount],"NotOk","Ok") AS CheckAmount, 
	tablebefore.[ledger Date], 
	tableafter.[ledger Date], 
	IIf([tablebefore.ledger Date]<>[tableafter.ledger Date],"NotOk","Ok") AS CheckLedgerDate, 
	tablebefore.[ledger Account], 
	tableafter.[ledger Account], 
	IIf([tablebefore.ledger Account]<>[tableafter.ledger Account],"NotOk","Ok") AS CheckLedgerAccount
FROM 
	tablebefore 
	INNER JOIN 
	tableafter 
	ON 
	tablebefore.Name = tableafter.Name
WHERE 
	(((IIf([tablebefore.Transit]<>[tableafter.Transit],"NotOK","OK"))="NotOk")) 
	OR 
	(((IIf([tablebefore.Amount]<>[tableafter.Amount],"NotOk","Ok"))="NotOk")) 
	OR 
	(((IIf([tablebefore.ledger Date]<>[tableafter.ledger Date],"NotOk","Ok"))="NotOk")) 
	OR 
	(((IIf([tablebefore.ledger Account]<>[tableafter.ledger Account],"NotOk","Ok"))="NotOk"));

or

SELECT 
	tablebefore.Name, 
	tablebefore.Transit, 
	tableafter.Transit, 
	IIf([tablebefore.Transit]<>[tableafter.Transit],"NotOK","OK") AS CheckTransit, 
	tablebefore.Amount, 
	tableafter.Amount, 
	IIf([tablebefore.Amount]<>[tableafter.Amount],"NotOk","Ok") AS CheckAmount, 
	tablebefore.[ledger Date], 
	tableafter.[ledger Date], 
	IIf([tablebefore.ledger Date]<>[tableafter.ledger Date],"NotOk","Ok") AS CheckLedgerDate, 
	tablebefore.[ledger Account], 
	tableafter.[ledger Account], 
	IIf([tablebefore.ledger Account]<>[tableafter.ledger Account],"NotOk","Ok") AS CheckLedgerAccount, 
	IIf([CheckTransit]="Ok",IIf([CheckAmount]="Ok",IIf([CheckLedgerDate]="Ok",IIf([CheckLedgerAccount]="Ok","Ok","NotOk"),"NotOk"),"NotOk"),"NotOk") AS FinalCheck
FROM 
	tablebefore 
	INNER JOIN 
	tableafter 
	ON 
	tablebefore.Name = tableafter.Name;

PS : This assumes that your tablebefore and tableafter have the same names in both & the same number of records ( hence the INNER JOIN ).
Also, if this happens to solve your problem, do remember to come back & check, David or someone else might have a better & easier way of doing it.

Thanks

 

[shoro]

Thanks, a lot mate! will give it a shot today to see if it works fine and will let you know.
Cheers!