Results 1 to 12 of 12
  1. #1
    neonslip is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    6

    Nested Select Statements in FROM clause

    I'm trying to have two select statements within the FROM clause, but no matter how I edit it, it keeps giving me errors:

    Code:
    SELECT ID, PlayerAverageHeight, CompAverageHeight
    FROM (SELECT ID, AVG(Height) AS PlayerAverageHeight FROM MasterDB WHERE Team="Medium" GROUP BY ID, (SELECT ID, AVG(Height) AS CompAverageHeight FROM MasterDB WHERE Team<>"Medium" GROUP BY ID))
    GROUP BY ID, PlayerAverageHeight, CompAverageHeight
    Any ideas?

    Thanks!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what data are you trying to query out?

  3. #3
    neonslip is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    6
    Each player is given an ID.. basically the position that that person plays.

    I'm then trying to query out the average heights for Medium teams for everyone in that position (ID) and the average heights for Non-Medium teams for everyone in those positions. My two select statements work independently but I can't get them to work in one query in which it has 3 columns: ID (position), Medium Height, and Non-Medium Height for those positions.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by neonslip View Post
    Each player is given an ID.. basically the position that that person plays.

    I'm then trying to query out the average heights for Medium teams for everyone in that position (ID) and the average heights for Non-Medium teams for everyone in those positions. My two select statements work independently but I can't get them to work in one query in which it has 3 columns: ID (position), Medium Height, and Non-Medium Height for those positions.

    you're trying to do it in one step, which is probably impossible with what your requirements are. the other thing is, the two WHERE clauses you have are redundant if they're coming from the same table. you can eliminate one because you're not giving any evidence of why the two need to be separated. your current outcome, if possible would give the results for MEDIUM and the other in one dataset anyway.

    try:

    Code:
    SELECT ID, avg([height]) AS PlayerAvgHeight, team
    
    FROM masterdb
    
    GROUP BY ID, team

  5. #5
    neonslip is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    6
    Well one "Where" clause = "Medium" and the other one <> "Medium." Does that not work?

    The code you gave won't work because I need it in this format:

    Position A | 5'9" (For Medium Teams) | 5'11" (For Non-Medium Teams)
    Postion B | 5'4" (For Medium Teams) | 5'3" (For Non-Medium Teams)

    etc. Column B is exclusive to only medium teams. And Column C is all non-medium teams. I need them side by side for comparison purposes. What I don't want is:

    Position A | 5'9" | Medium
    Position A | 5'11" | Non-Medium
    Position B | 5'4" | Medium
    Position B | 5'3" | Non-Medium

    Does that make sense?

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    yes it makes sense, but you're barking up the wrong tree. Access can't produce results like this easily, and that's not what it's for anyway.

    If you're truly set on getting results in that format, you'll have to put forth a lot of work, most of it by writing visual basic code.

    it'll be easier to just do it in excel. The golden rule to follow in Access is to manipulate data 'vertically', not 'horizontally'. Your desired output is horizontal manipulation.

  7. #7
    neonslip is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    6
    Hmm. Well I know it's possible without VB code as I've seen it done for a similar datasheet. I'm okay with having to do more complicated queries, but I just don't understand how this query should be written for something that intuitively seems really simple to me.

    Any one else have ideas?

  8. #8
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You could dump the data to a temp table with the "medium"/"non-medium" data in one record, then use a query to get the data in the format you want (I don't like doing it that way, but sometimes you have to)

    Use a Union query??

    Use a crosstab query??

    I modified your query ...I don't know if it will work since I don't know your field names and I don't know what your data looks like. You could try:

    Code:
    SELECT ID, iif(Team="Medium",PlayerAverageHeight,"") as MedHt, iif(Team="Medium","",CompAverageHeight) as NonMedHt
    FROM MasterDB
    If it gives the correct records, then add in the grouping....

    Code:
    GROUP BY ID, MedHt, NonMedHt

  9. #9
    neonslip is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    6
    So I tried:

    Code:
     
    SELECT ID, iif(Team="Medium", AVG(Height), "") AS MediumHeight, iif(Team<>"Medium", AVG(Height),"") AS OtherHeight
    FROM MasterDB
    GROUP BY ID, MediumHeight, OtherHeight
    I get the error:

    You tried to execute an expression that does not include iif(Team...) as part of an aggregate function.

    This means I need to use Group By right? But I do have the Group By clause in the end.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Try changing the Total row for the two IIF() columns from "Group By" to "Expression".

  11. #11
    neonslip is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    6
    Is this what you mean?

    Code:
    SELECT ID, iif(Team="Medium", AVG([Height]), "") AS MediumHeight, iif(Team<>"Medium", AVG([Height]),"") AS OtherHeight
    FROM MasterDB
    GROUP BY ID
    Also tried it with GROUP BY with MediumHeight and OtherHieght to no avail. Seems like the issue is the AVG function. If I just do [Height]*1.05 or something it works fine, but I can't get it to average all the Medium heights and slot it into its respective ID.

    // EDIT. I'm also trying to use Union which seems to work. But it's simply appending the OtherHeight column below the MediumHeight column. Is it possible to put them side-by-side rather than one below the other?

  12. #12
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe instead of using a table, create a query that does the AVG, then use it as the record source of the query instead of the table MasterDB.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use of COLLATE statement in SELECT clause
    By zurek in forum Queries
    Replies: 7
    Last Post: 03-16-2011, 06:46 AM
  2. IIF statements?
    By staceyo in forum Queries
    Replies: 15
    Last Post: 09-28-2010, 08:45 AM
  3. Nested IIF statements?
    By laavista in forum Access
    Replies: 9
    Last Post: 06-22-2010, 10:35 AM
  4. SQL statements from VBA
    By John Southern in forum Programming
    Replies: 12
    Last Post: 05-16-2010, 01:07 PM
  5. IIF Statements
    By JDA2005 in forum Queries
    Replies: 8
    Last Post: 07-07-2009, 04:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums