Key violation with APPEND query

[seestevecode]

Hi. I know this is a fairly common problem, and have trawled both these forums and Google to try and resolve it, but I still can't so I hoped that posting specifics of my DB might help.

I am importing data from Excel to a temporary table (with no PK) as follows:
tblTEMP
Date
Login (actual login number used)
OtherField1
OtherField2
... etc

I'm then running unmatched and matched queries to append and update the 'live' table. All seems to go well (unmatched query finds the right records, etc.) until the append process itself, when I get a Key Violation error on all records.

My reading (and a bit of experimentation) has identified that the problem lies with the Login field, and I think this may be to do with Foreign Keys in other tables, etc., but this is where I get lost. I don't know how to fix it.

The destination table is like this:
StaffLoginTelephony
StaffLoginTelephonyID [PK] [Autonumber]
StaffLoginTelephonyDate
StaffLoginID [FK] (see below)
OtherField1
OtherField2

The linked table is:
StaffLogin
StaffLoginID [PK] [Autonumber] (linked to FK above)
StaffID [FK] (linked to separate Staff table)
StaffLoginStartDate
StaffLoginEndDate
StaffLoginNumber (this is the actual login number matching in the Temp table)

[EDIT:] The foreign key is set up as a Lookup, showing First and Last Names concatenated.

I will be importing from several temp tables to the StaffLoginTelephony table - all using the same method, but just sending to different OtherFields.

I hope I've given enough information to ask for help. Please let me know if I need to provide anything else. Thanks for taking the time to look at this for me.

Steve

 

[seestevecode]

I think I've stumbled across a solution (not necessarily the best one).

I created a SELECT query pulling in all fields from the StaffLoginTelephony table except for the StaffLoginNumber, which I pulled from the linked StaffLogin table.

I then used this query, instead of the StaffLoginTelephony table, to compare to the Temp table in the unmatched query.

In the append query, I linked the unmatched query above to the StaffLoginNumber field in the StaffLogin table (showing ALL fields from the query, and only those that match in the table). I then appended all items from the unmatched query to the StaffLoginTelephony table, except for the StaffLoginID which I appended from the linked (in the append query) StaffLogin table.

Phew!! I'm not sure where that came from, but it seems to work.

The only issue I have now is that records aren't appended if they have a login in the Temp table which doesn't exist in the StaffLogin table. Does anyone know the best way to handle this? Should I start a separate thread?

If anyone can throw any light on WHY this worked, I'm sure it would still help me (and others) in the future, so I'd appreciate it. In the mean time, I hope my garbled explanation above may help someone else having similar issues.