Results 1 to 5 of 5
  1. #1
    Dave_D's Avatar
    Dave_D is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    67

    Subquery syntax

    The following subquery

    Select *


    From tbl_Combined_Assess3WQs
    Where C > 1
    (Select [WQ ID], Count([WQ ID]) as C
    From tbl_Combined_Assess3WQs
    Group By [WQ ID]);

    is issuing the following syntax error

    Invalid use of '.' , '!', or '()' in query expression 'C > 1
    (Select [WQ ID], Count([WQ ID]) as C
    From tbl_Combined_Assess3WQs
    Group By [WQ ID]'.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your sql does not make sense

    this bit does

    Select *
    From tbl_Combined_Assess3WQs
    Where C > 1

    providing your tbl_Combined_Assess3WQs has a field called 'C'

    Your subquery just seems to be stuck on the end

    perhaps you want


    Code:
    Select A.*
    FROM tbl_Combined_Assess3WQs A INNER JOIN 
        (Select [WQ ID], Count([WQ ID]) as C From tbl_Combined_Assess3WQs Group By [WQ ID]) B 
        ON A.WO_ID=B.WO_ID
    Where B.C > 1

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What are you trying to do in simple terms?
    Did you check the Similar Threads at bottom of the page?

    OOoops: I see Ajax has responded.

  4. #4
    Dave_D's Avatar
    Dave_D is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    67
    You understood enough of what I was needing and your solution worked perfectly. Thank you very much!!!

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    at least I was on the right track

    A better way to write this would be

    Code:
    SELECT A.*
    FROM tbl_Combined_Assess3WQs A INNER JOIN 
        (Select [WQ ID] FROM tbl_Combined_Assess3WQs GROUP BY [WO_ID] HAVING Count(*)  > 1) B
    ON A.WO_ID=B.WO_ID
    reason - you are only returning records where count>1, so a shorter list. Otherwise you are returning a longer list and then excluding those where the count=1 - which is more processing, more time.

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

Similar Threads

  1. A subquery inside a subquery
    By tbjmobile@gmail.com in forum Queries
    Replies: 15
    Last Post: 04-22-2018, 11:48 AM
  2. Guide for interpreting MSDN / MS documentation syntax-syntax !!
    By pisorsisaac@gmail.com in forum Access
    Replies: 4
    Last Post: 11-18-2017, 07:07 AM
  3. Convert SQL Server Syntax To Access Query Syntax
    By chalupabatman in forum Queries
    Replies: 1
    Last Post: 10-18-2017, 08:53 PM
  4. Subquery - Syntax Error In Expression
    By kagoodwin13 in forum Queries
    Replies: 4
    Last Post: 03-24-2016, 07:02 PM
  5. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 10:47 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