Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    17

    Unanswered: Urgent Table Comparison

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  3. #3
    Join Date
    May 2002
    Posts
    17
    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.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  5. #5
    Join Date
    May 2002
    Posts
    17
    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

  6. #6
    Join Date
    May 2002
    Posts
    17
    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

  7. #7
    Join Date
    May 2002
    Posts
    17
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •