Compare Tables, Produce Report Highlighting Differences 1

[NWildblood]

I need to query two tables to establish differences in stored data and updated data received from customers, which I have done succesfully using join properties within a query; I then output the result to a report (xls)

For reasons of volume, what would be really useful is to get some code that flags up the specific changed fields rather than simply churning out entire rows of data where changes have occured - checking them proves v time consuming.

Does anyone have any ideas ? I have limited SQL knowledge but can code in VB a bit. Any pointers gratefully received... thanks !

"No-one got everything done by Friday except Robinson Crusoe...

 

[GSCaupling]

OutofShape: The "Find Unmatched Query Wizard" may solve your problem.

good luck.

 

[NWildblood]

GSCaupling
Thanks, yes I have used that, but what I need then is any changed fields in table B to be highlighted so that I can see differences "at a glance"...

"No-one got everything done by Friday except Robinson Crusoe...

 

[BTSTeam]

This may be a bit clumsy if there are a lot of fields to compare but I use a query which compares fields named {Tel No] and [Costcode] in two different tables and outputs a comment if it finds records that do not match or are null:

SELECT [Table1].[Tel No], [Table1].[Notes], [Table1].Costcode, "Number Deleted" as Comment
FROM [Table1] LEFT JOIN [Table2] ON [Table1].[Tel No] = [Table2].[Tel No]
WHERE (([Table2]![Tel No] Is Null))

UNION SELECT [Table2].[Tel No], [Table2].[Notes], [Table2].Costcode, "New Number" as Comment
FROM [Table2] LEFT JOIN [Table1] ON [Table2].[Tel No] = [Table1].[Tel No]
WHERE (([Table1]![Tel No] Is Null))

UNION SELECT [Table2].[Tel No], [Table2].Notes, [Table2].Costcode, "Record Amended" as Comment
FROM [Table2] INNER JOIN [Table1] ON [Table2].[Tel No] = [Table1].[Tel No]
WHERE ((([Table2].Notes)<>[Table1]![Notes])) OR ((([Table2].Costcode)<>[Table1]![Costcode]));

[Table1] is this months data and [Table2] is last month so if [Tel No] in Table2 is null then the comment is "Number Deleted", if [Tel No] in Table1 is null then the comment is "New Number", and so on. I then use the query output to display this information in a report.

 

[NWildblood]

BTSTeam
Thanks, that looks good - will investigate later and get back with results !

"No-one got everything done by Friday except Robinson Crusoe...

 

[NWildblood]

BTSTeam
This looks perfect - yes it is a sophisticated database we have, but I am certain this will get the report we want - major time saver I predict; I am starring you for a great post 1 thanks, have a great weekend ;-)

"No-one got everything done by Friday except Robinson Crusoe...

 

[JBinQLD]

Is this the same post that was in another forum on this site too? If so, and the database is complex I think the VBA idea of looping through tables would be quicker and more sustainable than this approach?

If I'm mistaken, my apologies!

JB

 

[NWildblood]

JBinQLD
It is, but this will get the report off the ground while I get round to writing the code, assuming the contract lasts that long ! Thanks for your response too.

"No-one got everything done by Friday except Robinson Crusoe...