UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | 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

UtterAccess Addict
Posts: 160



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,321
From: CT



after your word SELECT, put in the word DISTINCT.
ee 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

UtterAccess Addict
Posts: 160



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,321
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

UtterAccess Addict
Posts: 160



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,321
From: CT



i guess i am not following correctly how the listbox comes into play.
erhaps 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

UtterAccess Addict
Posts: 160



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: 12,267
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

UtterAccess Addict
Posts: 160



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

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 21st August 2014 - 05:11 AM