Results 1 to 8 of 8

Union Query to obtain a Freq Array Giving Syntax Error

  1. #1
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    42

    Union Query to obtain a Freq Array Giving Syntax Error

    I have created 2 Union Queries to provide a Freq Array Table. This was successeful. The Query was based on a table

    I created a 3rd Union Query but this time based on a QUERY, I am getting a Syntax error in query expression Count(20_XYZ.QOHV), where 20_XYZ is the query name and QOHV is the calculated field in the query

    Any ideas why the error?. The SQL is given below

    Rgds & thanks once again
    Dave

    SELECT Count(20_XYZ.QOHV) AS CountOfQOHV, "0" AS rng
    FROM 20_XYZ
    WHERE (((20_XYZ.QOHV)="0"));

    UNION SELECT Count(20_XYZ.QOHV) AS CountOfQOHV, "0 to 100" AS Rng
    FROM 20_XYZ
    WHERE (((20_XYZ.QOHV)>0.01 And (20_XYZ.QOHV)<100));

    UNION SELECT Count(20_XYZ.QOHV) AS CountOfQOHV, "100 to 1000" AS Rng
    FROM 20_XYZ
    WHERE (((20_XYZ.QOHV)>100.01 And (20_XYZ.QOHV)<1000));

    UNION SELECT Count(20_XYZ.QOHV) AS CountOfQOHV, "1000 to 5000" AS Rng
    FROM 20_XYZ
    WHERE (((20_XYZ.QOHV)>1000.01 And (20_XYZ.QOHV)<5000));

    UNION SELECT Count(20_XYZ.QOHV) AS CountOfQOHV, "5000 to 25000" AS Rng
    FROM 20_XYZ
    WHERE (((20_XYZ.QOHV)>5000.01 And (20_XYZ.QOHV)<25000));

    UNION SELECT Count(20_XYZ.QOHV) AS CountOfQOHV, "25000 to 50000" AS Rng
    FROM 20_XYZ
    WHERE (((20_XYZ.QOHV)>25000.01 And (20_XYZ.QOHV)<50000));



    UNION SELECT Count(20_XYZ.QOHV) AS CountOfQOHV, "50000 to 160000" AS Rng
    FROM 20_XYZ
    WHERE (((20_XYZ.QOHV)>50000.01 And (20_XYZ.QOHV)<160000));

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    Maybe don't use quotes around 0 in WHERE clause.
    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.

  3. #3
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    42
    Many thanks tried that but same then I removed the 20_ in front of XYZ and it worked, didnt seem to like the 20_XYZ
    Thanks for that
    Dave

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,514
    where 20_XYZ is the query name
    I cannot imagine why removing part of an object name would work, but who can argue with success??
    My advice would be to NEVER start a name with a number, but if you must, always enclose it in brackets [20_XYZ].
    There might be a lot of other things you would do regarding names if you'd start with a number. You might benefit from research on how to name things in a database.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  5. #5
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    42
    Thanks for the advice, I did this to sort as have many queries, as its was analysing spares data, and check naming for quality. Maybe should have used letters to achieve the same effect or start with qry
    Dave

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    You mean you actually renamed the query object, not just removed the 20_ prefix in the query. I just did a test and the following query works: SELECT [202].ID, [202].test FROM 202;

    So in your query:

    SELECT Count([20_XYZ].QOHV) AS CountOfQOHV, "0" AS rng
    FROM 20_XYZ
    WHERE ((([20_XYZ].QOHV)="0"));

    or simplify

    SELECT Count(QOHV) AS CountOfQOHV, "0" AS rng FROM 20_XYZ WHERE QOHV=0
    UNION SELECT Count(QOHV), "0 to 100" FROM 20_XYZ WHERE QOHV>0.01 And QOHV<100;
    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.

  7. #7
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    42
    No I didnt rename, I was just saying maybe I COULD

    Thanks for the code, you are brilliant
    Dave

  8. #8
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    42
    Wonderful, worked a treat
    Many many thanks again
    There should be a system where you could donate some money
    Dave

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

Similar Threads

  1. Replies: 4
    Last Post: 08-09-2017, 11:06 AM
  2. Failing to grasp the syntax of a Union Query
    By brharrii in forum Queries
    Replies: 6
    Last Post: 05-09-2013, 10:51 AM
  3. DCount on a Query giving #Name? error
    By Huddle in forum Access
    Replies: 9
    Last Post: 06-20-2012, 10:40 AM
  4. Replies: 6
    Last Post: 05-30-2012, 11:32 AM
  5. Union Query Error
    By Earthmover in forum Queries
    Replies: 6
    Last Post: 03-05-2012, 09:44 PM

Tags for this Thread

Posting Permissions

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