Append query problems (1 Viewer)

Hi, having got my sql in some sort of order in a previous post I am trying to insert the sorted fields into another table.
Testing the code without the "Insert" clause it works fine, but when I come to try and insert the data into a table I get a warning from Access that it can't append all the files (see attached picture).

I have attached picture of the data as is appears in it's initial form imported from an excel s/s, and a picture of the data sorted as desired in the table but I need to get passed this hang up.

The fields Job Code, EmpName and WeekEnding are all set as a combined foreign key and are Indexed.

I have tried varying the Index properties for the fields in the table all to no avail.
Has anyone a direction they can point me in please?
Code as below:

the clue is in the error message - 'didn't add 4 records due to key violations'

Keys are indexes - so the implication is the data you are trying to insert would break the indexing rules. These rules are based on your index and/or table design. Typically it will be because you are trying to insert duplicates when duplicates are not allowed or insert null values where nulls are not allowed - and this might also apply to fields in the destination table you are not appending a value to.

Note that (although your query isn't sorting, but you mention it in your post) sorting records prior to insert has no benefit - records are stored randomly within a table regardless of order of insert. If they do actually appear in the same order, that is a coincidence rather than by design.

Also you query should not be a group by query because you are not summing/counting etc data. And if you are using it as a way of removing duplicates then your having criteria should be a where criteria - or use select distinct instead

Okay, so I've pruned the sql somewhat to this, the idea being to remove all the blank cells before inserting them into the main table, Tbl_Costing:
I understand the key violations warning but couldn't understand why I was getting it. in the table Tbl_Costing I have a 3 part foreign key comprising the fields Job Code, EmpName and WeekEnding which primarily is to stop users of the excel sheet sending the data multiple times.
The key doesn't seem to be working though as I can count 4 instances, same number in the key violation warning, where a EmpName occurs more than once, yet each time it has a different Job Code so in theory should be exempt from the key violations.
Bit puzzling this one.

SOLVED FOR NOW- WILL POST LATER IF ONLY A TEMPORARY FIX

Okay, here we go!
I pruned my sql code to this:

I found out the frustrating way that Excel, from where I am sending data to a db, DOES NOT LIKE the Nz function, hence the IS NOT NULL.

I created a 3 way unique index of Job Code, EmpName and WeekEnding to prevent data being re-sent and it works, hallelujah!

So there we are, after all the blood and frustration it works.
Thanks to CJ for their help on this one.