Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Notre Dame, Indiana, USA
    Thanked 0 Times in 0 Posts

    update make-table query (Access 2002/ Windows 2000 svcpk3)

    I'm working with a non-Microsoft, non-SQL database. I copy tables and open them in Excel, then import into Access. Because we're entering new data daily, I need to update my Access tables every day. To complicate matters, the original table, which is an ads database, has more than one record for each ad number, because it lists extra charges individually under the same ad number as the ad itself. Thus, a given ad may have up to five or more "records". I have been creating a "no-duplicates" table by copying the structure of the original Excel table, then setting the ad number as the primary key, then running an Append Query to the new table. When it tells me there are x number of duplicates that will not be appended, I say ok, and end up with a table I can work with.

    The problem is that I need to update the table on a daily basis. What is the best way to get new data into my "no-duplicates" table? If I have to go through this whole process every time, so be it, but I'm hoping there is a way to select out non-duplicate data and append it to the existing table.

    Thanks for any help or advice you can offer. We're already shopping for an ODBC compliant back end system, but may not have it for another 18 months or more.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 Posts

    Re: update make-table query (Access 2002/ Windows 2000 svcpk3)

    I would suggest you simply import the data from excel to a temporary table in Access each day. Then create a query which only creates one record for each ad record - you do that by setting the property Unique Values of the query to Yes, and do an append to the permanent table. Then you delete the data in the temporary table, and you are ready for the next day. Is there a reason to paste into Excel - if not, you could try pasting directly from your ads database into the temporary table.

Posting Permissions

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