Does anyone know how to write a query that will tell me which fields are left remaining, in a comparison between a master table and a lesser table which contains the same field (account number)?
The lesser table gradually fills up with the account numbers that are on the master as visits to the sites are completed.
I am using MS Access for this.
I can't remember how to do this.
My brain has turned to cheese.

select stuff from master
where not exists
(select 1 from lesser
where acctnum=master.acctnum)

you could also try

select stuff from master
except
select stuff from lesser

which is standard sql, but i have no idea if access supports this yet -- access 97 didn't

rudy
http://rudy.ca/

Use this:

SELECT master.fieldx
FROM master
WHERE master.fieldx NOT IN
(SELECT corresponding_field_in_lesser_table
FROM lesser)

Make sure that u are reffering to the same field from both tables. As in, may be u should use the account numbers field from both tables.


The 1st SELECT statement picks records that are not in the 2nd SELECT statement.
The 2nd SELECT statement picks up all the data from the lesser table.