I have a form that has 3 combo boxes (unbound) and a list box (unbound). The combo boxes get their data from their prospective tables and the list box from a query. The list box shows recipe names.
How do I get my list box to only show each recipe name once? Here is the sql for my query.

SELECT Cuisine_Type_TBL.CuisineID, Cuisine_Type_TBL.Cuisine_Type, Food_Type_TBL.Food_TypeID, Food_Type_TBL.Type, Ingredient_TBL.IngredientID, Ingredient_TBL.Ingredient, Recipe_TBL.Recipe_Name, Recipe_TBL.RecipeID
FROM (Food_Type_TBL INNER JOIN (Cuisine_Type_TBL INNER JOIN Recipe_TBL ON Cuisine_Type_TBL.CuisineID = Recipe_TBL.Cuisine) ON Food_Type_TBL.Food_TypeID = Recipe_TBL.Food_Type) INNER JOIN (Ingredient_TBL INNER JOIN RecipeIngredients_TBL ON Ingredient_TBL.IngredientID = RecipeIngredients_TBL.IngredientID) ON Recipe_TBL.RecipeID = RecipeIngredients_TBL.RecipeID
WHERE (((Cuisine_Type_TBL.Cuisine_Type)=[forms]![SearchByThree_frm]![Cuisine_Type_cbo]) AND ((Food_Type_TBL.Type)=[forms]![SearchByThree_frm]![Dish_Type_cbo]) AND ((Ingredient_TBL.Ingredient)=[forms]![SearchByThree_frm]![Ingredient_Type_cbo])) OR (((Food_Type_TBL.Type)=[forms]![SearchByThree_frm]![Dish_Type_cbo]) AND ((Ingredient_TBL.Ingredient)=[forms]![SearchByThree_frm]![Ingredient_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Cuisine_Type_cbo]))<>False)) OR (((Cuisine_Type_TBL.Cuisine_Type)=[forms]![SearchByThree_frm]![Cuisine_Type_cbo]) AND ((Ingredient_TBL.Ingredient)=[forms]![SearchByThree_frm]![Ingredient_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Dish_Type_cbo]))<>False)) OR (((Ingredient_TBL.Ingredient)=[forms]![SearchByThree_frm]![Ingredient_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Cuisine_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Dish_Type_cbo]))<>False)) OR (((Cuisine_Type_TBL.Cuisine_Type)=[forms]![SearchByThree_frm]![Cuisine_Type_cbo]) AND ((Food_Type_TBL.Type)=[forms]![SearchByThree_frm]![Dish_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Ingredient_Type_cbo]))<>False)) OR (((Food_Type_TBL.Type)=[forms]![SearchByThree_frm]![Dish_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Cuisine_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Ingredient_Type_cbo]))<>False)) OR (((Cuisine_Type_TBL.Cuisine_Type)=[forms]![SearchByThree_frm]![Cuisine_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Dish_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Ingredient_Type_cbo]))<>False)) OR (((IsNull([forms]![SearchByThree_frm]![Cuisine_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Dish_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Ingredient_Type_cbo]))<>False));



Any help would be great.

after your word SELECT, put in the word DISTINCT.

see if that gives you what you want.

SELECT DISTINCT Cuisine_Type,.....

Thank you, but I have already tried that and still get a list box with the recipe name in multiples. I believe since ingredients have multiple items per recipe, distinct only goes that far. Is there any way to limit it to the recipe name field? or set the list box not to show duplicates?

so you only want the listbox to show the items once? at which point you wouldnt need the ingrediants ??

The user is going to be searching for a recipe by one or a combination of choices: type of cuisine, type of dish, main ingredient. So if they want all the recipes for "French" cuisine and with the main ingredient of "chicken" it would list all those. But since they didn't choose what type of dish they wanted there might be many "French" "chicken" recipes in different type of dishes which would repeat the recipe name for each type of dish.

If they choose something in each category, there's no problem.

This post has been edited by redwrym: Apr 11 2012, 11:10 AM

i guess i am not following correctly how the listbox comes into play.

Perhaps others that look at this thread will have a better understanding

Thank you for your time.
The list box gets its data from the query which is updated when the user selects items in the combo boxes.
The data is RecipeID and RecipeName

redwrym,

If you only need the RecipeID and RecipeName, you shouldn't select anything else. Try this:

SELECT DISTINCTROW Recipe_TBL.RecipeID, Recipe_TBL.Recipe_Name
FROM (Food_Type_TBL INNER JOIN (Cuisine_Type_TBL INNER JOIN Recipe_TBL ON Cuisine_Type_TBL.CuisineID = Recipe_TBL.Cuisine) ON Food_Type_TBL.Food_TypeID = Recipe_TBL.Food_Type) INNER JOIN (Ingredient_TBL INNER JOIN RecipeIngredients_TBL ON Ingredient_TBL.IngredientID = RecipeIngredients_TBL.IngredientID) ON Recipe_TBL.RecipeID = RecipeIngredients_TBL.RecipeID
WHERE (((Cuisine_Type_TBL.Cuisine_Type)=[forms]![SearchByThree_frm]![Cuisine_Type_cbo]) AND ((Food_Type_TBL.Type)=[forms]![SearchByThree_frm]![Dish_Type_cbo]) AND ((Ingredient_TBL.Ingredient)=[forms]![SearchByThree_frm]![Ingredient_Type_cbo])) OR (((Food_Type_TBL.Type)=[forms]![SearchByThree_frm]![Dish_Type_cbo]) AND ((Ingredient_TBL.Ingredient)=[forms]![SearchByThree_frm]![Ingredient_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Cuisine_Type_cbo]))<>False)) OR (((Cuisine_Type_TBL.Cuisine_Type)=[forms]![SearchByThree_frm]![Cuisine_Type_cbo]) AND ((Ingredient_TBL.Ingredient)=[forms]![SearchByThree_frm]![Ingredient_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Dish_Type_cbo]))<>False)) OR (((Ingredient_TBL.Ingredient)=[forms]![SearchByThree_frm]![Ingredient_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Cuisine_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Dish_Type_cbo]))<>False)) OR (((Cuisine_Type_TBL.Cuisine_Type)=[forms]![SearchByThree_frm]![Cuisine_Type_cbo]) AND ((Food_Type_TBL.Type)=[forms]![SearchByThree_frm]![Dish_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Ingredient_Type_cbo]))<>False)) OR (((Food_Type_TBL.Type)=[forms]![SearchByThree_frm]![Dish_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Cuisine_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Ingredient_Type_cbo]))<>False)) OR (((Cuisine_Type_TBL.Cuisine_Type)=[forms]![SearchByThree_frm]![Cuisine_Type_cbo]) AND ((IsNull([forms]![SearchByThree_frm]![Dish_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Ingredient_Type_cbo]))<>False)) OR (((IsNull([forms]![SearchByThree_frm]![Cuisine_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Dish_Type_cbo]))<>False) AND ((IsNull([forms]![SearchByThree_frm]![Ingredient_Type_cbo]))<>False));

EDIT: This is just kind of off-the-cuff. Something tells me that three Unionized sub-queries would simplify this quite a bit, but I'm no SQL expert.

Hope this helps,

Dennis

Thank you. That worked great.