Unmatched Problem

[nj2la96]

I'm creating a database in Access 2007. Basically, what I'm trying to
accomplish is this...

a file gets imported from Excel into a temp table. The user views the
"new"records in the table and then decides whether to POST it to the main
table or not. The excel sheet have about 15 columns and the temp table and
main table have similar structure.

What I can't figure out is how to do an unmatched query on ALL columns and
not just one. The reason is that a record in the excel spreadsheet may be
updated (e.g the status of a serial number can be changed from "In Stock" to
"Damaged") but I want to be able to track all entries made. My final product
should be like this for example

serial1 12/1/10 Damaged (this will be imported in month2)
serial1 01/1/10 In Stock (this would be imported in month1)
serial2 03/5/10 Sold

The database should treat the 12/1/10 entry as new because the status is not
the same.

How can I do a query to compare ALL columns in the temp and main table to
produce only new or updated records??

 

[John W. Vinson]

How can I do a query to compare ALL columns in the temp and main table to
produce only new or updated records??

Do you have a Primary Key, or some other field that you can count on to
uniquely identify the record in the spreadsheet as "the same" as a record in
the table?

If so create a Query joining the main table and the temp table by this ID. Put
a criterion under each of the other fields in the Temp table of

<> [maintable].[samefieldname]

on *separate* rows of the query grid, to use OR logic.

This query will show all record which have changed value in any field. You can
use this to manually review the records, and/or change it to an update query
to update the main table to the values from the spreadsheet.

If you cannot uniquely identify which record is which, well, you're in a
pickle!