Many-Many junction table using tables with multiple key fields

[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.

 

[Guest]

What about using a multi-field index set to unique an Autonumber as primary
key.

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.

 

[Pat Hartman\(MVP\)]

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.

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.

 

[Guest]

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

 

[Pat Hartman\(MVP\)]

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.

 

[Guest]

Thank you. Very helpful.
Ross