Detect missing records

[Topher]

How can I use a query compare two tables with the same structure and
detect which records that are in Table A are missing from Table B?

 

[Rick B]

You can create an unmatched query (the wizard will help you).

I'd be curious to know why you have two tables with the same structure with
the same data, though.

 

[Topher]

Thanks, Rick. I will look into it. To answer your question, here it is:
I have a complex set of tables and relationships that define what
people get into what doors, and when, in an access control system. A
query generates from this the output data that gets sent to the door
controllers, which they store in local memory. When someone makes a
change in who-gets-in-where-or-when, the query will now generate a new
set of output records. Those records that have changed since the last
time need to be sent to the door controllers to update them. However, I
also need to send commands to the door controllers to delete those
records that do not exist in the output anymore. All I can think of is
to compare the output of the last query with the output of the new
query and see which records are no longer present.

 

[Topher]

Doesn't seem to work. The UnMatched Query looks for missing fields, not
missing records, as far as I can tell. I tried it various ways.

 

[Rick B]

I just tested this and it worked fine. Did you use the wizard to create an
unmatched query?

The SQL I used is...

SELECT Test2.*
FROM Test2 LEFT JOIN Test1 ON Test2.Number = Test1.Number
WHERE (((Test1.Number) Is Null));


This displayed all the records in table 2 that did not have a match in table
1

For example, in table 2 I had records with the following "number": 123,
124, 125, 126, and 127.
In table 1, I had records for the following numbers: 123, 124, 126

When I ran the query, it showed be records 125 and 127.

Perhaps there is an issue with your query. Mybe you could post the SQL here
so we can see what you have.

 

[Topher]

The Field table has 200 records in it. The Rights file is an identical
copy except that I manually deleted 20 records out of the middle of it
to do this test. I just want a list of the record in Table Field that
are not in table Rights. The Wizard asks for a field that they have in
common and I am not sure how to answer that - they are all common. The
fields are HostID, Channel, KeyID, and ScheduleID in both tables.

The wizard makes this:

SELECT Field.HostID, Field.Channel, Field.KeyID, Field.ScheduleID
FROM Field LEFT JOIN Rights ON Field.KeyID = Rights.KeyID
WHERE (((Rights.KeyID) Is Null));

For lack of a better field to pick, I select KeyID as the field that
they have in common.

 

[Topher]

Sorry, where I said "Rights file" please read "Rights table".

 

[Rick B]

That query looks correct to me.

Anyone got any ideas?

 

[John Spencer]

Since it appears the comparison has to be on all four fields, the query
needs to join on all four fields to identify records in one table that are
not in the other table

SELECT Field.HostID, Field.Channel, Field.KeyID, Field.ScheduleID
FROM Field LEFT JOIN Rights
ON Field.KeyID = Rights.KeyID
AND Field.HostID = Rights.HostID
AND Field.ScheduleID = Rights.ScheduleID
AND Field.Channel = Rights.Channel
WHERE (((Rights.KeyID) Is Null));

Assuming you want to DELETE from the Field table, you should be able to turn
that into a DELETE query
DELETE DistinctRow Field.HostId
Field LEFT JOIN Rights
ON Field.KeyID = Rights.KeyID
AND Field.HostID = Rights.HostID
AND Field.ScheduleID = Rights.ScheduleID
AND Field.Channel = Rights.Channel
WHERE (((Rights.KeyID) Is Null));

 

[Topher]

Thanks, John. That seems to work. It found fewer records than I
expected it to, but I will look into that.

 

[John Spencer]

For safety you might check all four fields for nulls. I don't think that
should be needed, but ...

SELECT Field.HostID, Field.Channel, Field.KeyID, Field.ScheduleID
FROM Field LEFT JOIN Rights
ON Field.KeyID = Rights.KeyID
AND Field.HostID = Rights.HostID
AND Field.ScheduleID = Rights.ScheduleID
AND Field.Channel = Rights.Channel
WHERE Rights.KeyID Is Null AND Rights.HostID is Null
AND Rights.ScheduleID is null and Rights.Channel is Null

Also, I am assuming that all the fields are filled with data.

 

[Topher]

OK, Guys. It looks like I got it working. Thanks a million! I owe you
both lunch!