RowSource question

[daglugub37]

I would like to design a tblInjuries which will have a fldRosterPlayer...which will look up a field which is also looking up from yet another table.

Let me breakdown everything in the database(which is not much)
****************************
tblPlayers
fldPayerID - AutoNumber PK
fldFirst - text
fldLast - text
fldPosition - text
****************************
tblTeams
fldTeamID - autonumber PK
fldName - text
****************************
tblRoster
fldRosterID - autonumber PK
fldTeamID - Number ComboBox look up
fldPlayerID - Number ComboBox look up
fldSeason - Number
fldStarter - Yes/No
fldRookie - Yes/No
fldVeteran - Yes/No
fldRating - Number
*****************************************************
Here is the rowsource for tblRoster.fldTeamID
SELECT [tblTeams].[fldName], [tblTeams].[fldTeamID] FROM tblTeams;
Bound Column = 2

Here is the rowsource for tblRoster.fldPlayerID
SELECT [tblPlayers].[fldPlayerID], [tblPlayers].[fldFirst]+" "+[tblPlayers].[fldLast]+" - "+[tblPlayers].[fldPosition] AS Name FROM tblPlayers;
Bound Column = 1

I guess the easy way out for me would to just use the same fldPlayerID and fldTeamID scheme as in tblRoster for the tblInjuries I am trying to build. But the let's say the Seattle Seahawk's Koren Robinson gets injured. When I do the data entry I would choose the Seattle Seahawks for the Team field and then Koren Robinson for the player field.

This would be redundant as I already established that Koren Robinson is on the Seattle Seahawks.

I would love to know the right way to do this.

 

[danvlas]

tblRoster
fldRosterID - (autonumber) PK
fldTeamID - Number
fldPlayerID - Number
fldSeason - Number
fldLevel - Text or number
fldRating - Number

Now the structure of tblRoster is slightly better:
1. No lookup fields!!!
2. the fields fldStarter, fldRookie, fldVeteran are combined into one single field that can hold as many values as you want...

Your tblInjury table should be related to fldRosterID from tblRoster, as that's the bridge between tblTeams and tblPlayers.

For data-entry, NEVER use tables or queries directly! Instead create forms and control all data through them. By using form-subform combinations, you can enter just what's necessary and let Access fill in the linked values.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html