Forum Discussion
VBA Code & Relationship setup to populate form fields on After Update event
Compound PKs are never a good idea. You should have a PK on each table that is a single, Autonumber column. And it should be meaningless to the user.
But that doesn't mean that you can't have a composite unique index as well that can be used to enforce constraints.
Managing relationships with compound PKs is near impossible. So let's see if we can simplify this.
tblSTO has a good PK - STO_ID but tblProject should have an Autonumber PK, let's call it Project_ID.
It can also have the natural key 'ProjectNo' which should not be the PK but it can have a unique index to prevent two instances of the same ProjectNo.
tblProject already has a column STO_ID - this is the FK that relates to tblSTO.STO_ID.
Is this a One (STO) to Many Projects? or are you trying to create a Many To Many relationship? ie can one project have more than one STO?
If it's the latter, then you would be better off with 3 tables not 2 - tblSTO, tblProject and tblSTOProject. Each with a single column PK.
tblSTOProject would then have STOProject_ID as its PK and two FK columns - STO_ID and Project_ID.
You can then put a composite unique index on these two FK columns in tblSTOProject to apply the constraint of having only one instance of a STO for a Project.
You can then use the autonumber PK of tblSTOProject to join to tblDeliverable.STOProject_ID knowing that the unique composite index ensures that STO combined with Project is unique.
Then you could put another composite unique index on tblDeliverable including STOProject_ID and Service_Name to ensure you can only have one instance of a Service for each combination or STO and Project.
So flick the Compound PKs, use single column Autonumber PKs and use composite unique indexes to enforce the business rules.
In the attached ACCDB - check out the Relationship Diagram, Indexes on each table, the query and the form.
