Assigning a value with various strenght

Posted on 2014-12-27
Medium Priority
Last Modified: 2014-12-28
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.
Question by:Chrysaor
LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40519703
<<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.

LVL 120

Expert Comment

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

Demo: Set the relationship between two tables

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question