Combo Box taking List from Query Problem

[music_al]

I have a query where one of the fileds might return


Food_Sub_Type:
Apple
Apple
Apple
Pear
Pear
Banana
Grape
Grape


If I refer to this field in a ComboBox, it is showing me all the fields (including the duplicates) but I just want the ComboBox to show one instance of each value, i.e....

Apple
Pear
Banana
Grape


How do I suppress the duplicates from a Combo Box ?


Thanks

Allan

 

[namliam]

Try going into design view of the query and add distinct:

Select distinct ....

Warning: If this table is going to get big, opening said combo is going to get Slllllooooooow

 

[DCrake]

If the field is being populated from a list of options, such as apple pear banana etc then it is more prudent to make a table of the options and use that table instead and simply store the PK as a FK in the main table. Then when you make a relationship between the two tables use the same two fields. Then should you want to analyse that results in a query you use the PK in the linked table for faster responses.

David

 

[music_al]

The first table in the Hierarchy is FOOD TYPE and this is indeed taken from a table.

COMBO_FOOD_TYPE (Meat, Fruit, Vegetables)
COMBO_SUB_FOOD_TYPE (Pork, Beef, Lamb, Apple, Orange, Banana)

So, if in the top combo box someone selects FRUIT, the Query will return many rows relating to fruit, like...

FOOD TYPE FOOD_SUB_TYPE VARIETY
Fruit Apple Granny Smiths
Fruit Apple Royal Gala
Fruit Apple Golden Delicious
Fruit Orange Blood
Fruit Banana Yellow Type
etc

So, do you see now how the query will return apple many times when FRUIT is picked in the top combo box ?

Any ideas ?

 

[namliam]

Try going into design view of the query and add distinct:

Select distinct ....

Warning: If this table is going to get big, opening said combo is going to get Slllllooooooow

Some ideas already posted and HAPPILY IGNORED

 

[namliam]

Though I agree with David, adding an additional table where you list only the options valid for "Food sub type" is probably the better way to go.
food sub type would then be a combo lookup that would work the same way

 

[music_al]

sorry, my formatting got a bit messed up there so I hope you got the idea

 

[music_al]

I do actually have 3 tables...

tbl_FOOD_TYPE
Food_Type_ID
Food_Type (Meat, Fruit, Vegetable)

tbl_FOOD_SUB_TYPE
Food_Type_ID (from table above)
Food_SubType_ID
Food_SubType (Beef, Pork, Apple, Banana)

tbl_FOOD_VARIETIES
Food_Type_ID (from table above)
Food_SubType_ID (from table above)
Food_Variety_ID
Food_Variety (Leg, Shoulder, Granny Smith, etc)

So, if you imagine the first combo box on the form selecting FRUIT, I want the second combo box to list just Apple, Pear, Orange, Banana etc.

I thought taking this from a query where the Food_Type filed was...

forms!frm_food_order!cbo_food_type_id

This works fine but as I say, Apple might appear 4 or 5 times in my combo box.


Where do I find the DISTINCT property ? I cant locate it anywhere ???????

 

[namliam]

NO YOU CANNOT LOCATE IT ANYWHERE, PERHAPS CAPS WILL HELP?? OH NO WAIT... THAT WONT SO LET ME JUST REPEAT

Try going into design view of the query and add distinct:

Select distinct ....

What you are porbably doing is joining Type > SUB_TYPE > Variety

But your tbl_FOOD_SUB_TYPE should only have 1 version of
Fruit Apple

While your tbl_FOOD_VARIETIES has
Fruit Apple Granny Smiths
Fruit Apple Royal Gala
Fruit Apple Golden Delicious

So you are using the cascade onto the wrong table, using the tbl_FOOD_VARIETIES instead of the tbl_FOOD_SUB_TYPE table.