Comparing 3 tables in an Access Database

Question

I am trying to compare a series of tables within an access database, 2 local and one linked.

Table A (local) contains UserID, Title, Position; Table B (linked) contains UserID, Title, and Position from the previous week (records could possibly change on a week to week basis); Table C (local) contains UNIQUE UserID's and Titles.

1. I need to ensure that all UserID's contained in Table C still exist in Table A.
2. I need to ensure that all UserID's contained in Table C have not had a change in Title or Position from the previous week. If so Add to a temp table.

I'd prefer to use Access VBA or SQL in accomplish this task and the information will be displayed in a report.

Answer 1

Basically the same logic for both examples. use a left join to to identify mismatches.

1. Identify missing users in A

   Insert into TableA (userID,Title)
   select TableC.UserID, TableC.Title
   from TableC
   left join TableA on TableC.UserID=TableA.UserID
   where TableA.UserID is null
   

2. Identify changes from B to A

   insert into temp (userID,title,position)
   select c.userID,c.title,c.position
   from TableA a
   left join tableB b on b.userid=a.userID and b.title=a.title and b.position=a.position
   where b.userID is null