Simple query question in MS Access

[adamskiii]

I am using MS Access to create this database and making a query that will
show my score of the winning team and the name of the winning team. This is
what my tables currently look like in Access:

To create the winning score I want to use:
max(homeScore, awayScore)


And to show the winning team I want to use:
IIf(homeScore>awayScore, [homeTeam.name], [awayTeam.name])

Where do I put these expressions in the query? I tried to put them in the
field and Criteria but this is not working for some reason. I must be doing
something wrong. Please help.

Thanks

 

[John Spencer]

SELECT IIF(HomeScore>AwayScore,HomeTeam.[Name],AwayTeam.[Name]) As Winner
, IIF(HomeScore>AwayScore,HomeScore,AwayScore) as WinningScore

FROM (Game INNER JOIN HomeTeam
ON Game.HomeTeamID = HomeTeam.TeamId)
INNER JOIN AwayTeam
ON GAME.AwayTeamID = AwayTeam.TeamID

By the way there is no need for an awayteam table and a hometeamtable. One
table could be joined to the game table twice. TeamID to to HomeTeamID and
TeamID to awayTeamId.

You also have used the following reserved words as field names
Name, Date, and Time. Every object in Access has a name. Date is a function
to return the current date and Time is a function to return the current time.

You will probably be OK with this for now, but you could eventually run into
some problems. Better field names would be TeamName, GameDate, and GameTime.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dorian]

You put them in the SELECT clause.
Your first expression will not work, it also needs to be an IIF.
IIf(homeScore>awayScore, [homeScore], [awayScore])
Can there be ties?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[adamskiii]

Thanks for the reply. I was able to get the winning team and and score to
work. Just have one more problem. In one field I have the following to show
the winnerScore:

winnerScore:
IIf([homeScore]>[awayScore],[game].[homeScore],[game].[awayScore])


And the loserScore:


loserScore: IIf([awayScore]>[homeScore],[game].[homeScore],[game].[awayScore])

Now from these two expression I would like to create another field to show
the difference in the scores. This is what I am currently trying to use:

spread: ([winnerScore]-[loserScore])


But this is not working right. When i run the query it ask me for the
winnerScore and then the loserScore. If I leave them blank then the
difference is shown. How can I create an expression to use the values from
the other two expression I create called: winnerScore and loserScore?

Thanks

 

[John Spencer]

Try the following expression.
Spread: Abs(AwayScore-HomeScore)

AwayScore-HomeScore will give the negative of HomeScore-AwayScore. The two
values are equal except for the sign. So use the Abs (absolute value) to
remove the sign.

If you wanted to use your method you would have to use
IIf([homeScore]>[awayScore],[game].[homeScore],[game].[awayScore])
-IIf([awayScore]>[homeScore],[game].[homeScore],[game].[awayScore])

Access will frequently NOT allow you to use a calculated field elsewhere in
the query. You have to recalculate the value.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County