Count distinct

  • Thread starter Thread starter Britt
  • Start date Start date
B

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
 
Hi Britt,
You can do something like this:
SELECT Count(ID),ID
FROM yourTable
Group By ID;
 
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
 
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
 
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:)
 
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?
Click to expand...

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.
 
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 ...
-=-=
 
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?
Click to expand...

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.
 
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];
 
Back
Top