One to Many Table Not Working (1 Viewer)

Wondering if someone can take a look at the attached DB and point out what I am missing as I have been messing with this for over 2 hours and keep chasing my tail.

Whenever I am running the EquipmentHistoryPt2 query, I am getting the Attached error message. When I look at the tables, I can't seem to find out what is causing it as everything looks right to me. I even set the query as a "Make Table" query, before changing it to an ""Append Query" to make sure the fields were the same.

Basically we work with equipment that gets transferred between facilities. While at each facility, whenever we have to do repairs, we add notes for whatever we did. This all works great, but now I want to add a history process for this that will take all existing notes and facility information and move them to a History table, before transferring the device to a new facility.

Don't mind the form layout as its just a quick mockup with only the essentials for testing this design and is only one portion of a much larger database.


EDIT: Forgot to mention that the tables the relationship isn't working correctly for are the EquipmentHistory and EquipmentHistoryNotes tables and the dbo_XXXX tables are linked tables from my production DB converted to local tables and stripped down to only 1 record, not that I would suspect that would make any difference.

1. Launch the dbo_FacilityEquipment Form
2. Run the EquipmentHistoryPt1 Query
3. Run the EquipmentHistoryPt2 Query

Thanks in Advanced

NoteID is a primary key so you cannot append another note with the same id - just leave it off your append query

So are you saying because it's a PK in the original Notes table it can't be appended as a "Number" in the history notes table?

It's not set as a PK in the NotesHistory table, and if it is, I apologise as I must have uploaded the wrong copy.

Also each Note ID is already a unique value due to it being a PK in the original table so even if it was set as PK, wouldn't it still be valid?

sorry - misread the db.

The reason is that in relationships you have enforced referential integrity on the historyID field. But you are not populating it in your update query so it defaults to 0 - and there isn't a historyID of 0 in your EquipmentHistory table.

unenforce referential integrity and the query works. If you need the link, then you need to find a way of populating it

delete the [EquipmentHistoryNotes] table from the db.
this will also delete the relationship (you don't need it).
copy [dbo_nFacilityEquipmentNotes] (only structure) and rename it [EquipmentHistoryNotes].
edit [EquipmentHistoryNotes] changing NoteID to number (long).
add a PK field, HistoryID (Autonumber).
save the table.
edit [EquipmentHistoryPt2 Query] query.
add criteria to NoteID:
this will prevent duplicate addition of same note.