Key violations in append query (1 Viewer)

[YasminaisReal]

Hello! I am fairly new to access and trying to append data from a new table that I created to an existing linked table. I'm working in the front end of a split database. The existing table has one primary key (Plankton_survey_ID) which is an autonumber. It also has two foreign keys (contact_ID and Monitoring_site_ID).
From what I understand, if the primary key is an autonumber then I do not include that field in an append query because it will autopopulate when the new data is added to the table.
When I try to run my append query, it says there are 240 key violations (and no other issues). I am trying to append 240 records, so this is every single record. This is the SQL for my query:

INSERT INTO tblMonitoring_Plankton_Tow ( Monitoring_site_ID, Collection_date, contact_ID, [Total_tow_distance_(meters)], Number_of_tows, [Total_tow_volume_(m^3)], towType, Comments )
SELECT DWRPTupload2023.Monitoring_site_ID, DWRPTupload2023.Collection_date, DWRPTupload2023.contact_ID, DWRPTupload2023.Total_tow_distance, DWRPTupload2023.Number_of_tows, DWRPTupload2023.[Total_tow_volume_(m^3)], DWRPTupload2023.towType, DWRPTupload2023.Comments
FROM DWRPTupload2023;

I have populated the contact_ID field and Monitoring_site_ID fields with the number corresponding to a record in each table. For example, if Bob Smith is a contact in the Contacts table and his ID is 123, I entered 123 into the contact_ID field in the table I created. I did the same in Monitoring_site_ID field.

How do I fix these key violations so that I can get this append query to run?

Thank you!

 

[CJ_London]

Check all your indexes in the table- only your primary key should be set to no duplicates

 

[YasminaisReal]

I did double check this and the primary key is the only field set to no duplicates, which is why I'm puzzled! I also checked the new table I made and the contact_ID and monitoring_site_ID fields were indexed (duplicates OK). I set these to not indexed and tried running the query again and ran into the same issue.

 

[CJ_London]

Have you set relationships?

 

[YasminaisReal]

I'm not quite sure what you mean. The existing table I'm trying to append to has relationships with two other tables, the contacts table and the monitoring sites table. The foreign keys that link these tables are the contact_ID and the monitoring_site_ID. Unfortunately since I'm working from the front end I'm not able to view the relationships.

 

[CJ_London]

it is a bad idea to use spaces and non alpha numeric characters in field and table names (they can cause misleading errors). usually square brackets overcome the issue, but not always. If you want these, use the caption property or alias them

Also try inserting just one record and check the data

 

[CJ_London]

One other thought - do you have a multi field/composite index set to no duplicates? - click the indexes button on the table design ribbon to check

 

[ebs17]

Previous question: The table you are looking at and using in the query is the same? Sometimes your own perception gets lost, you can go crazy if you look in the wrong place and think everything is right.

Otherwise, @CJ_London asked all the important questions. Index errors happen for two reasons:
(1) Violation of unique index, whereby composite indexes must be included in the consideration.
(2) Violation of the established referential integrity of the existing relationships. Only foreign keys that exist as primary keys in the associated primary tables can be written to the secondary table (n-table).
These foreign key fields must not be defined with a default value of 0. If there are no foreign keys in the assignment, which is allowed, this 0 would certainly not be present as the primary key.

If you continue to have problems, upload a stripped-down sample database (extract from the backend).

 

[CJ_London]

Unfortunately since I'm working from the front end I'm not able to view the relationships.

so open the back end

 

[Pat Hartman]

Also, look at the design of your tables. Current versions of Access default all numeric fields to 0 rather than null as it did in the past. This is 100% WRONG when the field is used as a foreign key. So, make sure that all FK's are defined to default to null and not zero.