Solved

Assigning a value with various strenght

Posted on 2014-12-27
2
81 Views
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.
0
Comment
Question by:Chrysaor
2 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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:

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
 
LVL 119

Expert Comment

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

Demo: Set the relationship between two tables
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

919 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now