Full Version: Remove duplicates in combobox with SQL
UtterAccess Forums > Microsoft® Access > Access Forms
tomhep
I have a combobox on a form that displays a list of every record of a field. A lot of these records are dupliates.
Is there any way to change the SQL statement for rowsource of the combobox so that it only displays one of each record ?
AJS
Yep. add the "Distinct" keyword:
Select DISTINCT Yourfield from YourTable
hope this helps
-AJ
P.S. Welcome to Utter Access uarulez2.gif
tomhep
I used the combobox wizard to create the combobox and so the SQL was already written. The SQL in the rowsource is as follows and already contains the DISTINCT keyword, but all the duplicates are still displayed. Any ideas please ?
SELECT DISTINCT [Working Tape Log].[Id No], [Working Tape Log].[Interviewing Officer] FROM [Working Tape Log] ORDER BY [Interviewing Officer];
AJS
The Distinct keyword prevents entire duplicate records from being returned. Is that [ID No] field a primary key? if so, it's always unique, so there would be no duplicates as far as the query is concerned.
That exactly are you trying to accomplish? To me it now sounds as if you might have a structural issue.
-AJ
tomhep
Yes, the ID number is the primary field. I have a table of about 2000 unique tapes, but the only unique field is the ID No. There is an 'Interviewing Officer' field which is the same person for a lot of the tapes. I need a combobox that will only display each person only once. I then have some VBA code which runs once the combobox is updated, setting a filter on the form to show all records with that person as the 'Interviewing Officer'.
Sorry if that's not very clear.
AJS
Okay, you definitely have a structure problem.
You need to create a separate table for the interviewing officers, with its own autonumber primary key. The main table should have a long integer field to use as a foreign key to the Officer table.
let me know if I'm not making any sense, and I'll see what I can do.
HunterOfTheShadows
This post interests me as I have a similar problem with a cbo box. I have a table with payments made by insurance co. to our company. the table also has a unique PK for each record, but it is an autonumber.
On the cbo box I pull down the case ID from the Ins co. Each ID may have several payments made, as Ins. co. don't always pay in full. I am trying to drop down the menu to autopop the rest of the form with the records that pertain only to the case ID. I CAN do this, however, if the case has had several payments, that's how many times it shows up in the cbo box pull down menu.
Ohaven't checked for the "DISTINCT" option in the sql, but if it is already there (I wrote the code, so it may not be), then what?
AJS, please keep on trying the help "tomhep" with his problem. I will just incorporate whatever you guys try in my DB. If I find anything, I will post it as well. Thanks.
-Hunter
ScottGem
There are two ways to deal with this. In Tom's case, having a separate table for Interviewing Officers seems the way to go. There may be additional info about the officers that could go in this separate table.
However, in your case, I'm not as sure. The key is whether the CaseID is used as a PK in some other table. If it is, then you should be pulling the case ID from that table and that should be the bound column of the combo. If CaseID is used no other place then in the payments table, then I just pull the one field into your combo.
tomhep
The thing is, is that there is no additional information about the officers that would go in the separate officers table. I only want one table just for the tapes with the fields : [Id No], [Name of person], [Interviewing Officer], [Date of Iterview] and a couple of others.
I've had a bit of an idea :
I want possibly put some VBA code in the 'On Click' or 'On Got Focus' Event of the combobox that searches through every record in the Tapes table and creates a list (in an array) of every Interviewing Officer, listed just once each. Then 'empty' the combobox of its list and 'fill' it with this new list.
What do you think about this ? Is it feasible ? I think I could write the code to do this.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.