Count Unique Records - from a Newbie

[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 [1].Org_Name, [1].Comment, [1].[Data-recd]
FROM 1
WHERE ((([1].[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

 

[Eric Butts [MSFT]]

Hi Rob,

The reason you get the result you noted is because in your table your data
has the following:

Org_Name Comment Data-recd
-------------- ------------- ---------------
abc comment1 yes
abc comment2 yes
abc comment3 no

Even if you use Distinct how is Access/JET going to know which of the above
2 to choose?

What you probably are looking for is the following:

SELECT [1].Org_Name, First([1].Comment) AS FirstComment,
First([1].[Data-recd]) AS FirstDataRecd
FROM 1
GROUP BY [1].Org_Name
HAVING((([1].[Data-recd])=Yes))
ORDER BY [1].Org_Name;

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| From: "RFJ" <[email protected]>
| Subject: Count Unique Records - from a Newbie
| Date: Tue, 21 Sep 2004 17:16:45 +0100
| Lines: 20
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.queries
| NNTP-Posting-Host: cpc3-eswd2-5-0-cust122.renf.cable.ntl.com 82.1.173.122
| Path:
cpmsftngxa06.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGXA0
1.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:214011
| X-Tomcat-NG: microsoft.public.access.queries
|
| 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 [1].Org_Name, [1].Comment, [1].[Data-recd]
| FROM 1
| WHERE ((([1].[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
|
|
|
|
|