Many before the One

[MarkH]

In my real world situation a set of iterations takes
place, which would be the 'many' side of a linked pair of
tables and after the iterations a single Approval Date is
arrived at, which would be in the 'One' side. After this
Approval Date a permit number is generated which seemed
to me to be the best thing for the primary key since it
is very important that permit dates are not duplicated.
The problem is that I cannot begin making records of the
iterative information until I have a permit number in
this scenario. Any ideas on how to dance around this and
still maintain unique permit numbers?

 

[Lynn Trapp]

Without creating the Primary Key on the "One" side there is no way to create
records on the "Many" side that are linked to the "One" side. Therefore, you
will need to create a separate field for your Permit Number and update that
once your Approval Date has been arrived at. You can use the DMax function
to generate the next available permit number.