crosstab query

[Michael Beatty]

I need to set up a query that tells a players name their score against all
their opponents and their best at home score. I have a crosstab query that
displays the players and their opponent scores, but I can't figure out how
to add in thier best home score. Right now the query is set up as follows

tblPlayer.fldPlayerName as row heading
tblTeamStats.fldRoundDate as row heading
tblOpponent.fldOpponent as row column
tblScores.fldScores as value

The output for this query is
Name Date Team 1 Team2
Team2
Jim Bob 1-1-01 23 24
24

which is what I want, but I don't know how to add in the best score at home
which is stored in a different table than the oppoents scores

here is a general run down of the relavent tables and fields

tblPlayer
fldPlayerNumber Name
1 Joe Schome
2 Jim Beam

tblTeamStats
fldRoundNumber fldHomeTeam
1 Home Team
2 Away Team

tblOpponents
fldRoundNumber fldOpponent fldOpponentScore
1 ATeam 100
1 BTeam 200
2 ATeam 101
2 BTeam 202


tblScores
fldRoundNumber fldPlayerNumber [scores]
1 1 72
1 2 74
2 1 74
2 2 75


That is the rundown on the fields that are relavant to my problem All of
the fields that have the same name are related to each other.
What I have is a crosstabl query that uses the fldOppoent as the column and
th fldPlayerName as the row, and the [scores] as the value. Which works
fine for showing each players score against each team, however I need to add
in the lowest score where the fldHomeTeam = Home for each player. I hope
this helps clarify this for you. And I would like to thank you for all your
help so far.

I've had one other person try to help me with this, but I think I frustrated
him. (I do appreciate your help). If any help would be greatly appreciated

 

[Deville]

Try this: Use MAX to get the highest score for each player

tblPlayer.fldPlayerName as row heading
tblTeamStats.fldRoundDate as row heading
Max(tblScores.fldScores) as row heading
tblOpponent.fldOpponent as row column
tblScores.fldScores as value