Assigning a value with various strenght

I am trying to create a database connecting the muscles of the human body in relation with the action they do.
I've created a Muscles Table and an Action Table and I created a relationship between them. Then i've created a form where I can automatically write the muscle and then the connected action. (For Example: Deltoid Muscle / Shoulder Abduction, Should Flexion).

The problem is, some muscles are contributing only to a weak extend to an action and some to a strong extend. Therefore, I would like to create another option, with which I can choose whether the muscle is Weak or Strong (regarding that particular action), or better , whether I can choose a percentage. So that, when I run a query asking which muscles are required for a certain action, that it will be shown that Muscle A contributes x %, Muscle B contributes y % etc.

How can I do this? Thanks a lot.
ChrysaorAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<How can I do this? Thanks a lot. >>

 What you have is called a "Many to Many" relationship; a muscle may have one or more actions it may be involved in, and a action may have one or more muscles involved with the action

To do this, you use a linking table:

tblMuscles
MuscleID - PK - Autonumber
MuscleDescription - Text

tblActions
ActionID - PK - Autonumber
ActionDescription - Text

tblMuscleActions - One record per unique combination of Muscle and Action
tblMuscleActionID - PK - Autonumber
MuscleID - CK1A - FK to tblMuscles
ActionID - CK1B - FK to tblActions
PercentContributed - Number - 0 - 100

'PK' is primary key, 'FK' Foreign Key, and 'CK' is candidate

 On the last table, some might design the table without tblMuscleActionID as the combination of MuscleID and ActionID  must be unique (a given muscle cannot be assigned to an action more than once).

In terms of forms, Access cannot model a M to M directly.   What you do is use a main/subform combination and do one side of the relation at a time (which is a one to many).  For example,

Main form is based on the muscle table, subform shows the records in the linking table.   In the subform, you have a combo to select the action along with a text control to enter the percent.

Then you would have a second main/subform, but for the "action" side:   Main form is based on the action table, subform again shows the records from the linking table, but now you have a combo that allows you to select a muscle.  Again a text control to enter the percent.

Jim.
0
 
Rey Obrero (Capricorn1)Commented:
to add to jim's post, watch this demo

Demo: Set the relationship between two tables
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.