Calculation query

[LG]

I posted previous on this but did not really get a response.
I have many tables with different fields and some fields are the same.
What I need to do is find an average of how many entries a processor entered
and how many corrections they had within a date range and than calculate the
total entered- corrections/total entered.
The fields would be Date, QCP_ID, batch_id, and corrections. (the
correction field will have a Y if it was corrected. It was posted that this
would be better done in excel but all the information is housed in the DB to
store for various agency audits.
Any ideas or any additonal info required from me.
Thanks in advance for all your assistance.

 

[Jeff Boyce]

If you've already been advised that Excel would handle this better, don't
let the "housed in" hold you back. Electrons are electrons, whether they
are organized in a relational database or in a spreadsheet.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane Hookom]

You don't have a field named "Processor".
You didn't provide any table names.
Does Batch_ID have any significance in this question?
What's up with "many tables"?
How would you calculate the average manually?

 

[LG]

There are many tables because each table has other fields that do not pertain
to each other and different gov agencies that the data must be protected.
The QCP_ID is the actual processor, the batch_id is what I use as a field to
find out how many were completed that day. I suppose I could do a count of
the QCP_ID as well.
The calculation that is done currently by a supervisor is she counts them
manually. My intent is so that all she would have to do is click a button in
the DB and she would have her monthly report. She actually adds them up
manually and same for the corrections and does the calculation in excel for
each person.
Does that clarify it a little more?

 

[Duane Hookom]

I still don't know what you are averaging? You haven't suggested what the
"manual" procedure is. How about providing some sample records and the
expected results? Please include all table and field names and their data
types.

 

[John W. Vinson]

I posted previous on this but did not really get a response.
I have many tables with different fields and some fields are the same.
What I need to do is find an average of how many entries a processor entered
and how many corrections they had within a date range and than calculate the
total entered- corrections/total entered.
The fields would be Date, QCP_ID, batch_id, and corrections. (the
correction field will have a Y if it was corrected. It was posted that this
would be better done in excel but all the information is housed in the DB to
store for various agency audits.
Any ideas or any additonal info required from me.
Thanks in advance for all your assistance.

You may want to base the totals query doing the counting on a UNION query
stringing the many tables together. You need to go into the SQL window to
create a UNION query, it's not available in the grid. It would be something
like

SELECT Count(*) AS Entered, -Sum(IsCorrected)/Count(*) AS PercentCorrected
FROM
(
SELECT [Corrected] = "Y" AS IsCorrected FROM TableA WHERE TableA.QCP_ID =
[Forms]![YourFormName]![cboQCP_ID]
UNION ALL
SELECT [Corrected] = "Y" AS IsCorrected FROM TableB WHERE TableB.QCP_ID =
[Forms]![YourFormName]![cboQCP_ID]
UNION ALL
SELECT [Corrected] = "Y" AS IsCorrected FROM TableC WHERE TableC.QCP_ID =
[Forms]![YourFormName]![cboQCP_ID]
UNION ALL
<etc etc through all the tables>
SELECT [Corrected] = "Y" AS IsCorrected FROM TableA WHERE TableA.QCP_ID =
[Forms]![YourFormName]![cboQCP_ID]
)

This assumes you have a form YourFormName with an unbound combo box named
cboQCP_ID to select the ID for whom the report will be generated.

 

[LG]

The calculation is total number of records- total number of corrections/
total number of records. by QCP_ID (processor)

Currently the supervisor does this all manually by adding what is done for
the day and how many corrections etc.

The database has a form where the processor fills out and enters a batch id.
I can than run a query by date, QCP_ID (processor), batch_id, countBatch_id
to get how many were actually inputted.
There is a correction field that I could run another query by date, QCP_ID,
Batch_ID, and CountBatch_ID with crieteria =Y (yes)

What I need to do is total by person by month how many they entries they had
and how many corrections they had.
Does that make sense? Sorry for all the confusion it is quite a confusing
process since it has regulations etc on the files.
Thanks

 

[Duane Hookom]

I expect your query might look like:

SELECT QCP_ID, Format([Date],"yyyymm") as YrMth,
Sum(Abs(Correction ="Y")) as NumCorrection,
Count(*) As NumTotal,
Sum(Abs(Correction ="Y"))/Count(*) As PctCorrection
FROM tblNoNameProvided
WHERE [Date] Between Forms!frmDates!txtStartDate AND Forms!frmDates!txtEndDate
GROUP BY QCP_ID, Format([Date],"yyyymm");