Count distinct

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
 
D

Dan Artuso

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

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
 
H

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
 
B

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:)
 
J

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.
 
J

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 ...
-=-=
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top