help on a cross tab query

[Michael Beatty]

I have a access db that is for keeping stats for a high school golf team. I
have a query importing data from multiple tables, I have a cross tab query
set up as follows

Players Team 1 Team 2 Team 3
Jon Doe 31 23 34

fldPlayers is the Row Heading
fldOpponent is the column heading
fldTotalScore is the value


what I would like to do is:
1. Add a column with the players best at home score
2. Add a column with the players overall average.


to look like this

Player Team 1 Team 2 Team 3 Home Average
Jon Doe 32 34 32 34 33

I can't seem to manually had them becuase I can only have one column field.
Any help would be greatly appreciated

 

[Duane Hookom]

How do we know which scores are from home or away? Average is just a matter
of adding the Score field to the crosstab, setting it as Row Heading and set
the Total to Average.

 

[Michael Beatty]

To determine if the score is home or away, there is a tblRoundInformation
table that has a fldHomeTeam. If fldHomeTeam = "Home Team" is how to
determine if it is a home match. The opponets are taken from an opponent
table. The home team is not.

 

[Duane Hookom]

You haven't provided near enough information about how your scores are
stored and how all your tables are related. --
Duane Hookom
MS Access MVP

 

[Michael Beatty]

tblPlayer contains players names
tblTeamStats contains round information including fldRoundNumber (key field)
tblTeamStats contains a fldHomeTeam that holds the home team name
tblOpponets contains opponents with the Round Number related to the
tblTeamStats.fldRoundNumber
tblScores containts key field from TeamStats, and Player tables and holds
the hole by hole score and a total

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.

 

[Duane Hookom]

Could you provide the SQL of your crosstab?

--
Duane Hookom
MS Access MVP

tblPlayer contains players names
tblTeamStats contains round information including fldRoundNumber (key field)
tblTeamStats contains a fldHomeTeam that holds the home team name
tblOpponets contains opponents with the Round Number related to the
tblTeamStats.fldRoundNumber
tblScores containts key field from TeamStats, and Player tables and holds
the hole by hole score and a total

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.

You haven't provided near enough information about how your scores are
stored and how all your tables are related. --
Duane Hookom
MS Access MVP

 

[Michael Beatty]

TRANSFORM
Sum([fldHole1]+[fldHole2]+[fldHole3]+[fldHole4]+[fldHole5]+[fldHole6]+[fldHo
le7]+[fldHole8]+[fldHole9]+[fldHole10]+[fldHole11]+[fldHole12]+[fldHole13]+[
fldHole14]+[fldHole15]+[fldHole16]+[fldHole17]+[fldHole18]) AS [Total Score]
SELECT tblPlayer.fldFirstName, tblPlayer.fldLastName
FROM tblTeamStats INNER JOIN (tblPlayer INNER JOIN (tblOpponentStats INNER
JOIN tblScores ON tblOpponentStats.fldRoundNumber =
tblScores.fldRoundNumber) ON tblPlayer.fldPlayerNumber =
tblScores.fldPlayerNumber) ON (tblTeamStats.fldRoundNumber =
tblScores.fldRoundNumber) AND (tblTeamStats.fldRoundNumber =
tblOpponentStats.fldRoundNumber)
GROUP BY tblPlayer.fldFirstName, tblPlayer.fldLastName
PIVOT tblOpponentStats.fldOpponent In ("Huron","Oak Harbor","Clyde","St
Mary's","Perkins","Port Clinton");

Could you provide the SQL of your crosstab?

--
Duane Hookom
MS Access MVP

tblPlayer contains players names
tblTeamStats contains round information including fldRoundNumber (key field)
tblTeamStats contains a fldHomeTeam that holds the home team name
tblOpponets contains opponents with the Round Number related to the
tblTeamStats.fldRoundNumber
tblScores containts key field from TeamStats, and Player tables and holds
the hole by hole score and a total

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.

You haven't provided near enough information about how your scores are
stored and how all your tables are related. --
Duane Hookom
MS Access MVP


To determine if the score is home or away, there is a tblRoundInformation
table that has a fldHomeTeam. If fldHomeTeam = "Home Team" is how to
determine if it is a home match. The opponets are taken from an opponent
table. The home team is not.

How do we know which scores are from home or away? Average is just a
matter
of adding the Score field to the crosstab, setting it as Row

Heading
and

set
the Total to Average.

--
Duane Hookom
MS Access MVP


I have a access db that is for keeping stats for a high school golf
team.
I
have a query importing data from multiple tables, I have a cross tab
query
set up as follows

Players Team 1 Team 2 Team 3
Jon Doe 31 23 34

fldPlayers is the Row Heading
fldOpponent is the column heading
fldTotalScore is the value


what I would like to do is:
1. Add a column with the players best at home score
2. Add a column with the players overall average.


to look like this

Player Team 1 Team 2 Team 3 Home Average
Jon Doe 32 34 32 34
33

I can't seem to manually had them becuase I can only have one column
field.
Any help would be greatly appreciated

 

[Duane Hookom]

I give up. Each response/reply opens a whole new list of fields and tables.
I have no idea how much further this thread will go as I hope someone else
has the time to peal back the layers with you. When a table ignores basic
normalization rules, I have a tendency to back away.

--
Duane Hookom
MS Access MVP

TRANSFORM
Sum([fldHole1]+[fldHole2]+[fldHole3]+[fldHole4]+[fldHole5]+[fldHole6]+[fldHole7]+[fldHole8]+[fldHole9]+[fldHole10]+[fldHole11]+[fldHole12]+[fldHole13]+[
fldHole14]+[fldHole15]+[fldHole16]+[fldHole17]+[fldHole18]) AS [Total Score]
SELECT tblPlayer.fldFirstName, tblPlayer.fldLastName
FROM tblTeamStats INNER JOIN (tblPlayer INNER JOIN (tblOpponentStats INNER
JOIN tblScores ON tblOpponentStats.fldRoundNumber =
tblScores.fldRoundNumber) ON tblPlayer.fldPlayerNumber =
tblScores.fldPlayerNumber) ON (tblTeamStats.fldRoundNumber =
tblScores.fldRoundNumber) AND (tblTeamStats.fldRoundNumber =
tblOpponentStats.fldRoundNumber)
GROUP BY tblPlayer.fldFirstName, tblPlayer.fldLastName
PIVOT tblOpponentStats.fldOpponent In ("Huron","Oak Harbor","Clyde","St
Mary's","Perkins","Port Clinton");

Could you provide the SQL of your crosstab?

--
Duane Hookom
MS Access MVP

tblPlayer contains players names
tblTeamStats contains round information including fldRoundNumber (key field)
tblTeamStats contains a fldHomeTeam that holds the home team name
tblOpponets contains opponents with the Round Number related to the
tblTeamStats.fldRoundNumber
tblScores containts key field from TeamStats, and Player tables and holds
the hole by hole score and a total

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.


You haven't provided near enough information about how your scores are
stored and how all your tables are related. --
Duane Hookom
MS Access MVP


To determine if the score is home or away, there is a
tblRoundInformation
table that has a fldHomeTeam. If fldHomeTeam = "Home Team" is how to
determine if it is a home match. The opponets are taken from an
opponent
table. The home team is not.

How do we know which scores are from home or away? Average is

just

a cross
tab