Import only unique rrecords - SQL (or an Append Query) Access 2010

[knarlyd]

Im trying to import only unique records but have yet to be able to figure out the correct SQL statement (using Access 2010):

INSERT INTO [current] ( [First], [Last], Phone, [E-mail address], Alias, [Country/Region], LastName )
SELECT DISTINCT tblGlobalAddressListImport.First, tblGlobalAddressListImport.Last, tblGlobalAddressListImport.Phone, tblGlobalAddressListImport.[E-mail address], tblGlobalAddressListImport.Alias, tblGlobalAddressListImport.[Country/Region], current.LastName
FROM tblGlobalAddressListImport LEFT JOIN [current] ON tblGlobalAddressListImport.[Last] = current.[LastName]
WHERE (((current.LastName) Is Null));

First off, I think E-mail address would probably be a better field to check for unique values?


When I run the above statement, I get an error: MS Access can't append all the records...and it didn't add 1600 record(s) to the table due to key violations

I've removed indexes from all but the "current" table (which has ID as autonumber), checked for primary keys in foreign table.
Unique is set to No, everywhere I can find, Allowed zero length,Yes

I've been stuck for a couple weeks+

Should be easy I think but I don't have hardly any SQL building experience
Can anyone offer corrections to the statement please?

[NTC]

well you are jumbling things together

make a stand alone Distinct query that runs correctly - leave out the Append until this task is clearly working right. I would use the Query Design View rather than code because it is easier to test stand alone......

then in regard to your Append problems - first, create that standalone also and use the Distinct Query as the record source; the error message typically is indicative of a data mismatch such as text into a date field...or text into an autonumber field....or duplicates into a field not allowing duplicates...or nulls into a field that has the property 'required'.... basically you might have to set it up field by field; add a field, run the append query, add the next field and then run the append query - - and see which one is the guilty field.... sometimes it just comes down to grunting thru it.....