qry to exlude records with same field value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Miguel

Take a look at the Unique Values property of the query. This will return
one instance of "a".

Another way to do this is to use the Totals query and GroupBy the field
holding (one/more) "a".
 
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)
 
Back
Top