Need Query to Search TWO columns - Can it be done?

[Vance]

Hello. I have a query (based on a table and two other queries):

tblGAMES:
fldGameID (PK)

qryVISITOR_TEAM:
fldTeamName
fldGameID

qryHOME_TEAM:
fldTeamName
fldGameID

My query is set up so that it displays the GameID, the "Home" Team and
the "Visitor" Team:

fldGameID fldTeamName fldGameID
(from qryVISITOR_TEAM) (from qryHOME_TEAM) (from tblGAMES)

I would like to be able to set a parameter to type in the team name
and get a list of all the games, regardless whether the team is "Home"
or "Visitor". I can't figure out how to make it search through BOTH
the Away and the Home team fields, however -- I either get a list of
the games where the team is considered "Home" or a list where they are
the "Visitor" team.

Is this possible?

Thanks!

Vance

 

[Van T. Dinh]

Have you tried a Query with the following SQL String:

SELECT H.fldGameID, H.fldTeamName As HomeTeam,
V.fldTeamName As VisitorTeam
FROM qryHOME_TEAM As H
INNER JOIN qryVISITOR_TEAM As V
ON H.fldGameID = V.fldGameID
WHERE ( H.fldTeamName = [Enter Team Name:] )
OR ( V.fldTeamName = [Enter Team Name:] )

You must have an entry for each GameID in each of the 2 Source Queries for
the above Query to work correctly.

 

[Michel Walsh]

Hi,

SELECT games.gameID, VisitorTeam.TeamName, HomeTeam.TeamName

FROM ( games INNER JOIN VisitorTeam ON games.gameID=VisitorTeam.GameID)
INNER JOIN HomeTeam ON games.gameID=HomeTeam.gameID

WHERE parameter IN ( VisitorTeam.teamName, HomeTeam.teamName)



Note that in theory, the design should be:


Table Games
GameID, HomeTeamID, VisitorTeamID ' fields name
1 2 5 ' data
such like team id 2 and 5 play together



Table Teams
TeamID, TeamName ' fields name
2 "daffy"
5 "bug bunny" ' data



since matching two teams is the property of the game, not of one team, and,
furthermore, a team is a team, home or visitor. With your actual design, how
to do enter a second game for a given team? With the proposed design, just
add a new line in table games.


With that proposed design, the query is now:


SELECT g.gameID, v.TeamName As VisitorTeamName, h.TeamName As HomeTeamName

FROM ( games AS g INNER JOIN Teams As v ON g.VisitorTeamID=v.TeamID)
INNER JOIN Teams As h ON g.HomeTeamID=h.TeamID

WHERE parameter IN ( g.HomeTeamID, g.VisitorTeamID)





Hoping it may help,
Vanderghast, Access MVP

 

[Vance]

Thanks for everyone's help.

I did originally design tblGames to include the fields for fldTeamHome
and fldTeamAway. I had direct links from tblTeams to these two
fields, and I did not try to enforce referential integrity.

tblGAMES
fldGameID
tblTEAMS: ----- fldTeamHomeID
fldTeamID ----| fldTeamHome_Score
----- fldTeamAwayID
fldTeamAway_Score

I must have done something wrong, however, because my query would not
work -- it returned no records! I worked and worked, but finally
gave up and decided to do it the other way using the separate home and
away tables. I haven't had any problems adding games to it... My main
form is based on tblGames, and I added two subforms, one for
tblGames_TeamsAway and one for tblGames_TeamsHome. I just select each
team through a dropdown box. If it is a flawed design, however, I
may still have a problem yet unforeseen!

Thanks for everyone's help on doing the parameter query. I really
appreciate it.

Vance