Distinct Count??? need help badly

[tafnuef1]

Ok, I have these fields from my form:

Batch Number ID:
Document Count: (for Preppers)
Manual Count: (for Indexers)
Prepper Name
Indexer Name
Prepper Errors:
Indexer Errrors

In my reports I want to take a batch: and have the document count or the manual counts (I wil have two diff reports for Preppers and Indexers) to sum up. So I will have a batch.. either a Prepper name or Indexer name. and Document Counts (or Manual counts which manes how many documents were in that batch) and how many errors in that batch. In my reports the document count is duplicating with each error.. So how do I show only one document count PER batch? How do I then sum up how many documents for each individual Batch? For example at the end of my report I am auditing how many batches the Prepper(or indexer) did and how many documents for each batch.. and for the individual batches how many errors there were?

How would I use Distinct Counts? because I will have duplicates because I could very well have a two batches of documents with let's say 250 documents in it?? HELP I need to get these reports finished.

 

[jzwp22]

I'm unclear as to what you are doing. You mention that you had the listed fields on your form. Are all of these fields coming from 1 table or multiple tables? Can you provide your table structure (tables and field names and how the tables are related to one another). Also, could you provide in more detail what process you are trying to model? Perhaps that will give us a better understanding so that we can be of assistance to you.

 

[tafnuef1]

Ok I have created an audit form that supplies data for two tables for a quality audit of batches of records.

First table:
Batch Number ID, Document Count, Manual Document Count, Date of Audit, Prepper Name, Indexer Name


Second Table: Prepper Error, Prepper Doc Type, Indexer Error, Indexer Doc Type

The data is flowing perfect.

Now: In my report I want to have all of the data for just a Prepper:
Prepper Name
Batch Number ID (the ID for the batch of documents)
Document Count (I want the document count of the batch not for each error)
Prepper Error (description of error)
Prepper Error Count (counts number of errors which I set up in a query)

At the bottom of my report I want to show:
Total number of Documents: (of each individual Batch)
Total Number of Errors:
Percentage of Errors divided by Documents

PROBLEM: I have grouped my Prepper names: My Batch Number ID But with each error it shows a document count EVEN if I put Document Count as a group to itself.) It duplicates my numbers for each error. I just want one document count per batch..

I looked up Distcinct counts etc.. I noticed someone saying to set it with no duplicates. However I could very well have 250 documents in different batches for example???

Does this make better sense??

 

[jzwp22]

Are document count and manual document count calculated values or are they just inputted by the user? Does each count represent specific groups of documents? What is the role of the prepper versus the indexer relative to the documents contained with the batch? Basically, I would like an understanding of you are practically doing with the documents. Not what you are doing in the database. If I have an understanding of your process, I can better understand the database.

How does the second table relate to the first?

I'm not sure but the structure of table 2 does not appear to be correct.

If you are trying to capture errors found relative to a batch (listed in table 1) then the structure should be more like this

tblBatchErrors
-pkBatchErrorsID primary key, autonumber
-fkBatchID foreign key relating to table 1
-fkErrorTypeID foreign key to tblErrorTypes
-txtError (description of the error)

tblErrorTypes (2 types of errors: prepper and indexer)
-pkErrorTypeID primary key, autonumber
-txtErrorType