Many-Many junction table using tables with multiple key fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would appreciate guidance on how to proceed with this design. My skills
level with Access 03 is between beginner and intermediate depending on what
task I am trying to perform. This is my first foray into multiple keys and
many-to-many relationships together. I can do one primary key with no
problem and understand how to create multiple keys. I can do junction tables
with a single key per table. I apologize this is a little long. I am trying
to be very clear of my need.

2 tables and a junction table

1st Table: [Procedures] keeps data regarding procedures that are used for
performing engineering activities.
- Procedures.Number is a unique text value that uniquely identifies the
procedure (such as "X31-20-40-028"). There can never be more than one
procedure with this value. However, a procedure may have many revisions.
- Procedures.Revision is a text value describing a specific version of a
given procedure (such as 0.7). Revision is not unique. Many procedures may
have the same revision number (such as 0.0, 1.2, 3.0). However, a given
Procedures.Number may not repeat Procedures.Revision numbers.
- Other fields keep other data.

2nd Table: [Requirements] keeps data regarding documents that are used for
capturing specific requirements that [Procedures] implement.
Requirements.Number is a unique text value that uniquely identifies the
document (such as "Rqmts-23").
Requirements.Revision is a text value describing a specific version of a
given requirements document (such as 1, 2, 3). It is not unique. Many
requirements documents may have the same revision number (such as 1, 2, 3).
However, a given Requirements.Number may not repeat Requirements.Revision
numbers.
- Other fields keep other data.

3rd table: a junction table to perform a many-many relation.

Rules:
- Data for [Procedures] is entered manually initially.
- Data for [Requirements] is entered manually initially.
- One Procedure can implement many Requirements documents.
- One Requirement document can be used in many Procedures.
- Procedures.Number and Procedures.Revision combine to make a unique
identifier, hence multiple key fields. There will only be one record for a
given number and revision.
- Requirements.Number and Requirements.Revision combine to make a unique
identifier, hence multiple key fields. There will only be one record for a
given number and revision.

Need:
The junction table appears to be the place to form the link between specific
multi-key fields of both [Procedures] and [Requirements]. This will allow
tracking of a given procedure/rev to a given requirements/rev.

Help:
Please consider I have not used multi-key fields to form a many-many before.
I'd appreciate either a detailed walkthrough or an example that fits this
need.

Thank you in advance for giving your time and effort to help me out with this.
 
What about using a multi-field index set to unique an Autonumber as primary
key.

facmanboss said:
I would appreciate guidance on how to proceed with this design. My skills
level with Access 03 is between beginner and intermediate depending on what
task I am trying to perform. This is my first foray into multiple keys and
many-to-many relationships together. I can do one primary key with no
problem and understand how to create multiple keys. I can do junction tables
with a single key per table. I apologize this is a little long. I am trying
to be very clear of my need.

2 tables and a junction table

1st Table: [Procedures] keeps data regarding procedures that are used for
performing engineering activities.
- Procedures.Number is a unique text value that uniquely identifies the
procedure (such as "X31-20-40-028"). There can never be more than one
procedure with this value. However, a procedure may have many revisions.
- Procedures.Revision is a text value describing a specific version of a
given procedure (such as 0.7). Revision is not unique. Many procedures may
have the same revision number (such as 0.0, 1.2, 3.0). However, a given
Procedures.Number may not repeat Procedures.Revision numbers.
- Other fields keep other data.

2nd Table: [Requirements] keeps data regarding documents that are used for
capturing specific requirements that [Procedures] implement.
Requirements.Number is a unique text value that uniquely identifies the
document (such as "Rqmts-23").
Requirements.Revision is a text value describing a specific version of a
given requirements document (such as 1, 2, 3). It is not unique. Many
requirements documents may have the same revision number (such as 1, 2, 3).
However, a given Requirements.Number may not repeat Requirements.Revision
numbers.
- Other fields keep other data.

3rd table: a junction table to perform a many-many relation.

