Unique Records

[Nirav]

I have a query result with one column has 250 records. Some of them are
duplicates and I want to set up a new query with only unique records from
that 250 records.

I tied to go on design view and in that column on properties but i did not
find option saying unique records.

What should I do?

 

[Duane Hookom]

Change the query to a totals query and find "Group By" in the totals row.

 

[Michel Walsh]

You can switch in SQL view and insert the word DISTINCT right after the
SELECT:

SELECT DISTINCT ...


Note that DISTINCT works on the field you list after:

SELECT DISTICT field1, field2 FROM table


will display distinct couple (field1, field2). If your table has a third
field, that one, in THIS example, will not be considered for "uniqueness".

If you like only DISTINCT field1 values, but want to show the associated
value from field field2 (anyone), you can use, instead:


SELECT field1, LAST(field2)
FROM table
GROUP BY field1



Sure, if your records are 'unique' based on field field1 and field3:


SELECT field1, field3, LAST(field2)
FROM table
GROUP BY field1, field3




Vanderghast, Access MVP

 

[Nirav]

I did now, what should I do?

Change the query to a totals query and find "Group By" in the totals row.

 

[Nirav]

No I am not familiar with that

Are you familiar with "Select Distinct" or "Group By"?

 

[Nirav]

SQL already has select distinct

You can switch in SQL view and insert the word DISTINCT right after the
SELECT:

SELECT DISTINCT ...


Note that DISTINCT works on the field you list after:

SELECT DISTICT field1, field2 FROM table


will display distinct couple (field1, field2). If your table has a third
field, that one, in THIS example, will not be considered for "uniqueness".

If you like only DISTINCT field1 values, but want to show the associated
value from field field2 (anyone), you can use, instead:


SELECT field1, LAST(field2)
FROM table
GROUP BY field1



Sure, if your records are 'unique' based on field field1 and field3:


SELECT field1, field3, LAST(field2)
FROM table
GROUP BY field1, field3




Vanderghast, Access MVP

 

[Duane Hookom]

Maybe you need to define "unique records" and "duplicates". There are great
resources on the web (and even Access Help) on creating various queries.