Count Unique Records - from a Newbie (Repost)

[RFJ]

I've got a simple query where I can't get the syntax right. The aim is to
report each unique organisation name once only - but the following code
shows repeats.

SELECT

.Org_Name,

.Comment,

.[Data-recd]
FROM Table
WHERE (((

.[Data-recd])=Yes));


I've been trying to use SELECT DISTINCT (on Org_name) in line 2 but can't
get it to work. Is that the right way of doing it and, if so, can SKS give
me the correct syntax.

TIA

Rob

 

[Gerald Stanley]

Your sample code implies that there are multiple rows in
your table with the same Org_Name, presumably with
different comments. If you only need one of those rows and
you are not bothered as to which comment is returned, then
you could try something along the lines of
SELECT T1.Org_Name, T1.Comment, T1.[Data-recd]
FROM Table AS T1
WHERE T1.Comment IN (SELECT TOP 1 Comment FROM

WHERE Org_Name = T1.Org_Name AND [Data-recd]=Yes)

However, that will still return duplicates if more than 1
row for the same organisation has the same comment.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I've got a simple query where I can't get the syntax right. The aim is to
report each unique organisation name once only - but the following code
shows repeats.

SELECT

.Org_Name,

.Comment,

.[Data-recd]
FROM Table
WHERE (((

.[Data-recd])=Yes));


I've been trying to use SELECT DISTINCT (on Org_name) in line 2 but can't
get it to work. Is that the right way of doing it and, if so, can SKS give
me the correct syntax.

TIA

Rob

.

 

[John Vinson]

I've been trying to use SELECT DISTINCT (on Org_name) in line 2 but can't
get it to work. Is that the right way of doing it and, if so, can SKS give
me the correct syntax.

SELECT DISTINCT

.Org_Name,

.Comment,

.[Data-recd]
FROM Table
WHERE (((

.[Data-recd])=Yes));

will... should anyway... give one record for each unique combination
of Org_Name, Comment and Data-Recd. What are you trying to count? If
each record has a different comment, you'll get the same number of
records with and without the DISTINCT term... is this what you expect?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps