Count distinct

[Britt]

Hey

I'm using access 97., and have a database with two posts
as the key.. That means that I can have several rows with
one ID.. I need to count how many times this ID appears..
Have tried Distinct, but it gave the same result...
counted all rows.. There must be an easy way of counting
this?

Please help

Britt

 

[Dan Artuso]

Hi Britt,
You can do something like this:
SELECT Count(ID),ID
FROM yourTable
Group By ID;

 

[Michel Walsh]

Hi,


SELECT COUNT(a.*) FROM [SELECT DISTINCT id FROM myTable]. As a


or, better, make a saved query, q1, with SELECT DISTINCT id FROM myTable

then, SELECT COUNT(*) FROM q1



Hoping it may help,
Vanderghast, Access MVP

 

[Howard Brody]

Create a new query in Design view.
Select your table.
Click on View > Totals to display the Total line.
Bring down the ID field twice.
In the Total line, select 'GroupBy' in the first field
and 'Count' in the second.

This should return one row for each ID and give you the
count how many times it appears.

Hope this helps!

Howard Brody

 

[Britt]

Thanks

I got it now! Ended up using a combination of the answers,
as I needed more data in the query.. (Used 2 queries)

Britt

 

[John Vinson]

Hey

I'm using access 97., and have a database with two posts
as the key.. That means that I can have several rows with
one ID.. I need to count how many times this ID appears..
Have tried Distinct, but it gave the same result...
counted all rows.. There must be an easy way of counting
this?

The SQL Standard COUNT DISTINCT clause is unfortunately not supported
in Access. The simplest way to do this is to use a Query based on a
Query (you can also do the same thing using a Subquery). Create a
Query selecting only this field and set its Unique Values property to
true, or equivalently in SQL use

SELECT DISTINCT ID FROM yourtable;

Then base a Totals query *on this query* to count records.

 

[jfp]

I have found no way to use "Distinct' via the Query builder, but, when i
put it directly into the SQL, it seems to work fine ...
-=-=

 

[John Vinson]

But -- my implied question still remains -- i can go behind the scenes
into the SQL and change "Select" to "Select Distinct" (and it works) but
it does not show up in Query Builder design view. Is there any way to
do this?

Yes, but annoyingly Microsoft uses different jargon in the two
contexts.

If you open the query grid and use the View menu option to view the
query's Properties (or right mouseclick in the grey background of the
table and select Properties), one of them is "Unique Values". Setting
it to Yes will add the DISTINCT keyword to the SQL, and vice versa.

 

[tafnuef]

How do you put it in SQL?

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];