Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replacing Data with Import (2000)

    Hello Forum:

    I want to import data to a table from an excel spreadsheet and to replace the contents of one record with the imported record...only if there is a match. For example, I am managing a telecom rate list that has the area code and a price per area code. If I get an updated spreadsheet that changes only SOME of the pricing, I'd like to import it and have the new data replace the old where there is a match, but not touch the other records where there is not a match. Can this be done?

    Thanks,
    Craig

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

    Re: Replacing Data with Import (2000)

    1. Import or link the Excel table.
    2. Create a new query in design view.
    3. Add the Access table and the imported/linked Excel table.
    4. Join them on the Area Code field. This will make the query work with matching records only.
    5. Select Query | Update query.
    6. Add the Price field from the Access table to the query grid.
    7. In the Update to line, enter [Excel Table Name].[Price Field Name], substituting the correct names, of course.
    8. Select Query | Run.
    If you save the query, you can reuse it later.

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replacing Data with Import (2000)

    Thanks Hans...but that wouldn't leave me with an updated table with the updated data in it, would it? Wouldn't that just give me a query result that showed the new data only?

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

    Re: Replacing Data with Import (2000)

    This step
    <hr>5. Select Query | Update query.<hr>
    means that the query is not just a query that selects records, but one that actually modifies records in a table. The step
    <hr>8. Select Query | Run.<hr>
    performs the update.

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replacing Data with Import (2000)

    Hans...YOU ARE THE MAN!

Posting Permissions

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