UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

 
   Reply to this topicStart new topic
> Don't Show Duplicutes In A List Box From A Query, Office 2010    
 
   
redwrym
post Apr 11 2012, 10:12 AM
Post#1



Posts: 168
Joined: 4-August 03



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.
Go to the top of the page
 
Bob G
post Apr 11 2012, 10:24 AM
Post#2


UtterAccess VIP
Posts: 10,668
Joined: 24-May 10
From: CT


after your word SELECT, put in the word DISTINCT.
See if that gives you what you want.
SELECT DISTINCT Cuisine_Type,.....
Go to the top of the page
 
redwrym
post Apr 11 2012, 10:42 AM
Post#3



Posts: 168
Joined: 4-August 03



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?
Go to the top of the page
 
Bob G
post Apr 11 2012, 10:54 AM
Post#4


UtterAccess VIP
Posts: 10,668
Joined: 24-May 10
From: CT


so you only want the listbox to show the items once? at which point you wouldnt need the ingrediants ??
Go to the top of the page
 
redwrym
post Apr 11 2012, 11:08 AM
Post#5



Posts: 168
Joined: 4-August 03



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.
Go to the top of the page
 
Bob G
post Apr 11 2012, 11:12 AM
Post#6


UtterAccess VIP
Posts: 10,668
Joined: 24-May 10
From: CT


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
Go to the top of the page
 
redwrym
post Apr 11 2012, 11:23 AM
Post#7



Posts: 168
Joined: 4-August 03



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
Go to the top of the page
 
doctor9
post Apr 11 2012, 02:01 PM
Post#8


UtterAccess Editor
Posts: 13,464
Joined: 29-March 05
From: Wisconsin


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
Go to the top of the page
 
redwrym
post Apr 11 2012, 02:21 PM
Post#9



Posts: 168
Joined: 4-August 03



Thank you. That worked great.
Go to the top of the page
 


RSSSearch   Top   Lo-Fi    30th March 2015 - 06:00 PM