Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subquery as Column Expression (2000)

    I'm trying to replicate the behaviour of Query1x in the attached mdb:

    SELECT Max(Bowler_Scores.RawScore) AS MaxOfRawScore, Bowlers.BowlerFirstName, Bowlers.BowlerLastName, <font color=448800>Int(Avg(Bowler_Scores.RawScore))</font color=448800> AS Means
    FROM Bowlers INNER JOIN Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID
    GROUP BY Bowlers.BowlerFirstName, Bowlers.BowlerLastName
    HAVING (((Max(Bowler_Scores.RawScore))>=(SELECT int(Avg(Bowler_Scores.RawScore))
    FROM Bowler_Scores)+20));

    by substituting the green part with(see Query2):
    (SELECT int(Avg(Bowler_Scores.RawScore)) FROM Bowler_Scores)

    but I get a constant value down the Means column in the output.
    What am I doing wrong?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Subquery as Column Expression (2000)

    I think in this case the Avg function needs to be in a group-by query, so doing it as a simple subquery is almost certain to return the average of all scores each time. Try making the subquery a GroupBy as well and see if that works. (I've never done it, but it seems as though it should work.)
    Wendell

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subquery as Column Expression (2000)

    Thank you very much Wendell, your suggestion brought me on the right track to the solution:
    SELECT Max(Bowler_Scores.RawScore) AS MaxOfRawScore, Bowlers.BowlerFirstName, Bowlers.BowlerLastName, (SELECT int(Avg(Bowler_Scores.RawScore) )
    FROM Bowler_Scores WHERE Bowler_Scores.BowlerID = Bowlers.BowlerID) AS Means
    FROM Bowlers INNER JOIN Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID
    GROUP BY Bowlers.BowlerFirstName, Bowlers.BowlerLastName, Bowlers.BowlerID
    HAVING (((Max(Bowler_Scores.RawScore))>=(SELECT int(Avg(Bowler_Scores.RawScore))
    FROM Bowler_Scores WHERE Bowler_Scores.BowlerID = Bowlers.BowlerID)+20));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •