Results 1 to 3 of 3
  1. #1
    smith is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2011
    Posts
    5

    How to perform calculations in rows of a query

    Hi -

    I have a query that pulls records from a table that meet certain date ranges (last week's records, for example). Additionally, I select records using a combo box so that only records that correspond to a certain ID# are included in the query results. From the selected records, I would like to calculate a sum of one of the "columns" in the query, then take that sum and divide it by the sum of a second "column" in the query. Thus I would be generating a "percentage". So the numbers in the first column, might be actual measured values, and the numbers in the second column might be target values, so my percentage is an indication of how close the measurements were to the expected values, for that one particular ID#.

    Table: (Dates not shown)
    ID# Actual_Value Expected_Value Date
    A123 15 20
    A123 16 20
    A123 15 20
    B123 20 20
    B123 19 20
    etc...

    Query: (Only 2 records met the date requirements in the query)
    Date ID# Actual_Value Expected_Value
    ... A123 15 20
    ... A123 16 20

    Now I created a report for my query and placed in the body of the report three text boxes, the first contains the SUM of the first column, the second text box contains the SUM of the second column, and the third text box contains an expression dividing the first text box by the second text box.

    Report:
    ID# Sum of Actual_Value Sum of Expected_Value %
    A123 31 40 77.5%
    A123 31 40 77.5%

    The math seems to be correct, however, my report ends up repeating the three text boxes I described above once each time for each record that is in the query. So if last weeks data for a certain ID# included 2 records, my report would show 2 rows with the three text boxes all showing me the correct data.

    My question is this - is there a method for me to perform calculations on rows of data without placing the calculation in a report? Or if I have to use a report, how can I format the report to only show me the one row of calculated data that I am interested in?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would use two queries.
    The first one is the totals query with the criteria.
    The second query is where you would do the math.


    -------------------------
    Also, be aware of reserved words ("Date" is one). Names like "Date", "ID" are not very descriptive

    Object names in ACCESS should only be letters, numbers and the underscore (_).

    Here is a list of reserved words:

    http://allenbrowne.com/AppIssueBadWord.html

  3. #3
    smith is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Apr 2011
    Posts
    5
    Thanks for your input - I used two queries today - the first sets the criteria and the second does the math. For the second query, I used a query type called a "summary query", then added an expression column to do the math.

    The only issue I encountered was when I added the expression column and run the macro, Access prompts me for values of the the two parameters in the expression. But when I ignore the prompts and hit enter, it still does the math correctly.

    I did a quick google search (http://www.pcreview.co.uk/forums/que...-t3663169.html) and found that if I change the "Total:" in the query design view from "Group By" to "Expression" then Access handles the math and does not prompt me for any values.

    Woohoo!

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

Similar Threads

  1. Calculations in Query
    By jdhaldane in forum Queries
    Replies: 5
    Last Post: 12-10-2010, 05:57 AM
  2. Perform an operation on multiple files.
    By newuser in forum Access
    Replies: 9
    Last Post: 11-18-2010, 11:21 AM
  3. MSACCESS Query with calculations?
    By Masterfinn in forum Queries
    Replies: 10
    Last Post: 02-24-2010, 10:51 AM
  4. Replies: 4
    Last Post: 01-15-2010, 06:06 AM
  5. Replies: 2
    Last Post: 11-18-2009, 06:49 PM

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