Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing Databses (MS Access 2000)

    I have two databases: tblSince2000 and a database that is a subset of that database: tbl2003Only. How can I delete a record in tblSince2000 if the record also exists in tbl2003Only?

    Thanks in advance for your help!
    shihalud

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Comparing Databses (MS Access 2000)

    Do you have two databases or one database with two tables?

    I would think you would be better off just deleting tbl2003only, and keeping all your records in one table. Whatever job you use that table for can almost certainly be done by constructing a query on tblSince2000.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing Databses (MS Access 2000)

    I agree with John's post regarding the structure of the 2 databases maybe your first issue, without a little more detail that is...

    However if the 2 tables you refer to and the fields are identical you can use a Union query to create just one data table if this is what you are trying to do?? Create the Union query and use 'Union' instead of 'Union All' - best you ensure you understand what impact this has as query otherwise you may drop out records you were not expecting too.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing Databses (MS Access 2000)

    Sorry for lack of info - you're correct it is one database with multiple tables. Actually this is the back-end of a program written to accumulate names, addresses, activity, and numerous other items. However, there are some things that we need at field level that can't be done (currently) with the front-end. The tables that I mentioned are ones that I created with make table queries. Each time a person bowls in a league there is a record created in a table. I want to know everyone that has bowled since 2000, but exclude those that have been bowling since Sep 2003. So I created the 2 tables that I mentioned originally: tblSince2000 and tbl2003Only. I used the DISTINCT clause to eliminate any duplicates in tblSince2000 and tbl2003Only, now I want to eliminate any entries in tblSince2000 if they also occur in tbl2003Only. Hopefully I made this clear as mud. Thanks again.
    shihalud

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing Databses (MS Access 2000)

    John Hutchison's comment is still valid: you can delete tbl2003Only. Create a query based on tblSince2000 that selects the records for the period since September 2003, and name this query qry2003Only. You can use this query instead of tbl2003Only.

    But if you want to go ahead with your original plan: do you have some kind of unique ID field on which you can match the two tables? If so, you can create a Delete query to do the job for you:

    DELETE * FROM tblSince2000 WHERE ID In (SELECT ID FROM tbl2003Only)

    Replace ID by the name of your unique identifier.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Comparing Databses (MS Access 2000)

    You can do this with a delete query, but the exact query depends on the structure of your tables.

    Make a new query on both tables. Join the tables on the key field, presumably a unique identifier of people. double click the join line and choose : "Include all Records from tblsince2000 and only those records from tbl2003Only where the joined fields are equal." This query should then retrieve just the records you want to delete. When you are happy that it is retrieving the right records turn it into a delete query and run it.

    Make a backup copy of each table first as it is easy to delete the wrong records with delete queries.
    Attached Images Attached Images
    Regards
    John



  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing Databses (MS Access 2000)

    Thanks HansV and johnhutchinson the delete query worked perfectly, I had tried this but could not get the syntax correct. Thanks again

    shihalud

Posting Permissions

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