I would like to use a table without a primary key as a rowsource? 1

[daglugub37]

i do not know how to accomplish this.

the table I am trying to build will track injuries to football players on a NFL Roster.

I have a tblRoster
fldTeam ID NUMBER uses a tblTeams PK
fldPlayer ID NUMBER uses a tblPlayers PK
fldSeason NUMBER
fldStarter YES/NO
fldRookie YES/NO
fldVeteran YES/NO
fldRating NUMBER

The combination of fields ensures that each record will be unique so I did not create an AutoNumber PK field. Was that a mistake?

So now I would like to create a tblInjuries with:
fldRosterPlayer
fldInjury
fldLength

I would like the data entry for the fldRosterPlayer be able to choose from a value list that would look like so

Joe Smith Pittsburgh Steelers 2003
John Black Miami Dolphins 2004

and so on,

 

[Transcend]

It is best to create the autonumber primary key field especially as now you want to list the rows in that table and be able to reference them with something easy like a numeric id

Transcend

 

[daglugub37]

OK I have made a PK autonumber field for tblRosters. Now here is my prob. This is the rowsource that I am trying to use for tblInjuries.fldRosterPlayer

SELECT tblRoster.fldRosterID, tblRoster.fldPlayerID, tblRoster.fldSeason, tblRoster.fldTeamID
FROM tblRoster;

Now the query outputs exactly as I want ie.

1 | Joe Smith | 2003 | Chicago Bears.

so i take this statement and rowsource it with the following properties

boundcolumn = 1
columncount = 4
columnwidths = 0,5,5,5

So what I expect is to bound the numeric unique value to the value of the tblInjuries.fldRosterPlayer(NUMBER TYPE) and then text values like such should show up in my combo list text values to pick from.

But instead I get the number PK values for both fldPlayerID and fldTeamID.

why does the same SQL statement output 2 different pick lists? and how do i avoid that?

 

[Transcend]

I don't understand how this query

SELECT tblRoster.fldRosterID, tblRoster.fldPlayerID, tblRoster.fldSeason, tblRoster.fldTeamID
FROM tblRoster;

Can produce these results?

1 | Joe Smith | 2003 | Chicago Bears.

Unless your teamID and playerid fields are text?

Transcend

 

[Transcend]

As opposed to id numbers ...

Transcend

 

[daglugub37]

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

So YES they are technically Number fields and I agree i do not understand how I got text results from the query, but i am; when i cut and paste the same statement into my row source then it outputs all the number ID's

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.