I am not even sure how to phrase this question. I am working on this sports
team database. I have come across a situation where I need to have one
record in one table with two teams listed. It is simple getting the record
populated with the FK to the t_TeamName. I just used a combo box to select
the team name and input the PK of that Team Name in to the field.
My Table looks like this.
t_TeamTournament
touTouID PK
touSeaID FK to t_Season
touSpoID FK to t_Sport
touGraID FK to t_Grade
touRank1Team FK to t_TeamName
touRank2Team FK to t_TeamName
The problem comes when I want to create a query for a report. I no longer
want to see the PK of the TeamName, I want to see the actual team name. In
My report query I added t_TeamName to my design and associated it in my
query. And here is my problem. It is associated to two fields in my query
to the t_TeamTournament, touRank1Team and touRank2team.
The Team Name Table looks like this.
t_TeamName
tnaTnaID PK auto number
tnaSpoID FK to t_Sport
tnaSeaID FK to t_Season
tnaGraID FK to t_Grade
tnaName This is where the team Name is listed
I cannot get the actual name to show in the query for both the FKs,
touRank1Team and touRank2team. The name only show up for the touRank1Team
or I get no records depending on the relationships I put in.
Is it possible to pull two team names out of one record?
Here is the SQL that produces no records because of the last statement where
one tnaTnaID (TeamNameID) is associated with two field in the query.
SELECT t_Grade.graName, t_Season.seaName, t_Sport.spoName,
t_TeamName.tnaName, t_TeamTournament.touRank1Team,
t_TeamTournament.touRank2Team, t_TeamName.tnaName
FROM t_TeamName INNER JOIN (t_Sport INNER JOIN (t_Season INNER JOIN (t_Grade
INNER JOIN t_TeamTournament ON t_Grade.graGraID = t_TeamTournament.touGraID)
ON t_Season.seaSeaID = t_TeamTournament.touSeaID) ON t_Sport.spoSpoID =
t_TeamTournament.touSpoID) ON (t_TeamName.tnaTnaID =
t_TeamTournament.touRank2Team) AND (t_TeamName.tnaTnaID =
t_TeamTournament.touRank1Team);
I suppose I could put together a few sub queries that pull the names and
then have this query pull from those sub queries. If that is the correct
way to do it I will.
Any help would be appreciated.
Bill Singer
[Non-text portions of this message have been removed]
Jumat, 20 April 2012
RE: [MS_AccessPros] Duplicate FK in one record
__._,_.___
.
__,_._,___
Langganan:
Posting Komentar (Atom)
Tidak ada komentar:
Posting Komentar