Access SELECT DISTINCT doesn't work

Question

Ok so I have a super simple database I am using to try to implement things for a more complicated database however I keep hitting walls on what seems like simple things

The database has one table contactsT with the fields ID first_name last_name I have one form with two combo boxes cboFirstName and cboLastName. I have some repeat first names and I only want the combo box to show unique names. I found this absolutely by the numbers tutorial,

https://www.techonthenet.com/access/comboboxes/unique_values2013.php

however it doesn't work which is baffling because it seems very simple. All that happens is I get nothing now showing in the combo box. Don't see an option to attach the database but here is the table

ID  first_name  last_name
1   Oliver      North
2   Oliver      Twist
3   Ren         Saturn
4   John        Smith
5   John        Ringo

the Row source is

SELECT DISTINCT contactsT.first_name FROM contactsT

I cannot reproduce the problem. When the screen comes up the combos are initially blank. When I go to either name combo box and click on the down arrow, it shows a list of the names. RowSourceType should be Table/Query. Try this>> in the form, press Create / QueryDesign / close any popup /// right click and select SqlView / and then paste in your RowSource sql that you have above /// then right click on its "query.." tab and select DataSheetView ..>> you should see the names. and that tells us that the form can see the database. — donPablo

Jiggles32 Jiggles32 · Accepted Answer · 2018-08-21T22:11:28

Your assessment is correct, DISTINCT does not work if you create a ComboBox and use the wizard to link it to a specific source.

The easiest way to bypass this is to simply create a ComboBox, hit cancel on the wizard and then type the query in manually into the property sheet.

This should solve your problem.

FYI: This is due to the fact that Access will try and base the ComboBox on the ID field by default, because of that, the fields you are seeing are technically unique, but only based on the the ID column.

Additionally, this can be circumvented in another method, perhaps you would prefer this (although I find it to be more difficult and slightly frustrating):

You can go into the Property Sheet of the ComboBox and change the Column Widths to include only one column, it most likely currently looks something like 0";1". So to fix it, remove the 0, like 1", this is where Access will "hide" the ID field on the ComboBox. If you then manually edit the query to be DISTINCT for the field you are looking for, that should also fix the problem.

Access SELECT DISTINCT doesn't work

1 Answers