Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Add criteria to a calculated field (Access 2000 >)

    Hi all,
    Is it possible to add criteria to a calculated field? As in the screenshot, when I run the query it prompts by parameter for the [Total] and also [VAT] fields!
    I notice that I can add criteria to the [Total] field, being the first calculated field, but I cannot add criteria to the [VAT] or [Sales] field, which prompts me. I need to extract all [Sales] that is higher than 5000. How can i do this???
    Attached Images Attached Images
    Regards,
    Rudi

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add criteria to a calculated field (Access 2000 >)

    Sorry about that. It should be Sale: [Total]+[VAT]!!!!

    Just to run that again...
    Total is a direct calculation, built from two fields! So I can add criteria to this calculated field, and it runs fine!
    VAT is a calculated field that is derived from a calculation (and not existing table fields), so i cannot add criteria to this, and run the query...I get a parameter prompt!
    The same goes with Sale, I cannot criteria on that either!
    Your example (Sale: [Total]*[UnitPrice]*1.1) still prompted with a parameter, as Total is a calculated field also. It seems to work only if all fields involved in the expression are existing table fields.

    Sorry, my question might sound a little confusing, but does Acces not allow criteria on expressions that contain names of calculated fields?
    And is there a reason for this? As i find it rather repetitive to have to type for example:
    Total:=[QTY]*[UnitPrice]
    then
    VAT:=[QTY]*[UnitPrice]*0.1
    then
    Sale: [QTY]*[unitprice]+[QTY]*[unitprice]*0.1

    Using this method, I can now add criteria to any other the calculated fields, esp. the Sales one as I wanted sales of >=5000. I am just interested to find out if this is my only solution. Is there no easier way without having to repeat the expressions over and over again???
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add criteria to a calculated field (Access 2000 >)

    Edited to correct mistake

    The answer to your question is yes, but only for direct calculations. Your example shows two levels of calculation: Total and VAT are calculated fields, and Sale is calculated from Total and VAT. If you replace Sale by a direct calculation, it should work:

    Sale: [Quantity]*[UnitPrice]*1.1

    (I assumed that you do not really want to multiply Total and VAT)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add criteria to a calculated field (Access 2000 >)

    Sorry, I meant [Quantity] * [UnitPrice] * 1.1 instead of [Total] * [UnitPrice] * 1.1

    You can also do what you want by creating two queries:
    1. The query you have now to calculate Total, VAT and Sale, but without the criteria.
    2. A query based on the first one, with criteria on the Sale field.

    This way, you allow Access to complete the calculations before applying criteria to the result.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add criteria to a calculated field (Access 2000 >)

    Your answer raises another question! As I have seen, and according to your answer, I assume this is default in access - That it calculates first, and then applies the criteria.
    How would I go about getting access to apply the criteria first, and then calculate on the result of the criteria? Hope this makes sense? Just asking out of curiosity!
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add criteria to a calculated field (Access 2000 >)

    You can switch the order of the two queries: first create a query that applies criteria, then create a second query based on the first one that performs calculations. If that is not what you want, can you explain what you mean, perhaps provide an example of what you want to accomplish?

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add criteria to a calculated field (Access 2000 >)

    Thanx for your patience Hans.
    I should have been more specific in my previous post as my question actually applies to grouped totals. I remembered I ran into this problem a while back, and your answer earlier triggered my memory to this question!

    If I have a grouped totals query, (see >> Attachment sample DB / test query), the calculation is being run first, and then the criteria is applied, with results in all records <5 by my criteria.
    Is there a way to set it up to filter all products <5 first, and then sum those into a summary result???
    Attached Files Attached Files
    Regards,
    Rudi

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Add criteria to a calculated field (Access 2000 >)

    Is the following what you want?

    SELECT Customer.CustomerName, Products.ProductName, Sum(Products.Price) AS SumOfPrice
    FROM Customer INNER JOIN Products ON Customer.CustomerID = Products.CustomerID
    WHERE (((Products.Price)<5))
    GROUP BY Customer.CustomerName, Products.ProductName;

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add criteria to a calculated field (Access 2000 >)

    Yes, exactly what i need!!! Thanx.
    I'm not so up to standard with SQL, but copied it into a query and studied the design view of it.
    I see it adds the Price field a second time, and then uses a where statement. So now it filters the data first, and then calculates the result.
    PERFECT!!!!!!

    Many thanx Hans and Pat. This is going to be saved into a file for future reference.
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •