Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Overwrite records (2000)

    I hope I can explain this problem correctly:
    I have a table with all the records I will call original table.
    I have another table that has some of the records that the original table has but these records have updated data that I want to overwrite the original records with.
    I created a query with an outer join on the original table.
    I brought all the fields from the original table and the other table with the changes so I could see how many I had to overwrite.
    How do I get the updated records from table 2 to overwrite the records in the original table?
    This problem started as an Excel spreadsheet that was divided and sent to different people to make changes to.
    Now they want the changes matched to the original data - leaving what is not changed and overwriting what is changed.
    Your help is greatly appreciated.

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

    Re: Overwrite records (2000)

    Do you have an ID field or something similar that identifies the records uniquely?

    What should happen if several people have edited the same record, so that there are different modifications to that record?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Overwrite records (2000)

    The key field is Client Number. There was another field in the original spreadsheet (Rep). That is how the original spreadsheet was distributed - by Rep.
    So far I created a query using the original table and the table with the records that changed. I did an outer join on the table that has the changed records so I have 92 records that need to replace the original records in the original table. I joined on the Client Number and brought all the changed fields from the changed records table.
    I need to delete those records (92) Client numbers from the original table and then append the 92 changed records that I have in the query I created. How can I do this?

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

    Re: Overwrite records (2000)

    One possibility: don't delete and append, but use an update query:
    - Create a backup copy of the database, so that you can go back if things go wrong.
    - Create a query based on the original table and the table with the changed records.
    - Join them (with an inner join) on the Client Number.
    - Add all fields from the original table to the query grid.
    - Select Query | Update Query.
    - In the Update To line, enter the following in each used column:

    [NameOfChangedTable].[NameOfField]

    substituting the correct names, of course.
    - Select Query | Run.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Overwrite records (2000)

    As always - worked like a charm. Thanks for your help.

Posting Permissions

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