relationships

[nx69]

I want to make a form where I will type the race number and in a subform below i will be able to select horses to participate.
One horse can participate only in one race and in one race can participate many horses.
To achieve this i believe i need one more table, linking tblRaces and tblHorses(thanks pat for this).
What this table structure should be? (fields, primary key, look up etc.)

Also if someone knows, will i need one more table linking tblRaces and tblBets?
In the bets forms i will have to select a race and in a subform below the horses(that only participate in that race) i choose to bet on.

Thanks in advance.

 

[Mile-O]

I'd have thought it was a many to many as I don't see one horse running one race in its career.

Also, Won?, Name, and Date are bad field names: the former as it uses a non-alphanumeric character, and the latter pair becasue they are Reserved Words.

 

[nx69]

re:

I'd have thought it was a many to many as I don't see one horse running one race in its career.

After every session (9 or 10 races for example) is completed, the races' records will be deleted and new race records will created for the next session. What i wanted to say is that a horses runs only once every session.

Also, Won?, Name, and Date are bad field names: the former as it uses a non-alphanumeric character, and the latter pair becasue they are Reserved Words.

Won? will be used to locate bet wons for a report.
Date, is for history purposes
and Name?

However, what do you suggest?

 

[Mile-O]

After every session (9 or 10 races for example) is completed, the races' records will be deleted and new race records will created for the next session. What i wanted to say is that a horses runs only once every session.

I don't understand why you don't just set up a proper relationship that will allow you to keep all the historical sessions' data.

Won? will be used to locate bet wons for a report.
Date, is for history purposes
and Name

However, what do you suggest?

Won
DateOfBet
Forename

 

[nx69]

Done with the races form

But now how do i built my Betting form?

take a look at the changes i made

 

[Mile-O]

Is Horse a text field that you've made part of a relationship? If so, bad choice. Joins on text take longer to index due to amount of bytes the text takes up.

 

[nx69]

I want to make a form where I will type the race number and in a subform below i will be able to select horses to participate.
One horse can participate only in one race and in one race can participate many horses.
To achieve this i believe i need one more table, linking tblRaces and tblHorses(thanks pat for this).
What this table structure should be? (fields, primary key, look up etc.)

Also if someone knows, will i need one more table linking tblRaces and tblBets?
In the bets forms i will have to select a race and in a subform below the horses(that only participate in that race) i choose to bet on.

Thanks in advance.

You are absolutely right. Apologies.

 

[nx69]

Relationships

This is how my relationships look now.

 

[nx69]

Thanks Pat.