Based on a values in a form, refernce a table and auto-complete a second field

[ttam73]

I have a form (named: APFT) which has a field (named: age) with the age of the person and a field (named: 1PU Raw) in this field you enter the number of push-ups completed. Based on the value entered into this field (Example: 40) push-ups, I'm trying to find a way (not VBA savey at all and hope a Macro or Expression can do this) to reference a table (named: Male Push-Ups) that has one field (named: REPS) which would be the equivalent to the 40 push-ups and multiple other fields based on age (named: 17YR; 18YR; all the way to 56YR). The form has '1PU Raw' which would be the equivalent to the table 'REPS'. Based on the age (Example: 32) entered into the form it would have to look up which field to reference in the table (Example: 32YR). The end state is to have a 'PU Score' field in the Form: APFT which looks up the value from the table, again all based on AGE and REPS.

I have attached a diagram, hope it helps.

Presentation1.zip

[ssanfu]

I looked at your attachment. Based on the picture, it appears that you are doing what is termed "committing spreadsheet". This means that the table design is "short and wide" (like a spreadsheet) instead of "tall and narrow". In the database world, rows are cheap, fields are expensive.

You have data (the ages) in the field names is not a good design. You say that the fields go out to "56YR" (approx 40 fields). So you get all of the queries, forms, functions/subroutines and reports completed. But now someone (or you yourself) need/decide to have the age go up to 75. Because of the table structure, you have to modify the the queries, forms, functions/subroutines and reports.

But Access is not a spreadsheet. Access is a database. And database tables are "tall and narrow".
In a properly normalized table, the number of fields in table would be 4:

Reps
Age
Gender
PUScore

Now your table looks like:

Reps	Age	Gender	PUScore
70	17	M	99
69	17	M	97
68	17	M	96
67	17	M	94
66	17	M	93
70	18	M	99
69	18	M	67
68	18	M	96
67	18	M	94
66	18	M	93
70	19	M	99
69	19	M	97
68	19	M	96
67	19	M	94
66	19	M	93

Obviously, the rows would continue to age 56. (there would be 16 rows per age) This same table could also hold the female PU scores..
With this table design, if (when) you want to add ages up to 75, it is just a matter of entering the data. No redesign of the queries, forms, functions/subroutines and reports.

(not VBA savey at all and hope a Macro or Expression can do this)

Using the above table structure, you could use the DLOOKUP() function to get the PUScore.

[ttam73]

I looked at your attachment. Based on the picture, it appears that you are doing what is termed "committing spreadsheet". This means that the table design is "short and wide" (like a spreadsheet) instead of "tall and narrow". In the database world, rows are cheap, fields are expensive.

You have data (the ages) in the field names is not a good design. You say that the fields go out to "56YR" (approx 40 fields). So you get all of the queries, forms, functions/subroutines and reports completed. But now someone (or you yourself) need/decide to have the age go up to 75. Because of the table structure, you have to modify the the queries, forms, functions/subroutines and reports.

But Access is not a spreadsheet. Access is a database. And database tables are "tall and narrow".
In a properly normalized table, the number of fields in table would be 4:

Reps
Age
Gender
PUScore

Now your table looks like:

Reps	Age	Gender	PUScore
70	17	M	99
69	17	M	97
68	17	M	96
67	17	M	94
66	17	M	93
70	18	M	99
69	18	M	67
68	18	M	96
67	18	M	94
66	18	M	93
70	19	M	99
69	19	M	97
68	19	M	96
67	19	M	94
66	19	M	93

Obviously, the rows would continue to age 56. (there would be 16 rows per age) This same table could also hold the female PU scores..
With this table design, if (when) you want to add ages up to 75, it is just a matter of entering the data. No redesign of the queries, forms, functions/subroutines and reports.



Using the above table structure, you could use the DLOOKUP() function to get the PUScore.

Thank You, I took your advice and re-established my tables. Now I'm trying to figure out based on entries I put into the form (Example: 'REPS' 66, 'Age' 19, 'Gender' M) would automatically return the 'Score' 93 in a 4th field?

[ssanfu]

I would write a function to get the score.
An alternative is to use the DLOOKUP() function.
The control source for the text box would be

(air code)

=DLOOKUP("ScoreFieldName", "TableName", "Reps = " & Me.RepsControlName & " AND Age = " & Me.AgeControlName & " AND Gender = '" & Me.GenderControlName & "'")

Change the names in blue to your field/table/control names.

[ttam73]

I tried to work it out, but it still doesn't work, I have attached a simple example of the database I am working with. There is one form (APFT) which after all fields are filled out the DLookup (assuming I will write this as an expression in the '1PU Score' form field) will reference the appropriate table, in this case the PU table to automatically fill in the last field '1PU Score', I hope this helps and appreciate any help you can provide. Thank You.

[June7]

Age, Reps, Score fields in each table need to be number type, not text. I think the Time field should also be a number in decimal minutes.

I entered some dates for DOB.

I used your existing APFT Query in another query:
SELECT [APFT Query].[Soldier Data].SSN, [APFT Query].Age, [APFT Query].Gender, [APFT Query].[1PU Raw], PU.Score
FROM PU RIGHT JOIN [APFT Query] ON (PU.REPS = [APFT Query].[1PU Raw]) AND (PU.Age = [APFT Query].Age) AND (PU.Gender = [APFT Query].Gender);

DLookup is an alternative to the compound join in query but domain aggregate functions can be slow, especially in larger dbs.

I would say make all the exercise tables one table with another field for the exercise type.

The APFT table is not a normalized structure.