Duplicate entries in a combo box

[awade]

i have a combo box that quires a table which is linked to an excel spreadsheet external to the database.

It has a column that is labeled tail number. this column has numerous duplicate entries that I would like to remove so the combo box only displays one of each entry.

I have tried, SELECT DISTINCT [eDNA Quick Status Query].Ind, [eDNA Quick Status Query].[Tail Number], [eDNA Quick Status Query].Pos FROM [eDNA Quick Status Query]; to remove the duplicates but it does not work.

Is there any other way to remove the duplicates?

 

[CJ_London]

within the fields you are selecting you will not have duplicates - you will need to drop one or more of the other fields - if you need them all then you will need to live with the situation.

 

[GrandMasterTuck]

i have a combo box that quires a table which is linked to an excel spreadsheet external to the database.

It has a column that is labeled tail number. this column has numerous duplicate entries that I would like to remove so the combo box only displays one of each entry.

I have tried, SELECT DISTINCT [eDNA Quick Status Query].Ind, [eDNA Quick Status Query].[Tail Number], [eDNA Quick Status Query].Pos FROM [eDNA Quick Status Query]; to remove the duplicates but it does not work.

Is there any other way to remove the duplicates?

Yes. Create a query based on your table (the table that's linked to that excel sheet) and view the query in SQL. It should say SELECT tailNumber FROM tblMyTable ... etc etc etc. Add the word DISTINCT after the word SELECT so that it says SELECT DISTINCT tailNumber etc... (obviously your column names might be different). Your query will now return only the unique tailNumbers and eliminate the duplicates. Use this query as the row source of your combo box.

If you already do this but your're getting duplicate tailNumbers, it's probably because your query is viewing too many columns and is counting those in the distinction. Meaning if you have two rows with the same tailNumber but different Ind values, and your query says SELECT DISTINCT Ind, tailNumber... etc... the query views those two rows as distinct. Instead, have the query ONLY look at the tailNumber. Like this:

SELECT DISTINCT [Tail Number] FROM [Your Excel Linked Table Name];

That way, the query is ONLY looking at that tailNumber column and returning only DISTINCT values. If you have the query look at two columns and return distinct records, it treats the two columns as one put together, and so if you have duplicate tailNumbers with differing Ind values, they're considered distinct rows.