trouble using totals from a query to calculate a value in a report

  • Thread starter Thread starter PollyPol
  • Start date Start date
P

PollyPol

The code for my query is this: SELECT [tbl mec master 2].[WORK EVENT], [tbl
mec master 2].[DATE REVIEWED], Count([tbl mec master 2].[DATE REVIEWED]) AS
[CountOfDATE REVIEWED], [tbl mec master 2].CORRECT, [tbl mec master 2].DNQ,
Count([tbl mec master 2].ERROR) AS CountOfERROR, [tbl mec assoc].DEPARTMENT
FROM [tbl mec master 2] INNER JOIN [tbl mec assoc] ON [tbl mec master
2].[ASSOCIATE REVIEWED] = [tbl mec assoc].ASSOCIATES
GROUP BY [tbl mec master 2].[WORK EVENT], [tbl mec master 2].[DATE
REVIEWED], [tbl mec master 2].CORRECT, [tbl mec master 2].DNQ, [tbl mec
assoc].DEPARTMENT
HAVING ((([tbl mec master 2].DNQ)=No) AND (([tbl mec assoc].DEPARTMENT)="NB
CONVERSION" Or ([tbl mec assoc].DEPARTMENT)="TOP BLUE NEW BUSINESS"));

My report is bound to this query and has the following groupings:
Header-date reviewed by month, Department, Work Event. I am trying to use
the totals from my query to calculate an accuracy rate. In order to
calculate the accuracy rate, I first need to calculate the number of correct
reviews. This is what I have: Total Reviews =Sum([CountOfDATE REVIEWED]).
Total Errors =Sum([CountOfError]),
This so far works great on the report. Here is the problem. Accuracy Rate
=Sum([CountOfDATE REVIEWED])-Sum([CountOfERROR])/Sum([CountOfDATE REVIEWED]).
My report is calculating the correct accuracy rate, but is displaying the
results for the =Sum([CountOfDATE REVIEWED])-Sum([CountOfERROR]) part of the
calculation directly before the accuracy rate. Example: Count of date
reviewed=17. Count of error=1 Accuracy rate is 94%. My report displays
1694% I'm pretty new to Access and appreciate any help.
 
Thanks for your quick reply. After trying that I still get the same results.

Golfinray said:
Try putting another set of parentheses around the first sum.

PollyPol said:
The code for my query is this: SELECT [tbl mec master 2].[WORK EVENT], [tbl
mec master 2].[DATE REVIEWED], Count([tbl mec master 2].[DATE REVIEWED]) AS
[CountOfDATE REVIEWED], [tbl mec master 2].CORRECT, [tbl mec master 2].DNQ,
Count([tbl mec master 2].ERROR) AS CountOfERROR, [tbl mec assoc].DEPARTMENT
FROM [tbl mec master 2] INNER JOIN [tbl mec assoc] ON [tbl mec master
2].[ASSOCIATE REVIEWED] = [tbl mec assoc].ASSOCIATES
GROUP BY [tbl mec master 2].[WORK EVENT], [tbl mec master 2].[DATE
REVIEWED], [tbl mec master 2].CORRECT, [tbl mec master 2].DNQ, [tbl mec
assoc].DEPARTMENT
HAVING ((([tbl mec master 2].DNQ)=No) AND (([tbl mec assoc].DEPARTMENT)="NB
CONVERSION" Or ([tbl mec assoc].DEPARTMENT)="TOP BLUE NEW BUSINESS"));

My report is bound to this query and has the following groupings:
Header-date reviewed by month, Department, Work Event. I am trying to use
the totals from my query to calculate an accuracy rate. In order to
calculate the accuracy rate, I first need to calculate the number of correct
reviews. This is what I have: Total Reviews =Sum([CountOfDATE REVIEWED]).
Total Errors =Sum([CountOfError]),
This so far works great on the report. Here is the problem. Accuracy Rate
=Sum([CountOfDATE REVIEWED])-Sum([CountOfERROR])/Sum([CountOfDATE REVIEWED]).
My report is calculating the correct accuracy rate, but is displaying the
results for the =Sum([CountOfDATE REVIEWED])-Sum([CountOfERROR]) part of the
calculation directly before the accuracy rate. Example: Count of date
reviewed=17. Count of error=1 Accuracy rate is 94%. My report displays
1694% I'm pretty new to Access and appreciate any help.
Click to expand...
Click to expand...
 
Goffinray-You were correct. =(Sum([CountOfDATE
REVIEWED])-Sum([CountOfERROR]))/Sum([CountOfDATE REVIEWED]) worked. Thank
you.

PollyPol said:
Thanks for your quick reply. After trying that I still get the same results.

Golfinray said:
Try putting another set of parentheses around the first sum.

PollyPol said:
The code for my query is this: SELECT [tbl mec master 2].[WORK EVENT], [tbl
mec master 2].[DATE REVIEWED], Count([tbl mec master 2].[DATE REVIEWED]) AS
[CountOfDATE REVIEWED], [tbl mec master 2].CORRECT, [tbl mec master 2].DNQ,
Count([tbl mec master 2].ERROR) AS CountOfERROR, [tbl mec assoc].DEPARTMENT
FROM [tbl mec master 2] INNER JOIN [tbl mec assoc] ON [tbl mec master
2].[ASSOCIATE REVIEWED] = [tbl mec assoc].ASSOCIATES
GROUP BY [tbl mec master 2].[WORK EVENT], [tbl mec master 2].[DATE
REVIEWED], [tbl mec master 2].CORRECT, [tbl mec master 2].DNQ, [tbl mec
assoc].DEPARTMENT
HAVING ((([tbl mec master 2].DNQ)=No) AND (([tbl mec assoc].DEPARTMENT)="NB
CONVERSION" Or ([tbl mec assoc].DEPARTMENT)="TOP BLUE NEW BUSINESS"));

My report is bound to this query and has the following groupings:
Header-date reviewed by month, Department, Work Event. I am trying to use
the totals from my query to calculate an accuracy rate. In order to
calculate the accuracy rate, I first need to calculate the number of correct
reviews. This is what I have: Total Reviews =Sum([CountOfDATE REVIEWED]).
Total Errors =Sum([CountOfError]),
This so far works great on the report. Here is the problem. Accuracy Rate
=Sum([CountOfDATE REVIEWED])-Sum([CountOfERROR])/Sum([CountOfDATE REVIEWED]).
My report is calculating the correct accuracy rate, but is displaying the
results for the =Sum([CountOfDATE REVIEWED])-Sum([CountOfERROR]) part of the
calculation directly before the accuracy rate. Example: Count of date
reviewed=17. Count of error=1 Accuracy rate is 94%. My report displays
1694% I'm pretty new to Access and appreciate any help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top