• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

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.
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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:

MuscleID - PK - Autonumber
MuscleDescription - Text

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.

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

Demo: Set the relationship between two tables

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now