one form many tables

[gbmarlysis]

Hi,
We need a db for our athletics club.
i have split the tables into three categories

1) Athlete Table
-Athlete ID (primary key)
-Name
-Age
-Sex

2) Race Table
-Race ID (primary key)
-Date
-Age Division
-Distance
-No. of entrants
-Track

3) Race Results Table


-Race result ID (primary key)
-Position
-Coach (changes regularly)
-Time

I put the Race Id and Athlete Id in the Race Results Table and created a 1 to many link.

Is this correct?
Our main reason for the post is we want to create one form for entering in all the data.

First put in the athletes name, if it doesnt automatically fill in the other boxes then put in their info.
second put in the race they just ran in, again if its already in a table autfill in the boxes but if not then fill in the details.

Then a list of all the races logged for them to come up on the form so you can see all their entered races.
If current race hasn't been entered then put in position, time etc then enter.

So if we put in a new athlete
or a new athlete with a new race description with a new result
or an entered athlete with a new race description with new result
or an entered athlete with an entered race description but new result
or just check what races of an athlete have been entered

could that all be done on one form
Many many thanks in advance for people taking the time to try and help us.
If i have been unclear let me know.
TY
LB

[aytee111]

Yes it can be all done on one form but I wouldn't recommend it. It will make the form very complex and hard to manage. Here is another suggestion:

Choose the detail table to be your record source for your form. Have a combo box drop down for the athlete with a button next to it for "New". The user can click on that button if he wants to add a new athlete, otherwise he must select one from the drop down. This button can either show a subform off to the right, using the visible Yes/No values, or else make a small pop-up form come up when the button is pressed. Do the same for the race details. This keeps forms simple and easy to troubleshoot and maintain.

(Some people like to make things as complicated as possible to give themselves challenges, some people - like me - prefer the KISS philosophy, Keep It Simple, Stupid!)

[June7]

All depends on data structure and how much programming you want to do. The more 'user friendly' the more code will be required. I have one simple project that by using form/subform/subsubform arrangement and bult-in record search/filter tools, no code (macro or VBA) is involved. Has a total of 6 tables and 12 forms (1 main form and the rest are subforms/subsubforms).

I think the relationship is really many-to-many. Each participant can have many races and each race has many participants, hence the requirement for the junction table.

[gbmarlysis]

hi June
whats a junction table and how would i apply it
ty
lb

[June7]

The junction table is RaceResults. You have done this correctly.

[gbmarlysis]

Hi June,
how then can i create one form that lets me add
1- the athlete ( if their in there already then fill in the rest of the fields eg. dob
2- the race info ( if thats in there already then fill in the rest of the fields eg. date, distance)
3- the details of the athlete for that race (and to see what has been entered already a table at the bottom of the form for that athlete showing all their races and results).
if at any stage its a new athlete, race or race detail being added then enter into the tables..
Thanks alot June
lb

[June7]

You have started another thread on that question. Let's close this one.

[gbmarlysis]

Hi Aytee,
i added athlete id and race id to the race details table. When i created the form and used race and athlete as a drop down list the names and races dont get recorded in the athlete id and race id in the race details table.. Any idea on how to do this..
TY
Lb

[June7]

Have you bound the form to the RaceResults table? I.e., set the form RecordSource?

[gbmarlysis]

yes the main form is bound to the race results table, but when i click on properties for the drop down boxes i dont have record source come up and both drop down boxes say they are unbound
ty
Lb

[gbmarlysis]

Hi,
first mini breakthrough, changed the control source to the linking relationship in the athlete and race tables. And it worked. Next step how to add a new entry button for athlete and race that brings up a new form.
TY

[June7]

You can use the design wizards and let them create embedded macro code for you. Or learn VBA.