forms - dropdown lists based on record data

[The Boondock Saint]

Howdy Everyone.. Hope the day is treating you all well.

Im trying to get 2 drop down lists into a form....
The form has a field called "id" this is a number... for each record....
using data from 1 table...

Id like to be able to bring in data from another table called players....
players has the following fields -

id - (which is just a autonumber field to keep everything in order)
game id - for each game
team - this will either be home or away
player1 - Players Name
player2 - Players Name
player3 - Players Name

so example data in the table "players" might be

1 - 1 - home - Rabbit - Sheep - Cow
2 - 1 - away - Soup - Noodles - Eggs

where the gameid = id from the other table...
one dropdown list will be for the home team players so it will have , Rabbit
, Sheep, Cow
the other dropdown list will be for away team players so it have , Soup
Noodles , Eggs in the drop down list....

Have i done this the right way... im new to databases.??

down the line id like to add in more players for teams with more than 3
players.... but at this stage id just love to get my head around how this
can be done.

Thanks for your thoughts and ideas.
The Saint

 

[Ron2005]

change your table to be:

home - Rabbit
home - Sheep
home - Cow
Away - Soup
Away - Noodles
Away - Eggs

then have query for dropdown boxes have constraint of Home and away
respectively.

This way you can add as many home and away entries as your heart
desires and never have to change the file or system.

 

[The Boondock Saint]

would i also have to have the gameid because there will be than just one
game... and so more than just 1 home...

 

[Ron2005]

If you are trying to limit the possibilities down to a particular game
then yes. I was concentrating on the aspect of players being vertical
and therefore chosable from a dropdown. If they were fields on the same
record then you could not put them in a dropdown nor easily add to them.