Importing Excel Data and Comparing to Existing Data Records

[Dean]

I have a database that tracks personnel activity for various tasks.
The main table of the database is tblActivity and it has only 8
fields, 1 being the Autonumber ID. TblActivity is populated by
importing time record entries exported to Excel from our accounting
software. Time record data is exported from the accounting software
into Excel using date range criteria. There is no guarantee that the
data exported to Excel from the accounting software is not duplicated,
in whole or in part, in tblActivity. Currently, the imported data is
temporarily stored in tblTemp until it can be validated for accuracy.
Both tables have the same structure.

I'd like to be able to compare the contents of tblTemp to tblActivity
and copy only unique records from tblTemp to tblActivity. There are 2
scenarios as I see it. The first is when the database is void of data
and Excel data is imported into tblTemp. TblTemp is then compared to
tblActivity (which is empty) and all records are appended to
tblActivity. The second scenario exists for all remaining imports in
which data exists in tblActivity, the imported data in tblTemp is
compared to that data, and only non-duplicated records are imported.

I've tried this with a Union query, Counting Duplicates, & an Append
query using a date range criteria and that works (sort of the Union
query bonks out in code due to the parameters). But I'd like to
eliminate the criteria and just look at each record and append new
data.

Thanks for your help!

Dean

 

[Ken Snell [MVP]]

Which fields in the tables are the ones you'd use to decide if a record is a
duplicate? You can use a query that returns records that are not duplicated,
and use that as the data source for an append query. Tell us more details
about these fields, and we should be able to help you with setting up a
single query to do what you seek.

 

[Hans Up]

I'd like to be able to compare the contents of tblTemp to tblActivity
and copy only unique records from tblTemp to tblActivity. There are 2
scenarios as I see it. The first is when the database is void of data
and Excel data is imported into tblTemp. TblTemp is then compared to
tblActivity (which is empty) and all records are appended to
tblActivity. The second scenario exists for all remaining imports in
which data exists in tblActivity, the imported data in tblTemp is
compared to that data, and only non-duplicated records are imported.

If both tblActivity and tblTemp have a unique field, you can use that
field to identify which tblTemp records are present in tblActivity and
discard them.

DELETE
FROM tblTemp AS t
WHERE t.uniq_field In (Select uniq_field FROM tblActivity);

However, if you want to examine the datasheet view for that query,
Access will need at least one item in the field list:

DELETE t.uniq_field
FROM tblTemp AS t
WHERE t.uniq_field In (Select uniq_field FROM tblActivity);

Either way, after deleting the duplicated rows, just run an append query
to insert the remaining records from tblTemp into tblActivity.

 

[Dean]

Which fields in the tables are the ones you'd use to decide if a record is a
duplicate? You can use a query that returns records that are not duplicated,
and use that as the data source for an append query. Tell us more details
about these fields, and we should be able to help you with setting up a
single query to do what you seek.

The main problem with the data is that there is not 1 single unique
field of the 7 "data" fields (the autonumber doesn't count). The
fields include: work date, personnel #, work hours, project info,
etc. A unique record is one that all 7 fields combined is unique. I
know that is messy, but it is imported data. I can send you the Union
query SQL if you'd like.

Thanks for your help!

 

[Ken Snell [MVP]]

Create and save a query that uses both the permanent table and the temporary
table as data source tables. Join each field to its corresponding field in
the two tables. Right-click on each join line, and select the option that
returns all records from the temporary table and only matching records from
the permanent table. Add all fields from the temporary table to the query
grid. Add any one field from the permanent table to the query grid, unselect
its Show checkbox, and use Is Null as its Where criterion.

Now create and save a new query, using the above query as the data source
table. Change this to an Append query, and select the permanent table as the
destination for the append.

This second query is the one you'd run to do the append.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Which fields in the tables are the ones you'd use to decide if a record is
a
duplicate? You can use a query that returns records that are not
duplicated,
and use that as the data source for an append query. Tell us more details
about these fields, and we should be able to help you with setting up a
single query to do what you seek.

The main problem with the data is that there is not 1 single unique
field of the 7 "data" fields (the autonumber doesn't count). The
fields include: work date, personnel #, work hours, project info,
etc. A unique record is one that all 7 fields combined is unique. I
know that is messy, but it is imported data. I can send you the Union
query SQL if you'd like.

Thanks for your help!