Select Distinct for combobox

[fat controller]

I am trying to get a combo box to only show unique values, but I can't get it to work properly - it still shows duplicates in the list.

The Row Source is set to

SELECT DISTINCT Areas.ID, Areas.Area FROM Areas ORDER BY Areas.Area;

And the combo is bound to column 2

Anyone able to give me a nudge in the right direction please?

 

[jdraw]

Please show us the records that are "duplicates" based on your query.
How are you differentiating unique and distinct?

 

[MarkK]

Say you have data like . . .

ID, Area
1, Boston
2, Washington
3, Boston
4, Boston

. . . then your SQL will show three Bostons, because you have requested a DISTINCT Area.ID

You could do something like . . .

SELECT Min(ID) As ID, Area FROM Areas GROUP BY Area ORDER BY Area;

Which selects the Min(ID) for any duplicated Area name, but then certain IDs will never appear in your list.

 

[fat controller]

The attached is a screenshot of the table where the values for the combo come from.

In design view, I clicked on the three dots to the right hand side of the Row Source, which opened the query for the combo, and I then chose 'Yes' to Unique Values in the property sheet, saving the changes to the SQL statement on exit

 

[jdraw]

fat,
As Markk pointed out, duplicates would have same id and same Area.

 

[fat controller]

Say you have data like . . .

ID, Area
1, Boston
2, Washington
3, Boston
4, Boston

. . . then your SQL will show three Bostons, because you have requested a DISTINCT Area.ID

You could do something like . . .

SELECT Min(ID) As ID, Area FROM Areas GROUP BY Area ORDER BY Area;

Which selects the Min(ID) for any duplicated Area name, but then certain IDs will never appear in your list.

It really didn't like that, and gave an error (attached)

 

[MarkK]

ooops, try . . .

SELECT Min([COLOR="Red"]Areas.[/COLOR]ID) As ID, Area FROM Areas GROUP BY Area ORDER BY Area;

 

[fat controller]

fat,
As Markk pointed out, duplicates would have same id and same Area.

Ah, so it needs more than just the area text itself to be the same. I am destined to see duplicates no matter what then?

 

[fat controller]

Re: ooops, try . . .

SELECT Min([COLOR=Red]Areas.[/COLOR]ID) As ID, Area FROM Areas GROUP BY Area ORDER BY Area;

You sir, are a gentleman and a scholar! Works perfectly, and as the ID's are never seen it matters not a jot that some might not show.

 

[MarkK]

Love it when a plan comes together,

 

[Jibbadiah]

Just a note... If I knew that my area lookup had duplicate entries... A) I would dedupe them and fix all references. B) Remove the possibility of getting duplicates in future. It is worth the effort to stop future workarounds in other parts of your dB. James.

 

[fat controller]

Not entirely sure how I would do that? The table is primarily used for another form altogether where a route ID is selected by one combo box, and then a second shows the areas that are associated. This particular combo is a secondary, where I am effectively 'nicking' the data that is already there - no point having it twice?

 

[Jibbadiah]

Unless the region table has a sub-dimension (e.g. suburb) then it shouldn't have multiple entries for an Area. If there are multiple developers working on the database then you had better just stick with the status quo. If it is just under your management now I would take a back up and have a play to see if I could fix the duplicate entries. Somewhere there will be a reference table that refers to the Area table and stores the id. You could copy the region table (select and CTL C from the table menu... no need to open it). Paste a copy as "structure only" (CTL V). Open the copy table in design view. Put a primary key on the "Region" field. Copy your original table. Paste onto the new table using "append data to existing table". You will be left with a copy table that only has one entry for each region (the first record of each duplicate). Now you join the two tables on region and do an update query to update the id in your primary table with any reference that previously held it.

For your example the query should produce these results:

ID Region New_Id
1 Boston 1
2 Washington 2
3 Boston 1
4 Boston 1

It sounds hard, but it's actually quite easy and if you work with lots of data it is an invaluable trick to remove duplicates from source.