Combining queries (1 Viewer)

[BobNTN]

I hate to ask this because I know it is a simple solution that I just can't stumble on, but would someone mind telling me how to combine 3 queries into one giving three result columns instead of one ? All 3 are the same except the 'day'. Lists players and the average score each day Friday, Saturday and Sunday. Trying to make one that gives 3 columns Friday Saturday and Sunday.

SELECT TblPlayers.PlayerName, TblPoints.Day, Avg(TblPoints.PointsPulled) AS AvgOfPointsPulled
FROM TblPlayers LEFT JOIN TblPoints ON TblPlayers.PlayerID = TblPoints.PlayerID
GROUP BY TblPlayers.PlayerName, TblPoints.Day
HAVING (((TblPoints.Day)="friday"));

SELECT TblPlayers.PlayerName, TblPoints.Day, Avg(TblPoints.PointsPulled) AS AvgOfPointsPulled
FROM TblPlayers LEFT JOIN TblPoints ON TblPlayers.PlayerID = TblPoints.PlayerID
GROUP BY TblPlayers.PlayerName, TblPoints.Day
HAVING (((TblPoints.Day)="saturday"));

SELECT TblPlayers.PlayerName, TblPoints.Day, Avg(TblPoints.PointsPulled) AS AvgOfPointsPulled
FROM TblPlayers LEFT JOIN TblPoints ON TblPlayers.PlayerID = TblPoints.PlayerID
GROUP BY TblPlayers.PlayerName, TblPoints.Day
HAVING (((TblPoints.Day)="sunday"));

 

[plog]

First, why do you need 3 columns for those averages? Why couldn't you have a record for each day?

In either case, you need to combine the logic in your HAVING clauses together into a WHERE clause (what you have now, shouldn't be in the HAVING) by seperating each with the ' OR ' keyword. Right, now You have this:

...HAVING A...
...HAVING B...
...HAVING C...

Should become:

WHERE A OR B OR C

That will get you to one query that produces a new record for each day in the WHERE clause. If you insist on having a column for each day, you would then turn that query into a Cross-tab query. Google that term for help in doing that.

 

[BobNTN]

I am trying to display all 3 days in a report rather than having 3 different reports.

 

[Galaxiom]

You don't need three queries in the first place.

SELECT TblPlayers.PlayerName, TblPoints.Day, Avg(TblPoints.PointsPulled) AS AvgOfPointsPulled
FROM TblPlayers LEFT JOIN TblPoints ON TblPlayers.PlayerID = TblPoints.PlayerID
 WHERE TblPoints.Day IN("Friday", "Saturday", "Sunday")
GROUP BY TblPlayers.PlayerName, TblPoints.Day

Then build a Cross tab query based on this query. Follow the instructions in the Wizard.

BTW. Day is a function name and should not be used as a fieldname.