Calculation using Table Data

[scoobz1234]

Good Afternoon!



I am trying to build a Fitness tracking database, where i am trying to input a calculated item. there are two inputs and i want an out put, i put in Height and Weight, and then i have a TBL that has Height then the maximum weight that applies to that height...

so TXT BOX 1 = HEIGHT
TXT BOX 2 = current Weight
TXT box 3 = MAX Weight

my tbl looks like this

58" 131lbs
59" 136lbs
60" 141lbs
61" 145lbs

What im trying to do is have the form read automaticaly what i put in TXT BOX 1 and enter in automatically the MAX weight for that height.

[ssanfu]

A quick and dirty way is to use the Dlookup() function.
Example:
a table name of "tblWeights", with fields "Height" and "Weight"
A form named "Form9", controls (textboxes) named "CurHeight", "CurWeight" and "MaxWeight".

The control source for the textbox "MaxWeight" would be:

=DLookUp("[Weight]","tblWeights","[Height] = " & [Forms]![form9].[CurHeight])

Change the table name, field names and control names to your names.


The reason I say "Quick and dirty" is there is not any error handling and it is harder to do verification of the input data.
I think a better way would be to write a UDF .

[scoobz1234]

Can this be done using a queiry?

table names are

tblBCP - this is the table all data is stored for the individual IE current height current weight sex age ect...
tblFemaleHeightWeightStandards - this is the table where the heights and the MAX weights are stored for Females
tblMaleHeightWeightStandards - this is the table where the heights and the MAX weights are stored for Males

the form im using is
frmPersonellWorksheet

so i created a Query that has HEIGHT from tblBCP MAX from tblFemale.... and MAX from tbl Male....

i used a criteria under the HEGHT column in the query like this [forms]![frmPersonellWorksheet]![Text216]

so as of now it shows one line of information HEIGHT that i have inputted and female max and male max.
problem is i need it to read and only show wether the person is male or female and show its respective max.

and then put it in the MAX text box...

is this too complicated to do? is there an easier way?

[ssanfu]

Not sure I really understand. But here goes nothing......

FYI stuff:
You don't really need two tables to store the max weights. It is the same type of data, so have a field for "Height", a field for "MaxFemale" and a field for "MaxMale".
You should always take the time to name your controls. Is it easier to read "[Height]" or "[Text216]"?


For a text box control, the DLookup() would look like this:
(this should be one line)

=IIf([Gender]="M",DLookUp("[Weight]","tblMaleHeightWeightStandards","[Height] = " & [Forms]![frmPersonellWorksheet].[Text216]), DLookUp("[Weight]","tblFemaleHeightWeightStandards","[Height] = " & [Forms]![frmPersonellWorksheet].[Text216]))

In a query:

Max: IIf([Gender]="M",DLookUp("[Weight]","tblMaleHeightWeightStandards","[Height]  = " & [Forms]![frmPersonellWorksheet].[Text216]), DLookUp("[Weight]","tblFemaleHeightWeightStandards","[Height] = " &  [Forms]![frmPersonellWorksheet].[Text216]))

Change "Gender" to your field name.
I don't know what you use for male, "M" or "Male". If you use "Male", in the expression, change "M" to "Male".

[scoobz1234]

For a text box control, the DLookup() would look like this:
(this should be one line)

=IIf([Gender]="M",DLookUp("[Weight]","tblMaleHeightWeightStandards","[Height] = " & [Forms]![frmPersonellWorksheet].[Text216]), DLookUp("[Weight]","tblFemaleHeightWeightStandards","[Height] = " & [Forms]![frmPersonellWorksheet].[Text216]))

In a query:

Max: IIf([Gender]="M",DLookUp("[Weight]","tblMaleHeightWeightStandards","[Height]  = " & [Forms]![frmPersonellWorksheet].[Text216]), DLookUp("[Weight]","tblFemaleHeightWeightStandards","[Height] = " &  [Forms]![frmPersonellWorksheet].[Text216]))

Change "Gender" to your field name.
I don't know what you use for male, "M" or "Male". If you use "Male", in the expression, change "M" to "Male".

im trying to use the DLOOKUP code, i am using this;

=IIf([GENDER]="M",DLookUp("[Weight]","tblHeightWeightStandards","[Height] = " & Forms!frmPersonellWorksheet.Height))

