Find records that are in one table, but not the other

[Baard Dahl]

Hallo,

I have two identical tables, which show our clients
revenue in two different years.

I need to find out which clients we have lost (In table A,
but not B) and which are the new clients (Not in table A,
but in table B)

Can anyone offer any help?

 

[Brian Camire]

You might try using the "Find Unmatched Query Wizard", which you can select
from when you create a new query.

In general, the SQL one way of doing this kind of thing might look something
like this:

SELECT
[Table Containing All Records].*
FROM
[Table Containing All Records]
LEFT JOIN
[Table Containing Some Records]
ON
[Table Containing All Records].[Related Field 1] = [Table Containing Some
Records].[Related Field 1]
AND
[Table Containing All Records].[Related Field 2] = [Table Containing Some
Records].[Related Field 2]
AND
..
..
..
AND
[Table Containing All Records].[Related Field N] = [Table Containing Some
Records].[Related Field N]
WHERE
[Table Containing Some Records].[Primary Key Field] Is Null

 

[Jeff Boyce]

Baard

The Access Query Wizard has a "unmatched query" helper. You can use this to
find rowIDs in table1 that are not in table2.

Good luck

Jeff Boyce
<Access MVP>