Results 1 to 6 of 6
  1. #1
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12

    Aggregate Query prompting for Parameters when I add criteria

    Hi and thanks for taking your time to help. In trying to learn a bit about brackets in SQL, and in doing so built a small query. It's an aggregate query including 2 Sum Columns and 1 Expression Column along with a few Grouped. Strange thing is that the query works fine (It runs without prompting for values) until I try and add a criteria (>0) to my expression column. When I do that I get prompted for the values that should be in the sum Columns. I even set it up in the query builder and added the criteria there, same problem.

    This is the SQL without criteria: This works

    SELECT tblEquity.EquityID, tblEquity.Ticker, tblEquity.Company, Sum(tblCCTransaction.CallQuantity) AS SumOfCallQuantity, Sum(tblCCTransaction.Quantity) AS SumOfQuantity, [SumOfQuantity]+([SumOfCallQuantity]*100) AS Availble
    FROM tblEquity LEFT JOIN tblCCTransaction ON tblEquity.EquityID = tblCCTransaction.EquityID
    GROUP BY tblEquity.EquityID, tblEquity.Ticker, tblEquity.Company
    ORDER BY tblEquity.Ticker;

    This is with Criteria and I get two prompts, one for SumofCallQuantity and one for SumofQuantity:

    SELECT tblEquity.EquityID, tblEquity.Ticker, tblEquity.Company, Sum(tblCCTransaction.CallQuantity) AS SumOfCallQuantity, Sum(tblCCTransaction.Quantity) AS SumOfQuantity, [SumOfQuantity]+([SumOfCallQuantity]*100) AS Availble
    FROM tblEquity LEFT JOIN tblCCTransaction ON tblEquity.EquityID = tblCCTransaction.EquityID
    GROUP BY tblEquity.EquityID, tblEquity.Ticker, tblEquity.Company
    HAVING ((([SumOfQuantity]+([SumOfCallQuantity]*100))>0))


    ORDER BY tblEquity.Ticker;

    I can't figure out why it would do that. When I run the one without any criteria everything runs properly, including the results. Should also mention that I will be using this SQL to set the RowSource of a Combo Box


    Here's a screen shot in the query builder of the no criteria:

    Click image for larger version. 

Name:	No 0.PNG 
Views:	20 
Size:	16.1 KB 
ID:	44222

    and Here it is with criteria added:


    Click image for larger version. 

Name:	With 0.PNG 
Views:	17 
Size:	15.5 KB 
ID:	44223




    Thanks again for any help

  2. #2
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12
    ..........

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you cannot reference the name of the calculated value since it isn't 'known' at that point the criteria is applied. Use the underlying calculation e.g.


    HAVING (((Sum(tblCCTransaction.Quantity)+(Sum(tblCCTransaction.CallQuantity)*100))>0))

  4. #4
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12
    Ajax -What a great thing to wake up to! I'm going have to ponder your explanation for a while, and still need to work on understanding the brackets, but this, thank you very much, works!!

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The access query wizard does tend to overdo the brackets, basically to compensate for potential inconsistencies - can't remember the term, think it is 'boolean logic' but you need some to ensure the calculation is done correctly

    you can remove some brackets

    HAVING (Sum(tblCCTransaction.Quantity)+(Sum(tblCCTransaction.CallQuantity)*100)>0)

    but remove more and your calculation might be misinterpreted

    this part

    (Sum(tblCCTransaction.CallQuantity)*100)>0

    might be interpreted as true so adding -1 to Sum(tblCCTransaction.Quantity)

    you also don't need to use the table name if the field name is unique within the fields available (e.g. your equityID field is used in both tables so is not unique - I use PK and FK rather than ID for this reason plus they also identify which end of the relationship I am dealing with) so you could just have

    HAVING (Sum(Quantity)+(Sum(CallQuantity)*100)>0)

    but go back to the query designer and save and access will put it all back


  6. #6
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12
    Quote Originally Posted by Ajax View Post
    The access query wizard does tend to overdo the brackets, basically to compensate for potential inconsistencies - can't remember the term, think it is 'boolean logic' but you need some to ensure the calculation is done correctly

    you can remove some brackets

    HAVING (Sum(tblCCTransaction.Quantity)+(Sum(tblCCTransaction.CallQuantity)*100)>0)

    but remove more and your calculation might be misinterpreted

    this part

    (Sum(tblCCTransaction.CallQuantity)*100)>0

    might be interpreted as true so adding -1 to Sum(tblCCTransaction.Quantity)

    you also don't need to use the table name if the field name is unique within the fields available (e.g. your equityID field is used in both tables so is not unique - I use PK and FK rather than ID for this reason plus they also identify which end of the relationship I am dealing with) so you could just have

    HAVING (Sum(Quantity)+(Sum(CallQuantity)*100)>0)

    but go back to the query designer and save and access will put it all back


    Thanks for all that, it's good info. I'll have to spend a little time plodding through it, but I really like your idea of using PK and FK Instead of ID.

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

Similar Threads

  1. Replies: 19
    Last Post: 07-17-2018, 05:25 AM
  2. Replies: 13
    Last Post: 08-16-2017, 01:36 PM
  3. Replies: 2
    Last Post: 11-28-2016, 09:49 PM
  4. Query ignoring criteria ONLY with parameters
    By Lluewhyn in forum Queries
    Replies: 2
    Last Post: 07-08-2016, 10:34 AM
  5. Replies: 3
    Last Post: 12-28-2013, 09:13 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