I've got several tables: People, Orders, Deliveries, etc. All of which have a AutoNumber field as the primary key.
What I'd like to do it make a generic "Comments" table, that I can apply on all the forms (so people can leave comments).
Presently, the only way I can see to do this is to have a separate comments table for each data table:
- People (pk:PersonID) -> PeopleComments (fk:PersonID)
- Orders (pk:OrderID) -> OrderComments (fk:OrderID)
- Deliveries (pk: DeliveryID) -> DeliveryComments (fk: DeliveryID)
- ...etc.
But given that these "Comments" tables have identical fields (name, date, comment), it'd be nice if I only had to have ONE such table, that could store all comments for all records regardless of whether they come from People, Orders, or Deliveries.
But for this, I'd need some sort of global unique record identifier (a "URID") to use as a foreign key so I could tie in any generic record to the "Comments" table.
- People (URID) -> Comments (fk:URID)
- Orders (URID) -> Comments (fk:URID)
- Deliveries (URID) -> Comments (fk:URID)
- ...etc.
Is it possible to do this sort of thing in Access?
Any tips/advice/links much appreciated!
MM