CODEWITHSUNDEEP
sqldatabasems-access
A Fish
A Fish

Reputation: 13

Error message when executing Insert Into query in Access

I have a query where I am looking to take data from one table and insert that into another table if specific criteria are met -

the first table (Temp_Day_Table) has columns: Emp_ID_FK, Time_ID_FK, Hours, Description, Day - it is not indexed and has no key - it's essentially a temporary table where all data will be deleted after the insert into query has executed.

the second table (EMP_TIME) is has the columns: EMP_ID_FK, TIME_ID_FK, Hours, Date_Time, EMP_TIME_DESC. The primary key is comprised of EMP_ID_FK, TIME_ID_FK, and Date_Time.

The intention of the query is to insert all values from Temp_Day_Table into EMP_TIME if there is a value in 'Hours' and the values for the primary key do not already exist.

The query seems to work as expected, however when it's executed, I get a pop-up error message that reads as follows:

Employee Time can't append all the records in the append query.

Employee time set 0 field(s) to Null due to a type conversion failure, and it didn't add 4 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

Do you want to run the action query anyway?

Specifically it indicates that the query didn't add (#) records to the table due to key violations - the error message gives the option to ignore the errors and run the query anyways, and when doing this, the query executes as expected and doesn't produce any duplicates in the end table (EMP_TIME) - I don't understand why I'm getting the key violation if the query is not attempting to insert a primary key duplicate. It is attempting to insert duplicates of parts of the primary key (ie EMP_ID_FK) but not the entire key - I'm sure I'm missing something as I'm still early in my sql & access adventure but just looking to resolve the error.

here is the query being executed:

INSERT INTO EMP_TIME ( EMP_ID_FK, TIME_ID_FK, Hours, Date_Time, EMP_TIME_Desc )
SELECT t.Emp_ID_FK, t.Time_ID_FK, t.Hours, t.Day, t.Description
FROM Temp_Day_Table t

left join EMP_TIME on t.EMP_ID_FK = EMP_TIME.EMP_ID_FK
and t.Time_ID_FK = EMP_TIME.TIME_ID_FK
and t.Day = EMP_TIME.Date_Time

where t.Hours <> 0;

Again, the query seems to operate as expected, but Access is throwing a key violation error and I'm not sure exactly why.

Thanks in advance.

Upvotes: 1

Views: 303

Answers (1)

Bohemian
Bohemian

Reputation: 425358

Separate the two criteria by using NOT EXISTS for the key check:

INSERT INTO EMP_TIME ( EMP_ID_FK, TIME_ID_FK, Hours, Date_Time, EMP_TIME_Desc )
SELECT t.Emp_ID_FK, t.Time_ID_FK, t.Hours, t.Day, t.Description
FROM Temp_Day_Table t
WHERE NOT EXISTS (
  SELECT 1
  FROM EMP_TIME x
  WHERE t.EMP_ID_FK = x.EMP_ID_FK
  AND t.Time_ID_FK = x.TIME_ID_FK
  AND t.Day = x.Date_Time
)
AND t.Hours <> 0

Upvotes: 1

Related Questions