Combo Box Distinct not working!

[loo8866]

Hi, Can any1 give me any clues as to what is up with the following code-

SELECT DISTINCT classes.classid, classes.Category FROM Classes;

The combo box still shows up duplicate values!!

 

[boblarson]

Try this instead:

SELECT classes.classid, classes.Category _
FROM Classes _
GROUPBY classes.classid, classes.Category;

 

[loo8866]

Sorry, that doesn't seem to make a difference?!

 

[boblarson]

Can you post your table structure? Maybe a screenshot of your table in design mode and one of the data.

 

[loo8866]

Not really much to it, regarding this anyway!

I have a table - 'Classes' Two if its fields are called classid and category.

On one of my forms I have an unbound combo box which I wish to fill with each category.

With the code above, all the categories are loaded into the combo box, but I only want one of each (no duplicates).

 

[boblarson]

So, what I'm getting at, is that your table is likely not constructed properly if there are more than one of the same classid in the table. Is that your primary Key (classid)? If so, classid should only have one of each of itself in the table and the query I showed you would work. I suspect that you either don't have a primary key, or if you do you are trying to tie tables together using the wrong data. That's why seeing your structure would help. Can you compact, zip and post it here? (393Kb or less to do so).

 

[loo8866]

classid is the primary key, and is an autonumber, so no duplicates.

It is category i want in the combo! Maybe i have to leave out class id in the select statement on the combo box...

 

[boblarson]

is category in it's own table? If so, you want CategoryID and Category Description from the Category table.

 

[loo8866]

Sorry, no category is in Class!

 

[boblarson]

Apparently we're not communicating well. Unless you can post your database (go to Tools > Database Tools > Compact and Repair and compact first, then zip it) here I think we'll still be missing on what each other is talking about.

 

[TK5]

I am running into the same thing. This is really not easy to figure out. Here is a screenshot of my table (attached) and the query (below) Let me know what else you want to see, what I should check or where I should be looking. I appreciate your help with this... its probably something silly that I am overlooking.

Combo Box that is showing dups ROWSOURCE:
SELECT MainDetail.ID, MainDetail.Institution
FROM MainDetail
GROUP BY MainDetail.ID, MainDetail.Institution
ORDER BY MainDetail.Institution;

Table MainDetail: Attached