Indefinite fields on the same record?

[darantares]

Being new to access, I think I've come a long way over the past week or two in creating a database from the ground up. However, I've hit a snag.

What the database is doing is tracking user exercises. Initially, I had a table called exercises, and a field for exercise 1, exercise 2 and exercise 3. The database user goes to the exercise form and enters the name of the 1-3 exercises. That works exactly like it should.

I want to know if a user can input an indefinite number of exercises. Sometimes they might do 1 exercise, sometimes 3. I thought about making 10 fields for exercises, but what happens if they do 11? or 20? I don't want to add another row because each row now has the people doing the exercises. Like Jon day 1 might be Row 1, Sam day 1 will be Row 2, Jon day 2 will be Row 3, etc. Each day they do the exercises adds a new row to the table (and that part is working too).



So what I'm asking is if you can put a + button or something on the forum and have it automatically add another field to that record. I've tried looking in the book I have, as well as Googling, and I haven't found anything close. Thanks for the assistance.

[recyan]

Do not know if I have understood things properly ?
What tables do you have ?
The way I see it :
The tables :

tblUsers
UserID - PK
UserName
.......

tblExercises
ExerciseID - PK
ExerciseName
.....

tblUserExercisesRecords
UserExerciseID - PK
UserID - FK
ExerciseID - FK
ExerciseDoneDate
.....

Thanks

[orange]

For further info on the concepts underlying the design suggested by recyan, investigate NORMALIZATION.

Here are a few good sites with relevant info.
http://forums.aspfree.com/attachment...2&d=1201055452
http://www.rogersaccesslibrary.com/T...malization.zip
http://www.rogersaccesslibrary.com/T...ormalForms.zip
http://www.rogersaccesslibrary.com/T...lationship.zip
http://www.rogersaccesslibrary.com/f..._Databases.zip

[darantares]

Do not know if I have understood things properly ?
What tables do you have ?
The way I see it :
The tables :

tblUsers
UserID - PK
UserName
.......

tblExercises
ExerciseID - PK
ExerciseName
.....

tblUserExercisesRecords
UserExerciseID - PK
UserID - FK
ExerciseID - FK
ExerciseDoneDate
.....

Thanks

Only two tables. The user one, and the exercises one. Perhaps a picture will help



The user enters what exercises they do, so those exercises are not pulled from a database. As you see in the pictures, there is Exercise_Activity_1, Exercise_Paramaters_1 and then Exercise_Comments_1, and then 2, and then 3.

Here is the form where they enter the data (rough draft)


What I want to know is if we add 4 or 5 exercises can a user add additional exercise (more than the 3 shown) and those will be stored in the table, as Exercise_Activity_4, and so on.

I looked over the normalization stuff, but I don't think that's what I'm trying to do. I could be wrong, but that looked more like linking tables and such.

[ssanfu]

I looked over the normalization stuff, but I don't think that's what I'm trying to do. I could be wrong, but that looked more like linking tables and such.

Normalization is exactly what you need.

Another site is "Access Basics By Crystal"
http://www.accessmvp.com/Strive4Peace/


At a minimum, I see tables:

tblPatients
PatientID - PK
NameFirst
NameLast
.......

tblExercises
ExerciseID - PK
ExerciseName
.....

tblPatientExercises
PatientExercisesID  - PK
UserID - FK
ExerciseID - FK
ExerciseDoneDate
ExerciseActivity
ExerciseParameters
ExerciseComments