Highlighting/Showing unmatched fields and records

[beckyr]

I want to compare all fields in two tables (one of which has just been imported from excel) They are called tblStudents and tblSIS.
Both tables contain exactly the same information. What i am looking for is something that will tell me either if a record is missing from tblStudents (for instance a student has left) or there is a difference in some fields relating to a certain student (for example they have changed course)

Ideally what id like are these differences to be highlighted on tblStudents but i will settle for a query showing the differences.

So far i have implemented an unmatched query which shows me what students have left. Can anyone advise me on how to go about checking if fields within these two tables are different?

 

[RuralGuy]

Either an unmatched query for each field in the table or open two RecordSets (ADO or DAO) and walk through the tables comparing all of the fields in both tables.

 

[beckyr]

Thanks for your suggestion. I found a way thats meant to do it using a union query.

The idea is: GROUP the union of two tables on all columns, then if the two tables are identical in all groups will result in a COUNT(*) of 2. But for any rows that are not completely matched on any column in the GROUP BY clause, the COUNT(*) will be 1 -- and those are the ones we want.

The problem with mine is that its not showing those records with a count of 1 its showing me all of them. Can anyone see what im doing wrong?

SELECT MIN('STUDENTS') as TableName, STU_ID, STU_FORENAME, STU_SURNAME, STU_COURSE_CODE, STU_FAC_NO, STU_EMAIL, STU_STANDING, STU_TU_CODE

FROM

(

  SELECT 'STUDENTS' as TableName, tblStudents.*

  FROM tblStudents

  UNION ALL

  SELECT 'SIS' as TableName, tblSIS.*

  FROM tblSIS

) tmp

GROUP BY STU_ID, STU_FORENAME, STU_SURNAME, STU_COURSE_CODE, STU_FAC_NO, STU_EMAIL, STU_STANDING, STU_TU_CODE

HAVING COUNT(*) = 1

ORDER BY STU_ID

 

[WayneRyan]

Becky,

Maybe this will help:

http://www.access-programmers.co.uk/forums/showthread.php?t=127628

It was just an experiment, but it may be of use.

Wayne