now i did put the two tables into one, and named it tblHeightWeghtStandards, inside that table there are fields named HEIGHT, FEMALE MAXIMUM, MALE MAXIMUM

i also renamed my text boxes for easier handling,

the height is named HEIGHT
the max weight is named MAXWEIGHT

but inside the text box i see #error....do you have any ideas what i have thats wrong?

i have also tried

=IIf([Gender]="M",DLookUp("[MALE MAXIMUM]","tblHeightWeightStandards","[Height] = " & [Forms]![frmPersonellWorksheet].[HEIGHT]), DLookUp("[FEMALE MAXIMUM]","tblHeightWeightStandards","[Height] = " & [Forms]![frmPersonellWorksheet].[HEIGHT]))

[scoobz1234]

Good Afternoon again!

I have changed my approach on this subject. i created a Qry that combines the two tables together (main data table and my table with max weights) i set up relationships between the actual height to the height on the max weight table. then it puts in the data for me. now i linked this qry to my form.

this has solved partially my issue. when i put in the HEIGHT the MAX weight text box does not update until i close the form and reopen it. i am trying to get it to do this update using the afterupdate function...however i dont wanna lose my place on the record, IE i tried using a macro that closed the form and reopened it but it would bring me from record 6 back to 1..

[ssanfu]

On form "frmPersonellWorksheet", do you have a text box named "Gender"? (Check your object names. )

Other suggestions...
- you have a field named "Height" and a control named "Height". Gets a little confusing what you are referring to. I try and use different names. The control I would use "tbHeight" (tb = textbox) or "CurrentHeight".

- it is not a good idea to use spaces in object names. I would use "FemaleMaximum". If you must separate the words, use an underscore - "Female_Maximum".

Attached is the mdb I created... (Access 2000)

[scoobz1234]

ok i think i see where the miscommunication is...

my tbls are as follows

TblMAIN - this table has id,lastname,firstname,mi,gender,ssn,height,weight, bodyfat, and a few others
TblHeightWeightStandards - this table has ID,HEIGHT,MAXWEIGHT,GENDER

my query compares the TBLMAIN records gender and height with the data in TBLHeightWeightStandards and then i end up with

all that is in TblMAIN and an additional Field MAXWEIGHT

[ssanfu]

Like this??

[scoobz1234]

Useful things.zip

this should help, this is my DB. the problem im having is once i enter a new height in for someone on the form, it doesnt update the MAX.

[ssanfu]

I'll look at it when I get home tonight...

[ssanfu]

Well, I made a lot of changes. Took spaces out of field names and changed field types. I changes a couple of embedded macros to event procedures.

"SSN" should be a text type because you don't do calculations on it and you need leading zeros.
"Age" should be calculated (and not stored) - next year the age will be different.
"Last4" can be calculated and shouldn't be stored.

I modified the query - the Dlookup for the max weight is returning values. I tried to set up the max BF percentage... but I don't know enough about the BF % table. I do think you will have calculation problems because the max weight table has Height as an integer (69) and the BF table has Height as a single (69.5).

The combo box for Marine search shouldn't be bound. (I fixed it)

[June7]

Consider this query:

SELECT tblBCP*, tblHeightWeightStandardsCondensed.MINIMUM, tblHeightWeightStandardsCondensed.MAXIMUM
FROM tblHeightWeightStandardsCondensed RIGHT JOIN tblBCP ON (tblHeightWeightStandardsCondensed.HEIGHT = tblBCP.HEIGHT) AND (tblHeightWeightStandardsCondensed.GENDER = tblBCP.Gender);

Could probably include tblMale_Standards similarly. What circumference is measured - neck?

Not considering age factor in determining standards?

[ssanfu]

Thanks June, the query is a much better method. Wasn't thinking well enough at 1 am to try and create the query.

Also turned off "Autocorrupt" (autocorrect), added another button (Move first), changed the form record source to the new query and converted the embedded macros to VBA.

[scoobz1234]

Thanks for all your help! However; the data does not update itself. my problem right now is when i put in a height the MAX weight on the form doesnt update.. i have to close out completely and re-open, navigate to the person and then its updated.

i have tried before but i could never figure out the calculation for age. (probably extremely simple)