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 ?

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