Re: qry to exlude records with same field value

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 06/09/04 

Date: Wed, 09 Jun 2004 18:35:19 -0400

If you want to exclude all records where a value appears in more than one
record, then you might look at the Duplicates query generated by the wizard. An
SQL statement would look something like

SELECT Field1
FROM Table1
GROUP BY Field1
HAVING Count(Field1) = 1

If you need multiple fields returned in the result, you can try

SELECT Field1, First(Field2) as f2, First(Field3) as f3, ...
FROM Table1
GROUP BY Field1
HAVING Count(Field1) = 1

Another way MIGHT be the following UNTESTED SQL statement
SELECT *
FROM Table1
WHERE Table1.PK IN
  (SELECT First(Tmp.PK)
   FROM Table1 as Tmp
   GROUP BY Field1
   HAVING Count(Field1) = 1)

miguel wrote:
>
> Hi
>
> probably very simple but still
> I want to make a qry based on a table
> if in a record say field1 has a value "a"
> and in different record field1 would have the same value
> these records need to be excluded of the qry
>
> is there a simple way to fix this
> thanks
> Miguel

Quantcast