Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Excel and Compare (2003 SP2)

    I need to import a table from Excel (representing sale items) into a database. The records in the Excel sheet contain a Reference number (RefNo), as do the items in the database.

    I need to display a table showing each item that has been imported from the Excel sheet, followed by the corresponding item (with the same RefNo) that already exists in the database. If no item currently exists in the database then the Excel item shows by itself. The user selects a check box to determine which version to keep, then runs code to remove the one not selected. If neither item is selected, neither is deleted and both items should be displayed next time the import is run.

    I

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

    Re: Import Excel and Compare (2003 SP2)

    If you allow two items with the same RefNo to remain, you could over time end up with larger numbers of items with the same RefNo. Is that acceptable?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel and Compare (2003 SP2)

    Hi Hans,

    Yes, that is acceptable.
    cheers,

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

    Re: Import Excel and Compare (2003 SP2)

    In that case:
    - Add a Yes/No field ToDelete to the Access table.
    - Import the Excel table into the Access table. Since the ToDelete field doesn't exist in the Excel table, it will be False in all imported records.
    - Create a select query that sorts the table on RefNo.
    - Create a delete query that deletes all records from the table for which ToDelete is True.
    - Create a form based on the query, preferably a continuous form.
    - Put a command button in the form header or footer that executes the delete query, then requeries the form.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel and Compare (2003 SP2)

    Thanks Hans,
    Concise and accurate as always - thanks for the advice.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel and Compare (2003 SP2)

    My next issue related to this is that if I download the items into a spreadsheet (eg to create a price list) before the Delete Records code has been run, I only want the earliest version of an item to be downloaded.

    So if I have 3 records with the same RefNo, all datestamped with the date they were loaded into the database, then I want the earliest version to be exported when I create a price list.

    How could this be accomplished? I can

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

    Re: Import Excel and Compare (2003 SP2)

    1) Create a Totals query based on your table.
    Add the RefNo and datestamp fields.
    Set the Total option for RefNo to Group By (the default) and for the datestamp field to Min.
    Save this query as (for example) qryMinDate.

    2) Create a new query based on the table and on qryMinDate.
    Join the table and query on RefNo and on the datestamp field vs the MinOf... field.
    Add all fields (or *) from the table to the query grid.
    Use this query for exporting.

Posting Permissions

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