I am using transferspreadsheet to import data from an excel
spreadsheet to an access table. I am using a query as the argument to
the "Table Name" for transferspreadsheet. The query (qryOrders) is
comprised of the fields productID, name, city, county, state. The
field city is pulled from a lookup table and the field county is
pulled from a lookup table.
If I am trying to import a spreadsheet in which 3 records have the
same county, the import works for the all the fields, but it will add
the county 3 times to the end of the countyLookup table. If the
county already exists in the lookup table, I don't want a duplicate
added to the end of the table everytime a record has that lookup
value. I tried to set that field to "no duplicates", but then
transferspreadsheet will reject the entire record if county already
exists in the lookup table.
Any suggestions?

You say you want to reject the duplicate record. Yet you complain,

Transferspreadsheet will reject the entire record if county already
exists in the lookup table.
I'm confused. I thought you WANTED to reject the duplicate record.

Maybe what you meant to say was this:


Transferspreadsheet will reject the entire SPREADSHEET if county already exists in the lookup table.

If this 2nd scenario is the case, then you might want to first transfer into a temp table, and, from there, insert only the non-dup records into the real table.


INSERT INTO LookupTable
SELECT * FROM TempTable AS TT
LEFT JOIN LookupTable as L
ON TT.County = L.County
WHERE L.County IS NULL

This can also be written like this (but the first way is faster)



INSERT INTO LookupTable
SELECT * FROM TempTable AS TT
WHERE TT.County NOT IN (SELECT County FROM LookupTable)