Create textbox to fill in a set term for multiple variables

[Eirea]

I am building a database for a martial arts program for older/elderly adults. I have a section that measures their physical capabilities. I need to create a text box that will fill in if their abilities are below average, average, or above average on one of the tests. The problem is, the test measurements are based on age and a range of repetitions.

For example, men 60-64 need to do 14-19 reps of the exercise to be average and women of 60-64 need to do 12-17 reps of the exercise. Men from 65-69 need 12-17 reps and women of the same age need 11-16 reps of the exercise. I have a box for their age (on a separate table from the rest of the information) but I need to figure out how to allow someone to fill in the number of reps a person did and have if they were average, above average, or below average filled into the last box based on that number, their age and their gender.



I have no idea how to do this. I tried a combo box, but I can't seem to figure out how to create the table to get it to work. Does anyone have some suggestions on this or if it is even possible?

[ItsMe]

Data is stored in tables. Do you have any tables?

[Eirea]

Yes, I have multiple tables. The issue is that the way the data needs laid out, it makes the table I'm putting it in too complicated for people to use properly. I need a way to streamline it down so that users who aren't familiar with Access are able to fill in the age, gender and reps and have it auto-populate if they are above average, below average, or average for their category.

[ItsMe]

Users should be inputting data through forms. Forms are the public interface, not tables.

Your first step is to normalize your tables/relations and not worry about forms, for now. As you test your table structure, use queries in datasheet view. Then, move to forms to get an idea how your queries and forms will be built. At this stage, you will likely need to revisit your table structure.

[Eirea]

I started with four tables, two of which are relevant here. One table has age and gender on it, the other has the repetition number and the rating.

After organizing the tables, I built the form for the relevant data and realized that the users won't know if the number of repetitions is average, above average or below average unless it is calculated directly in Access due to the way the hard copy survey is created. I need them to be able to fill in the age, gender and number of reps on the Access form and have Access fill in the rating automatically for the users.

I thought I could create a combo box that would be able to fill in the information, but when I started to make the table for the combo box, I realized that wouldn't work because of the sheer volume of information the users would have to sort through. I need a simple way to tell Access that if their gender is male, if their age is between 60 and 64 and if they have between 14-19 reps, they have an average rating.

From what I can tell, if I create a table for this information, I need to have a line for each variant of the information--so female, 60, 1 rep, below average; male, 60, 1 rep, below average; female, 60, 2 reps, below average; male, 60, 2 reps, below average; and so on and so forth. Because I would need to go from ages 60 to 94, covering at least 1-20 reps for each gender, it seemed to get a little over sized and I thought a properly written expression might resolve the issue more easily.

[ItsMe]

...From what I can tell, if I create a table for this information, I need to have a line for each variant of the information--so female, 60, 1 rep...

You are going to need an additional table or two. When you say, "One table has age and gender on it, the other has the repetition number and the rating.", I see a normalization issue. The table with age and gender should probably also have info regarding the client's personal profile/contact info. Fields such as FirstName, LastName, Address, Gender, Phone, Age, etc. The table that stores the number of repetitions performed should be an events table. Something happens, an event, where the client performs a task. The results should be time-stamped, labeled and the activities logged. The rating would be in its own table, like a lookup table (not to be confused with lookup fields).

Then back to the variables and calculating the appropriate rating based on the variables logged in the events table. I would use some VBA to formulate the variables and place the relative key value from your lookup table with the Ratings. This Primary Key value would be stored in the relative record for the events table.

You would not need a record or special formula for each repetition performed, year of age, or every combination therein. You would most likely use ranges of ages and reps, and a Case Select statement.