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.

Re: Import Excel and Compare (2003 SP2)

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.

Re: Import Excel and Compare (2003 SP2)

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.

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.