transferspreadsheet query - adding duplicates to lookup tables

[abstractva]

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?

 

[Arvin Meyer [MVP]]

Try changing your query to DISTINCT (Unique Values in the query properties)

Also, understand that a database only has rows and fields, the intersection
of which does not mirror a cell in a spreadsheet. The use of Lookup Fields
in a table is to be discouraged.:

http://www.mvps.org/access/lookupfields.htm

instead, use a lookup table and do the actual lookup in a query, rather than
in the table.

 

[abstractva]

I have found out the hard way about lookup field pitfalls, just a few
application revisions too late. But thanks for confirming.

The current implementation is a main table with fields [orderID],
[orderDate],[name],[address],[city],[county]
I have a city table with fields [cityID],[propertyCity]
I have a county table with fields [countyID],[propertyCounty]

I made a query(qryImport) within access that has the main table, with
both the city and county tables as 'right' joins. (from the SQL view),
e.g, qryImport.orderID, ...qryImport.address, qryImport.propertyCity,
qryImport.propertyCounty

I then made a transferSpreadSheet macro that use qryImport as the
table argument
The spreadsheet to be imported has the same field names as my query.
The spreadsheet has 10 records with the same propertyCity and 5
records with the same propertyCounty.

The transferspreadsheet macro seems to import the main table
correctly. But when I look at the propetyCity and propertyCounty
tables, I see that the propertyCity table has 10 additional entries of
the same city and the table propertycounty has 5 additional records of
the same county. The city and counties that were added already
existed in the propetycity and propertycounty tables.

I tried no dupes on the propertycounty and propertycity tables, but
the macro would not add the records due to rules violation.

I'm not sure how to apply the select DISTINCT to both the propetycity
and propertycounty fields in the query?? Won't this eliminate some
records from being added?