Associate multiple records from the same table

[fret hack]

Hello All,



Im creating a database for a group in a state regulatory agency that issues permits. We often issue multiple permits for different parts of the same facility, and I need to find a way to properly associate these permits with each other. If a note is entered about one permit, I would like to propagate that note to the other associated permit records as well.

Currently I have a main table (tPermitData) in a one-to-many relationship with a few subtables. Two of the tables are:

• tPermitData_Assoc - Contains data describing which permits are associated together. Has a field containing the KeyID (non-unique) for the parent record and a field that contains the KeyID for an associated permit. If five permits are associated there are a total of 20 entries, 4 for each permit.
• tPermitData_Notes - Contains a chronology of notes written for each permit by KeyID.

My inclination is to do this with an update query tied to a command button, but Id like to know if there might be an easier, more intuitive way to enter the same note for all permits at once.

Thanks in advance.

[Micron]

A query would be the way to go if you want to propagate to more than one table, but that sounds like a duplication of data, which would be unusual and not recommended. I can't tell if there can be many "notes" for one permit, or just one. If one and it's the first, it's not an update. If one and it's a revision, it's an update. If more than one and you update, you will over-write the first. If you can have many notes for one permit, you may need a junction table between permits and notes. It would repeat the permit ID for each note, like

ID | NOTE
6 | 4
6 | 14
6 | 40
If I have that backwards and there's to be one NOTE and several permits, then reverse the example. If several of NOTE to each one of several permits, then more like

ID | NOTE
6 | 1
7 | 1
8 | 1
1 | 2
2 | 2
3 | 2

[fret hack]

Thank you for the quick reply. Its many notes to many permits.

I had considered (and tried) a junction table but didnt know how to structure it. Ill use your suggestion and try again!

[ArviLaanemets]

From your post it looks like you need a table of facilities, to which permits are linked. Something like:

tblFacilities:FacilityID, Facility
tblPermits: PermitID, FacilityID, ...
tblNotes: NoteID, PermitID, NoteDate, NoteText, ...