I realized I replied to a very old Thread so here goes:
I created an audit database: I have a Batch of documents that are scanned.
Per Batch I have a Prepper With each Batch Number ID (actual Batch of documents) I have: Document count, Date of Audit, Prepper Name, Prepper Error (Error they may have made in scanning the documents) and Prepper Document type (Document type they made an error on)
In a Access Query I want Document count ID to be counted only once so when I total in my reports it only totals the number one time. Currently if I simply bring it up it duplicates that number however many times I have an error in the batch.
I heard I need a distinct count and I needed to place that into my SQL view. Below is that view can someone tell me where I put "Distinct Count"?
here is my SQL view: where Do I put the Distinct Count for the 2nd ID [Document Count]?
SELECT DIQA.[Batch Number ID], Count(DIQA.[Document Count]) AS [CountOfDocument Count], DIQA.[Document Count], DIQA.[Date of Audit], DIQA.[Prepper Name], Count([Audit Errors].[Prepper Error]) AS [CountOfPrepper Error], [Audit Errors].[Prepper Error], [Audit Errors].[Prepper Doctype]FROM DIQA RIGHT JOIN [Audit Errors] ON (DIQA.[Batch Number ID] = [Audit Errors].[Batch Number ID]) AND (DIQA.DIQA = [Audit Errors].[DIQA #])
GROUP BY DIQA.[Batch Number ID], DIQA.[Document Count], DIQA.[Date of Audit], DIQA.[Prepper Name], [Audit Errors].[Prepper Error], [Audit Errors].[Prepper Doctype];
I created an audit database: I have a Batch of documents that are scanned.
Per Batch I have a Prepper With each Batch Number ID (actual Batch of documents) I have: Document count, Date of Audit, Prepper Name, Prepper Error (Error they may have made in scanning the documents) and Prepper Document type (Document type they made an error on)
In a Access Query I want Document count ID to be counted only once so when I total in my reports it only totals the number one time. Currently if I simply bring it up it duplicates that number however many times I have an error in the batch.
I heard I need a distinct count and I needed to place that into my SQL view. Below is that view can someone tell me where I put "Distinct Count"?
here is my SQL view: where Do I put the Distinct Count for the 2nd ID [Document Count]?
SELECT DIQA.[Batch Number ID], Count(DIQA.[Document Count]) AS [CountOfDocument Count], DIQA.[Document Count], DIQA.[Date of Audit], DIQA.[Prepper Name], Count([Audit Errors].[Prepper Error]) AS [CountOfPrepper Error], [Audit Errors].[Prepper Error], [Audit Errors].[Prepper Doctype]FROM DIQA RIGHT JOIN [Audit Errors] ON (DIQA.[Batch Number ID] = [Audit Errors].[Batch Number ID]) AND (DIQA.DIQA = [Audit Errors].[DIQA #])
GROUP BY DIQA.[Batch Number ID], DIQA.[Document Count], DIQA.[Date of Audit], DIQA.[Prepper Name], [Audit Errors].[Prepper Error], [Audit Errors].[Prepper Doctype];