Rules:
- Data for [Procedures] is entered manually initially.
- Data for [Requirements] is entered manually initially.
- One Procedure can implement many Requirements documents.
- One Requirement document can be used in many Procedures.
- Procedures.Number and Procedures.Revision combine to make a unique
identifier, hence multiple key fields. There will only be one record for a
given number and revision.
- Requirements.Number and Requirements.Revision combine to make a unique
identifier, hence multiple key fields. There will only be one record for a
given number and revision.

Need:
The junction table appears to be the place to form the link between specific
multi-key fields of both [Procedures] and [Requirements]. This will allow
tracking of a given procedure/rev to a given requirements/rev.

Help:
Please consider I have not used multi-key fields to form a many-many before.
I'd appreciate either a detailed walkthrough or an example that fits this
need.

Thank you in advance for giving your time and effort to help me out with this.
Click to expand...
 
For this purpose, you will find it easier to use an autonumber primary key
and create a unique index for the compound natural key to enforce the
business rule. The reason being that the junction table is most easily
built by using a combobox and a combobox only works with a single unique
identifier so a table that requires a compound primary key will be difficult
to work with if you want to use a combo to select the related records.

facmanboss said:
I would appreciate guidance on how to proceed with this design. My skills
level with Access 03 is between beginner and intermediate depending on
what
task I am trying to perform. This is my first foray into multiple keys and
many-to-many relationships together. I can do one primary key with no
problem and understand how to create multiple keys. I can do junction
tables
with a single key per table. I apologize this is a little long. I am
trying
to be very clear of my need.

2 tables and a junction table

1st Table: [Procedures] keeps data regarding procedures that are used for
performing engineering activities.
- Procedures.Number is a unique text value that uniquely identifies the
procedure (such as "X31-20-40-028"). There can never be more than one
procedure with this value. However, a procedure may have many revisions.
- Procedures.Revision is a text value describing a specific version of a
given procedure (such as 0.7). Revision is not unique. Many procedures may
have the same revision number (such as 0.0, 1.2, 3.0). However, a given
Procedures.Number may not repeat Procedures.Revision numbers.
- Other fields keep other data.

2nd Table: [Requirements] keeps data regarding documents that are used for
capturing specific requirements that [Procedures] implement.
Requirements.Number is a unique text value that uniquely identifies the
document (such as "Rqmts-23").
Requirements.Revision is a text value describing a specific version of a
given requirements document (such as 1, 2, 3). It is not unique. Many
requirements documents may have the same revision number (such as 1, 2,
3).
However, a given Requirements.Number may not repeat Requirements.Revision
numbers.
- Other fields keep other data.

3rd table: a junction table to perform a many-many relation.

Rules:
- Data for [Procedures] is entered manually initially.
- Data for [Requirements] is entered manually initially.
- One Procedure can implement many Requirements documents.
- One Requirement document can be used in many Procedures.
- Procedures.Number and Procedures.Revision combine to make a unique
identifier, hence multiple key fields. There will only be one record for a
given number and revision.
- Requirements.Number and Requirements.Revision combine to make a unique
identifier, hence multiple key fields. There will only be one record for a
given number and revision.

Need:
The junction table appears to be the place to form the link between
specific
multi-key fields of both [Procedures] and [Requirements]. This will allow
tracking of a given procedure/rev to a given requirements/rev.

Help:
Please consider I have not used multi-key fields to form a many-many
before.
I'd appreciate either a detailed walkthrough or an example that fits this
need.

Thank you in advance for giving your time and effort to help me out with
this.
Click to expand...
 
Thanks.
I'm pretty sure I understand w/o playing with the db. Is there an example db
of this just so I don't spin my wheels needlessly?
Much appreciated.
Ross
 
The Order entry form in Northwind is an example of how a many-to-many
relationship is implemented. A product can appear on many orders and an
order can be for many products. In your case, the "product" combo's
RowSource would be populated by a query that concatenates the two natural
"key" fields.
Select YourPK, Procedure & "-" & RevNum From your table;

Set the length field for the first field to "0" so that only the Procedure +
Rev number show in the combo.
 
Back
Top