Compare Records from two tables (1 Viewer)

I'm having a hard to describing this as i have attemted to write this post a few times now. Basicaly whats going on is as follows.

We currently have a in office database that is modified from day to day to keep up with the current going on's of the office .We also have an organization database that we can view but it is not quite as current as we ourselves can not update those records and must wait on the paper work to go through its proper routes before updates are made to this database by the appropriate people.

We are able to have a view of the database records that are pertanent to us download from the organizational database to our in office database.

What my boss wants me to do is compare the two tables by taking a record from one then taking the users SIN of that record and querying the other table with that SIN if that member exists then he wants me to compare each field to see if they match if they do then fine if not then create a report showing the two records and how they are different.

god i hope that was clear ive create a vb program that does this but i was wondering if there was a better way for me to deal with this situation the query will have roughly 2 tables will both have roughly 15 fields containing only text.

Thanks for the Help

Jerry

Basically after re thinking my post and to make a long story short what i ned to do is take two tables within one database and use the unique field to relate them together then compare every field in a specific record to make sure that all data is identical.If it isnt i then need to output that record to a report.

Look for help on Unmatched query wizard. You should be able to query your data out and do your checking and send output to a report.

Or you could use VB and snatch the record via SQL and do some sort of looping to check for sub records?

Jon

I realise this is a very old thread, and I'd be very interested in looking at the vba solution. I have a similar scenario, where I need to compare an existing value with changes and produce a report. Below is a simplified version of my rather basic solution.

SELECT tblNewVersion.UniqueID, tblNewVersion.PatientID, tblNewVersion.[at-risk type], tblNewVersion.PatientForename, tblNewVersion.PatientSurname
FROM tblNewVersion INNER JOIN tblOriginal ON tblNewVersion.UniqueID = tblOriginal.UniqueID
WHERE (((tblNewVersion.PatientID)<>[tblOriginal]![PatientID])) OR (((tblNewVersion.[at-risk type])<>[tblOriginal]![at-risk type])) OR (((tblNewVersion.PatientForename)<>[tblOriginal]![PatientForename])) OR (((tblNewVersion.PatientSurname)<>[tblOriginal]![PatientSurname]));

Ideally I'd like to produce a report just listing the id, field name, candidate value and existing value, and I reckon vba would be the way to go (My scenario involves rather ore fields (but finite, < 300 records) to be compared.

Sorry to post this in a VBA forum, but it arose in my search for pointers, and the op did request a simple method.

Thanks

Mark McCall
Information Analyst, CJD
Public Health England