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?