How to Create Unique List out of Duplicate List

Topic

New Reply

Say we have a table tblParts
ABC
ABC
ABC
DEF
DEF

How to create a query of unique list based upon the above table?
ABC
DEF

Reply | Quote

Replies

Hi Armstrong,
The unique list can be created by following the steps:
1. In the Design View, press Totals button;
2. In Total grid field, choose group by.

Then the query will create a unique list.

Armstrong

Reply | Quote

Set the Unique Values property of the query to yes. This corresponds with changing the SQL to
Select Distinct ….

In Query Design, display the properties box, the click some empty space in the top half of the grid, so the Properties box tells you about the query, rather than one of its fields.

Reply | Quote

Hi John,
Thanks for your help.
The actual tblParts are rather complex and the PartNo repeats freely. That’s exactly where I find your Property sheet won’t work in my case.
The combination of PartID (based on autonumber) and PartNo always render each record unique. The objective here is to create a list of unique PartNo.
Armstrong

Reply | Quote

If you just want to create a list of unique PartNos, you shouldn’t include PartID in the query, only PartNo. Setting “Unique Values” to Yes will then ensure that each PartNo occurs only once.

If you want to do more, for example count the number of occurrences of each PartNo, you need a Totals query that groups by PartNo. But that wasn’t what you asked in the first post in this topic…

Reply | Quote

Hi HansV,
I couldn’t thank enough for your help. Your guide works brilliantly. Yes, selecting PartNo only and setting Unique Values property to Yes succeed to generate a list of unique PartNos.
Thanks again.
Armstrong

Reply | Quote