By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Any count distinct alternative ?

P: n/a
Hi,

Since Access doesn't support COUTN DISTINCT, I'm facing a new
problem I didn't take into account: how to get the count of PK's in a
complex query. I create queries on the fly -this means I can't use a
stored macro or even a query view-.

A simple example: Let's say you have a simple database with three
tables: People, Laguages and a table with the relationship between
them. How do you retrieve the count of persons who don't speak the
language?

Something like:

SELECT COUNT (PEOPLE.PK_DUDE)
FROM PEOPLE, RELATIONSHIP_PEOPLE, LANGUAGES
WHERE PEOPLE.PK_DUDE = RELATIONSHIP.PK_DUDE
AND RELATIONSHIP_PEOPLE.PK_LANGUAGE = LANGUAGES.PK_LANGUAGE
AND LANGUAGE.NAME <> 'ENGLISH'

returns 3 (correct)

But without DISTINCT in the previous query I'm unable to get the
count of unique PK's in the table PEOPLE.

Any comments / help would be really welcome.

Best regards,

- Leny
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 13 Sep 2004 04:53:56 -0700, le*******@hotmail.com (Leny) wrote:

Try this:
Select count(PK)
From MyQuery
Group by PK

-Tom.

Hi,

Since Access doesn't support COUTN DISTINCT, I'm facing a new
problem I didn't take into account: how to get the count of PK's in a
complex query. I create queries on the fly -this means I can't use a
stored macro or even a query view-.

A simple example: Let's say you have a simple database with three
tables: People, Laguages and a table with the relationship between
them. How do you retrieve the count of persons who don't speak the
language?

Something like:

SELECT COUNT (PEOPLE.PK_DUDE)
FROM PEOPLE, RELATIONSHIP_PEOPLE, LANGUAGES
WHERE PEOPLE.PK_DUDE = RELATIONSHIP.PK_DUDE
AND RELATIONSHIP_PEOPLE.PK_LANGUAGE = LANGUAGES.PK_LANGUAGE
AND LANGUAGE.NAME <> 'ENGLISH'

returns 3 (correct)

But without DISTINCT in the previous query I'm unable to get the
count of unique PK's in the table PEOPLE.

Any comments / help would be really welcome.

Best regards,

- Leny


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.