Count Unique Records - from a Newbie

  • Thread starter Thread starter RFJ
  • Start date Start date
R

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
 
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
|
|
|
|
|
 
Back
Top