Using Distinct or Select count?

[tafnuef1]

I created an audit database: I have a Batch of documents that are scanned.
Per Batch I have a Prepper for 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 the document count 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 or select 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 (or Select 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];

 

[vbaInet]

I heard I need a distinct or select count and I needed to place that into my SQL view. Below is that view can someone tell me where I put "Distinct Count"?

With a bit of Googlling here's a link:

http://www.techonthenet.com/sql/distinct.php

Create your count as usual then apply the Distinct predicate. It's not common place to perform a count on distinct records because that indicates you have duplicate records.

 

[tafnuef1]

With a bit of Googlling here's a link:

http://www.techonthenet.com/sql/distinct.php

Create your count as usual then apply the Distinct predicate. It's not common place to perform a count on distinct records because that indicates you have duplicate records.

OK What I did was start a separate query and pulled only Batch Number ID and Document Count. I want Document Count to be grouped by the Batch Number ID so when I put my data together in a report it and for each Prepper i want to show each Batch they Prep and how many documents in each batch and at the end of the report for each prepper I want to sum up how many documents total and how many batches total. Currently when I do that, the Document Count field is duplicated under the batches every time an error is listed. Example if John Doe has Batch number 12345 and there were 3 errors in that batch then it will list those errors but everytime the document count is listed it will duplicate 3 times per batch.. Which is WRONG. SOOOO with that being said below is what I put into my SQL: (BUT STILL ERRORS OUT) HELP

SELECT [DIQA Query].[Batch Number ID], [DIQA Query].[Document Count]
FROM [DIQA Query];
SELECT DISTINCT [DIQA Query].[Document Count]
GROUP BY [DIQA Query].[Batch Number ID]

 

[vbaInet]

Use the wizard to build your query. The wizard doesn't include TWO Select statements.

 

[tafnuef1]

This is what it looked like before I added anything.. so how should it look if I want a Select Distinct of Document Count and grouped by Batch number ID?

SELECT [DIQA Query].[Batch Number ID], [DIQA Query].[Document Count]
FROM [DIQA Query];

 

[vbaInet]

When you've done that Grouping and Sorting is performed on the report itself (not the query).

http://blogs.techrepublic.com.com/howdoi/?p=170
http://www.msaccess-skillbuildit.com/msaccess-reports-grouping.html

That was gotten, again, by Googling.

 

[vbaInet]

This is what it looked like before I added anything.. so how should it look if I want a Select Distinct of Document Count and grouped by Batch number ID?

SELECT [DIQA Query].[Batch Number ID], [DIQA Query].[Document Count]
FROM [DIQA Query];

If you look at the first link it's very clear exactly where DISTINCT was put. Just have a look at the first link.