Results 1 to 11 of 11
  1. #1
    scotharr is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6

    Creating a SELECT Sub-Query

    Hi All, (first post)

    I receive the following error: "Syntax error in query expression (SELECT (Scores) FROM (String Results) WHERE (Stage) = 1. Help?



    I'm new to Access and trying to create a SELECT Sub-query. I have a Table that has scores for competitors who completed different stages of the competition. The table has all the scores for all the stages, but I want to create a field/column to select just one particular stage score. I want the query to look something like this:

    [Competitor ID] [Stage1Score] [Stage2Score] [Stage3Score]

    I have tried to create a SELECT sub-query in the Field with the following syntax
    Stage1Score: (SELECT [String Results].[Score]
    WHERE [String Results].[Stage] = 1)

    I get the syntax error shown above and am stuck. Any help is appreciated.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Code:
    (SELECT [String Results].[Score] FROM [String Results]
    WHERE [String Results].[Stage] = 1)

  3. #3
    scotharr is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    Thanks, but I get the same syntax error.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Post the entire attempted SQL statement. Something like:

    SELECT *, (SELECT [Score] FROM [String Results] WHERE [Stage] = 1) AS Stage1Score FROM [String Results];

    This syntax works in my test where only 1 record returns in the subquery.

    But doesn't every competitor have a stage1, stage2, stage3 score? That subquery will then return multiple records and that won't work so it errors.

    Maybe you should do a CROSSTAB query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    What is [String Results]? Is that a table or query?
    You say subquery. What's the main query? Please show the entire query including the subquery.
    I want the query to look something like this:

    [Competitor ID] [Stage1Score] [Stage2Score] [Stage3Score]
    Are all those fields in [String Results]?
    You are showing that you want 4 fields to show, but the query only selects 1.
    Perhaps you don't need a subquery, but just a WHERE clause in the main query.

    Select [Competitor ID], [Stage1Score], [Stage2Score], [Stage3Score] From [String Results] Where Stage = 1

  6. #6
    scotharr is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    Sorry for the confusion. The table includes results for all competitors and all stages (1 thru 4) I want a single column in the query to display the result from just one stage....as shown below

    [CompetitorID] [Stage1Score]

    Within "Field" for the column stage1score, I tried to create a SELECT query to pull that score.
    tage1Score: (SELECT [String Results].[Score]
    WHERE [String Results].[Stage] = 1)

    [Stage] & [Score] are fields in the Table [String Results]

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you read post 4?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    scotharr is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    Quote Originally Posted by davegri View Post
    What is [String Results]? Is that a table or query?
    You say subquery. What's the main query? Please show the entire query including the subquery.


    Are all those fields in [String Results]?
    You are showing that you want 4 fields to show, but the query only selects 1.
    Perhaps you don't need a subquery, but just a WHERE clause in the main query.

    Select [Competitor ID], [Stage1Score], [Stage2Score], [Stage3Score] From [String Results] Where Stage = 1
    Here is a screen shot of what I am trying to do...put a SELECT query into one of the columns, so it displays just the stage 1 score. My table includes scores from all the stages....417 records for just 74 competitors. I am trying to list the competitors only once in the query and have all their scores from the different stages print as different columns in the query.

    Click image for larger version. 

Name:	access1.png 
Views:	7 
Size:	105.5 KB 
ID:	36475

    and I get the following error:

    Click image for larger version. 

Name:	access2.png 
Views:	7 
Size:	102.3 KB 
ID:	36476

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As noted in post 4, if subquery returns multiple records, it won't work, only errors: "At most one record can be returned by this subquery"

    Consider a CROSSTAB query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    scotharr is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    I'm not sure that is the problem since I am getting a syntax error....query never runs. I will look into a cross tab query

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I just noticed you are trying a GROUP BY query so I tested that and got the syntax error. Do a normal query and you will likely get the other error message.

    I think a CROSSTAB using Max() aggregate function is appropriate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-24-2017, 09:31 AM
  2. Replies: 1
    Last Post: 01-14-2016, 10:19 PM
  3. Replies: 5
    Last Post: 05-14-2014, 01:17 PM
  4. Replies: 6
    Last Post: 02-19-2013, 03:38 PM
  5. Replies: 4
    Last Post: 09-17-2012, 10:36 AM

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