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?

 

[tkelley via AccessMonster.com]

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

 

[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.

 

[tkelley via AccessMonster.com]

From Access Help:

GROUP BY Clause
Combines records with identical values in the specified field list into a
single record. A summary value is created for each record if you include an
SQL aggregate function , such as Sum or Count, in the SELECT statement.

Syntax
SELECT fieldlist
FROM table
WHERE criteria
[GROUP BY groupfieldlist]

A SELECT statement containing a GROUP BY clause has these parts:

Part Description
fieldlist The name of the field or fields to be retrieved along with any
field-name aliases, SQL aggregate functions, selection predicates (ALL,
DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table The name of the table from which records are retrieved.
criteria Selection criteria. If the statement includes a WHERE clause, the
Microsoft Jet database engine groups values after applying the WHERE
conditions to the records.
groupfieldlist The names of up to 10 fields used to group records. The order
of the field names in groupfieldlist determines the grouping levels from the
highest to the lowest level of grouping.

SQL already has select distinct

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

[quoted text clipped - 31 lines]