average problem

[MrBLT]

Hi everyone
i've been stumped on this query for a while now. The query reads as this

SELECT Teams.Team, Avg([Game Percentages].[FG%]) AS [AvgOfFG%], Avg([Game Percentages].[3PT%]) AS [AvgOf3PT%], Avg([Game Percentages].[FT%]) AS [AvgOfFT%], Avg([Game Percentages].OffReb) AS AvgOfOffReb, Avg([Game Percentages].DefReb) AS AvgOfDefReb, Avg([Game Percentages].Tot) AS AvgOfTot
FROM Teams INNER JOIN (Game INNER JOIN [Game Percentages] ON Game.GameID = [Game Percentages].GameID) ON Teams.TeamID = Game.Home
GROUP BY Teams.Team, Game.Away;

When i put it in datasheet view it gives several rows of the same team with different averages. My tables include

GAME
gameid(pk)
hometeam
awayteam
date
venueid
homeline
awayline

SCORE
gameid(pk)
periodid
score

STATS
gameid(pk)
teamid
fgm
fga
ftm
fta
3ptm
3pta
offreb
defreb
assist
steal
t/o
block
fouls

TEAM
teamid(pk)
team

VENUE
venueid(pk)
venue

PERIOD
periodid(pk)
period

i already have game joined to team twice. As i'm not too confident with sql, i'm using design view and have been able to make the home team averages query successfully and would not think it would be possible to join an home and away averages query together. If joining home and away to team is all that is required, what am i doing wrong then?

This is what the outcome should be. This is the outcome of my home averages query.

Team AvgOfFG% AvgOf3PT% AvgOfFT% AvgOfOffReb AvgOfDefReb AvgOfTot
Victoria Giants 46.86 36.30 74.43 13.45 26.25 39.70
Townsville Crocodiles 46.5652435544365 35.22 78.6254488212333 14.85 27.15 42
Perth Wildcats 46.10 34.99 74.97 13.61 27.8333333333333 41.4444444444444
Adelaide 36ers 45.5828370343191 32.5995574090462 70.9236986651785 15.85 28.35 44.2
Hunter Pirates 45.0838275841689 33.5230332916944 74.7295779044246 13.1 29.35 42.45
------------------------------------
This is how it is currently turning out

Team AvgOfFG% AvgOf3PT% AvgOfFT% AvgOfOffReb AvgOfDefReb AvgOfTot
Adelaide 36ers 39.2647058823529 32.4587706146927 74.0740740740741 16 30.5 46.5
Adelaide 36ers 43.1006493506493 31.6770186335404 65.5228758169935 18.5 28 46.5
Adelaide 36ers 46.7226890756303 35.3302611367128 75 19.5 23.5 43
Adelaide 36ers 52.4390243902439 37.280701754386 73.7616099071207 10.5 26 36.5
-----------------------------------
Game Percentages is a query i made. The sql for this is

SELECT Stats.GameID, Teams.Team, Stats.FGM, Stats.FGA, Stats!FGM/Stats!FGA*100 AS [FG%], Stats.[3PTM], Stats.[3PTA], Stats![3PTM]/Stats![3PTA]*100 AS [3PT%], Stats.FTM, Stats.FTA, Stats!FTM/Stats!FTA*100 AS [FT%], Stats.OffReb, Stats.DefReb, Stats!OffReb+Stats!DefReb AS Tot
FROM Teams INNER JOIN Stats ON Teams.TeamID = Stats.TeamID
ORDER BY Stats.GameID;
-------------------------------
Some lines from it

GameID Team FGM FGA FG% 3PTM 3PTA 3PT% FTM FTA FT% OffReb DefReb Tot
1 New Zealand Breakers 32 76 42.1052631578947 14 34 41.1764705882353 32 41 78.0487804878049 14 24 38
1 Adelaide 36ers 38 82 46.3414634146341 8 18 44.4444444444444 27 34 79.4117647058823 16 31 47
2 Townsville Crocodiles 46 96 47.9166666666667 7 22 31.8181818181818 15 21 71.4285714285714 24 20 44
2 Cairns Taipans 44 77 57.1428571428571 7 26 26.9230769230769 23 27 85.1851851851852 13 23 36
3 Hunter Pirates 32 83 38.5542168674699 4 19 21.0526315789474 9 14 64.2857142857143 14 32 46
3 Melbourne Tigers 36 84 42.8571428571429 3 14 21.4285714285714 30 36 83.3333333333333 14 36 50

thanks for your help

 

[Pat Hartman]

You have not selected Game.Away but you are grouping by it. Either get rid of it in the group by clause or add it to the select clause.