Different number of fields for each record in a table

[gbmarlysis]

i have to do a database with a large amount of athletes.
the table would be Name , age, but then the other fields will vary and some athletes wont have an entry..eg some athletes have had 15 or more events and some only three... i need to put the event details in for each athlete eg put 1st run, and then under that ''where they came", the time they ran, how many runners they ran against, the date etc. How could i set this table up??

[aytee111]

You have to create separate tables, you cannot have all of this in one table, you are breaking a very important normalization rule!

Table 1 will contain athlete information, just who that person is - name, address, etc

if there are a lot of similarities then you will have a table of events, if many of your athletes run in the same race then it is best not to repeat the event information every time:
table 2 - event name, date, place, etc

table 3 will link to the athlete (and optionally the events table) and will be the details of each event they attended and the results (time, placing, etc)

Link the tables by using the Relationships screen

PS Don't store their age in a table, it is a calculated field. Keep their dob instead and calucate it as required.

[orange]

As aytee111 says, you will need to structure your tables and relationships.
It isn't that there will be a Different number of fields for each record in a table -the reality may be that all records will have the same number of fields, BUT some records will have fields with no data values.

You should research NORMALIZATION to help you with tables and relationships.

[gbmarlysis]

can fields have no data in them, if i'm doing the distance they came from each other in metres the person who came first would be 0 then second would be 2metres from first etc.. could this work

[orange]

But 0 isn't the same as nothing.

If distance is a numeric data type, it should have a numeric value.
0 is such a number, but NULL isn't

[gbmarlysis]

sorry bit messy in the format i hope this is a bit clearer..
ok thanks, ive looked up normalisation and the rules to follow but am still not confidant in what tables to set up. here is an exact example of the spread sheet i'm trying to turn into a db,
(athlete 1) "date" {position} [margin] 'time' ;trainer;

(Derek Jones (his info))
race 1 "15/11/11" {1st} [0] '11.23' ;Paul s;
race 2 "22/11/11" {3rd} [.75m] '11.54' ;Paul s;

(Matt Rogers )
race 1 "7/11/11" {4th} [1.25] '11.85' ;Sue P;
race 2 "22/11/11" {2nd} [.25] '11.3' ;sue P;
race 3 "29/11/11" {1st} [0] '11.22' ;Dave P;

i know some are obvious like the athlete table, but what other tables would i create and how would i link them?

[orange]

Where is the original data? What format?

[gbmarlysis]

i originally sent it layed out like my spreadsheet but it came out all messed up so i just resaved the reply, hope you can understand the layout.
i'm very knew to this so i hope the lay questions arent frustrating. The main issue i have is when different fields change eg matts trainer changed after he had two races with us.also to prevent multiple entries, matt and derek both competed in the same race at one stage, can i run a query for race 2 on the 22nd and it will show derek came 3rd and matt came second. i can see the possibilities in access i'm just having a hard time determining how i should set up the original tables , how to link them and then how to do a form that lets me put in the data entry without duplication..

[orange]

I'm trying to see enough of the raw data to see what tables and relationships are appropriate.
Before you get to queries, we have to have tables set up and related; then populate the tables.

I'm seeing multiple rows, and each field with different delimiters....who/how did that happen?

Where is the data at this point?

[aytee111]

Here is a Q&D (quick and dirty) but you must use your imagination and try and figure out what will be needed in the future.

Athletes_tbl
AthleteID - autonumber (primary key)
AthFName - Text
AthLName - Text
AthDOB - Date
AthGender - Text

Events_tbl
EventID - autonumber (primary key)
EventDate - Date
EventLocation - Text
EventStartTime - Date
EventLength - number (miles?)

RaceDetails_tbl
RD_ID - autonumber (primary key)
AlthleteID - number
EventID - number
RacePosition - number
RaceTime - Date
RaceMargin - ? is this a number?
RaceTrainer - Text

Then go to the Relationships window, add the three tables, join them by matching the pertinent ID fields (drag and drop)

You can do it!