Duplicate Records in Combo box

[czinker]

I have an issue with duplicate records showing in a combo box. I have checked "Yes" for unique values and they still show. I only have one field showing in the drop down and don't care about the other fields for that specific selection.

Any ideas how to remove these duplicates from showing?

 

[Mile-O]

Sounds to me like a normalisation [read up on it!] issue.

It sounds like you are sourcing your combo from a field in the table your choice will eventually go.

Ideally, what you want is your table where the data is being entered into, and another table where your combo data is sourced from.

So, for example, you have a data entry table for employees. When entering your employee data you want to select the office they are located.

Therefore, you do not source your combo from the data entry table (which is what I suspect you may be doing) but from an office table.

Using my example, we would have our employees table

tblEmployees
EmployeeID (Primary Key; Autonumber)
Forename
Surname
Email
OfficeID (Foreign Key, Number)​

Then we have a table for our offices.

tblOffices
OfficeID (Primary Key; Autonumber)
OfficeName
Address1
Address2​

So, our data entry form is bound to a query based on our Employees table. If we had 100 people in there, split across four offices, then supplying our combobox with data from that table is going to give us 100 instances of the office, with many duplications.

Therefore we source our combo from the Offices table, like the sample SQL below

SELECT OfficeID, OfficeName
FROM tblOffices
ORDER BY OfficeName;​

This way, we will only have one instance of each Office.

[Makes maintenance of the database easier, too.]

If this is not what your issue is, then sorry for dragging this out, but at least it may be useful to someone in future.

 

[czinker]

I have attached a picture of the column I'd like to ignore duplicates. I don't want "one" repeating.

 

[pbaldy]

As posted elsewhere:

Normally I'd get the selections from a table that only had one record each, but you can use

SELECT DISTINCT FieldName
FROM TableName

or

SELECT FieldName
FROM TableName
GROUP BY FieldName

 

[czinker]

I am still getting duplicates and I have attached a screen shot. "One" is repeated. Any other ideas?

 

[pbaldy]

Don't need another idea. What is the SQL of that row source?

 

[czinker]

SELECT DISTINCT Sheet1.ID, Sheet1.Vendor_Name FROM Sheet1 ORDER BY Sheet1.Vendor_Name;

 

[pbaldy]

DISTINCT operates on all fields in the SELECT clause, so either the ID or name is different. If not, can you post the db here?

 

[RainLover]

Use a Table that only has One One.

This table is strictly a look up table. You must only populate it with unique records.

If you have a reason for repeating ONE then that reason is wrong.

So delete the second ONE.

 

[czinker]

So is this the problem?
SELECT DISTINCT Sheet1.ID, Sheet1.Vendor_Name

I tried it as just: SELECT DISTINCT Sheet1.Vendor_Name
but then the combobox is empty

 

[pbaldy]

Is it empty, or are you hiding column 1?

 

[czinker]

Column 1 is the ID, I don't want it to use that because 1,one and 4,one I want to just show up in the combo box as one

 

[pbaldy]

Your most recent SQL only has 1 column, so your column widths property needs to change. Of course, if you have 2 ID's with "One", how will you know which is chosen?

 

[czinker]

Is there a way to choose the first "one"? Just thinking

 

[pbaldy]

Sure, but it doesn't make any sense. If you're choosing a vendor, as the picture implies, you'd want to be specific if you have 2 with the same name. If you don't, you should be picking from a vendor table that lists each only once, as I first mentioned.

 

[czinker]

I have attached my database. Any suggestions would be greatly appreciated in helping with organizing and using other tables to have no duplicates in the combobox.

 

[pbaldy]

Again, it doesn't make sense to me to only see 1 "One" when they represent different vendors. But to your question "Is there a way to choose the first "one"?":

SELECT DISTINCT Min(Sheet1.ID) AS MinOfID, Sheet1.Vendor_Name
FROM Sheet1
GROUP BY Sheet1.Vendor_Name
ORDER BY Sheet1.Vendor_Name;

 

[czinker]

Sorry that I'm confusing but this is working for me right now. I don't need all the other info in the fields since "one" is basically the same thing as the other "one". I can see this being a problem later but for now it works like I like.

Thanks!

 

[pbaldy]

No problem.

 

[RainLover]

A lookup table consists of two or more Fields.

The first is Auto Number. Unique. Cannot be duplicated. This is the field that we copy into other Tables to use as an identifier. We use one only Field to identify a particular record. This is the Primary Key for this Table

Other Fields. (Sample Data) We will use the Automobile industry.

Manufacturer Ford Text
Model Courier Text
BodyType Van text

Naturally there could be many records describing many vehicles.

In our Combo Box we set the Column widths at something like, 0,2,2.5,2.5

Note the Zero against the Primary Key. Although it is being used it is hidden from the Public and is the ONLY field that is saved in the Parent Table. The Primary Key field (Auto Number) here would therefore be the Foreign Key in other Tables.

We only copy the PK which is very little data to represent the other three Tables. There is no need to copy all fields.

Hope this helps.