1 - Many Relationship -Primary key

[sbyrne]

Hello all,

I have two tables with a 1-many relationship. Each have an autonumber as a primary key. Is there any way that on the many table, the primary key can be reset for each record on the 1-table?

To explain - an incident will get a reference number eg 895. Each incident will have multiple notifications. Ideally, I would like the notifications to be numbered from 1 up for each incident thereby requiring the notification primary key to be a contactenated key (895-1, 895-2, 896-1) etc.

Any assistance would be greatly appreciated.

Regards,
Sinéad

 

[Norie]

Keep the Autonumber as the primary key in the notification table.

Add another field, a foreign key, which corresponds to the primary key of the incident the notification is related to.

You can then get all the notifications for each incident using this field.