If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count Unique Records - from a Newbie (Repost)
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 [Table].Org_Name, [Table].Comment, [Table].[Data-recd] FROM Table WHERE ((([Table].[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 |
#2
|
|||
|
|||
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 [Table] 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 [Table].Org_Name, [Table].Comment, [Table].[Data-recd] FROM Table WHERE ((([Table].[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 . |
#3
|
|||
|
|||
On Tue, 21 Sep 2004 20:09:45 +0100, "RFJ"
wrote: 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 [Table].Org_Name, [Table].Comment, [Table].[Data-recd] FROM Table WHERE ((([Table].[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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count Unique Records - from a Newbie | RFJ | Running & Setting Up Queries | 1 | September 22nd, 2004 12:25 AM |
Advanced Filter - Unique Records Only shows 2 of the first value and is correct for the rest | L Mehl | General Discussion | 4 | September 2nd, 2004 06:27 AM |
Copy Unique Records ... Formula? | Ken | Worksheet Functions | 5 | November 11th, 2003 11:18 PM |