Help,

I am losing records in one of my tables (access97) vb5 as a front end. I intend to make a copy of the table and have the program notify which records when a record is added or removed. I can then also keep a backup table in another db that keeps all records as they are added.

What I want to do is to compare one table with the other and show me the additions and deletions etc. Does any one know how to compare 2 like tables using a sql statement

Many thanks

Will the backup table contain all records - including the deleted records from the primary table ? Or will the backup table only keep transactions of what is added/deleted to/from the primary table ? Are you using this to recover lost records ?

Many thks for the quick reply,

Yes the Backup table will be a pristine state. That is all records will be kept. The reason being is I intend to keep another table with records removed so that if a recovery is necessary again I can replace all missing records and then remove ones that have been legitimately deleted by looking at the items removed.
You see the problem is I cannot find anywhere in the program that records are being deleted except for where they are suppposed to be. The program then logs these "official" removals. However records are going and there is no transaction record of them being deleted. The database is password protected.

I hope this convoluted explanation makes it a bit clearer for you.

Many Thanks again.

I have included 2 examples - both are using the order details table from the northwind database - the backup is called od2:

This query uses a left outer join from od2 to order details - this will allow you to pull all records that are in od2 even if no match occurs. The key here is that the select statement uses orderid from order details as well and if there is no match this field will be blank:

select od2.*,o.orderid
from od2 left outer join [order details] as o
on ((od2.orderid = o.orderid) and
(od2.productid = o.productid) and
(od2.unitprice = o.unitprice) and
(od2.quantity = o.quantity) and
(od2.discount = o.discount))

You can also add a where clause to this statement to return only rows that exist in od2 but not order details:

where o.orderid is null

This query will only select records that exist in od2 but not order details:

select od2.*
from od2
where not exists
( select * from [order details] as o where
((od2.orderid = o.orderid) and
(od2.productid = o.productid) and
(od2.unitprice = o.unitprice) and
(od2.quantity = o.quantity) and
(od2.discount = o.discount)))

I have included all fields for comparison because I do not know your table structure. But basically you will have to keep up with updates of all fields - otherwise it will be interpreted as a delete, when in fact it is not. The exception would be if you have a unique identifier (primary key(s) on your tables) that is never reused - even when deleted, then you would not have to worry about validating against all fields (just the primary key field(s)) and any updates. Also, remind yourself if the primary key has to updated for any reason, you will have to make the same modifications to the all-inclusive table.

Good luck.

Many many thanks for the proposed solution and the fast response. I will start on it straight away.
Sometimes you dont se the wood for the trees, I was thinking of using a join in the tables but the solution just would not come.

Thanks again

Hi Mealejr,

I tried the code but it did not work. this is the actual code I used;

Set rst = MyDb.OpenRecordset("SELECT CQ2.*,[Contractor Id] " _
& "FROM CQ2 left outer join [Contractor Qualified] AS CQ " _
& "ON ((CQ2.[Contractor Id] = CQ.[Contractor Id]) AND " _
& "(CQ2.[Code Type] = CQ.[Code Type]) AND " _
& "(CQ2.[Area] = CQ.[Area]) AND " _
& "(CQ2.[Rate Above Par] = CQ.[Rate Above Par]) WHERE " _
& "CQ.[Contractor Id] Is Null")

Am I missing something?

Regards

Success, Almost

I was able to get a recordset using this;

Set rst = MyDb.OpenRecordset("SELECT CQ2.*,CQ.[Contractor Id] " _
& "FROM CQ2 left outer join [Contractor Qualified] AS CQ " _
& "ON CQ2.[Contractor Id] = CQ.[Contractor Id] AND " _
& "CQ2.[Code Type] = CQ.[Code Type] AND " _
& "CQ2.[Area] = CQ.[Area] AND " _
& "CQ2.[Rate Above Par] = CQ.[Rate Above Par] WHERE " _
& "CQ.[Contractor Id] Is Null")
With rst
While Not .EOF
Debug.Print !CQ2.[Contractor Id]
.MoveNext
Wend
End With

but when i try to print it my syntax is wrong. Also how would I be able to create a new table rather than create a recordset

Thanks again