Query Issue - HELP PLS

[kingjes]

Hello All, I need someones help with this one please! I have a dB in Access
that I need to query.
I need to get all of the Approved Ingredients for a region and sub the
ingredients quanity's. I will be passing in a URL var for the Region ID.

Below is a query that works like BUTTA when I do it by the User. But now,
ofcourse, I have to do it for a whole region.



<!--- START This is the new SUM query from the approved_ingredients table.--->
<cfquery name="qList_Ingredients_Sum" datasource="#Application.DataSource#">
SELECT recipes.recipe_name AS recipe_name,
recipes.recipe_id AS recipe_id,
recipes.number_served AS number_served,
recipes.last_day_to_sign_up_for AS last_day_to_sign_up_for,
recipes.recipe_instructions AS recipe_instructions,
recipes.active_recipe,
recipe_ingredients.recipe_ingredient_id,
recipe_ingredients.recipe_id,
recipe_ingredients.ingredient_id,
recipe_ingredients.active_ingredient AS active_ingredient,
ingredients.ingredient_id,
ingredients.ingredient AS ingredient_name,
SUM(approved_ingredients.ingredient_quantity) AS ingredient_quantity,
approved_ingredients.ingredient_id,
approved_ingredients.ingredient_unit AS ingredient_unit,
recipes_signed_up_for.recipe_id,
recipes_signed_up_for.user_id,
users.user_id
FROM recipes, recipe_ingredients, ingredients, approved_ingredients,
recipes_signed_up_for, users
WHERE recipes.recipe_id = recipe_ingredients.recipe_id
AND recipe_ingredients.ingredient_id = ingredients.ingredient_id
AND approved_ingredients.user_id = #url.user_id#
AND approved_ingredients.ingredient_id =
recipe_ingredients.recipe_ingredient_id
AND recipes.recipe_id = recipes_signed_up_for.recipe_id
AND recipes_signed_up_for.user_id = users.user_id
AND recipes_signed_up_for.user_id = #URL.user_id#
AND recipes_signed_up_for.participate = 1
AND recipes_signed_up_for.authorized
GROUP BY recipe_ingredients.ingredient_id,
recipes.recipe_ID,
recipes.recipe_name,
recipes.number_served,
recipes.last_day_to_sign_up_for,
recipes.recipe_instructions,
recipes.active_recipe,
recipe_ingredients.recipe_ingredient_id,
recipe_ingredients.recipe_id,
recipe_ingredients.active_ingredient,
ingredients.ingredient_id,
ingredients.ingredient,
approved_ingredients.ingredient_id,
approved_ingredients.ingredient_unit,
recipes_signed_up_for.recipe_id,
recipes_signed_up_for.user_id,
users.user_id
ORDER BY recipe_ingredients.ingredient_id
</cfquery>


<cfquery name="qList_Ingredients_Sum_final" dbtype="query">
select ingredient_name, ingredient_unit, sum(ingredient_quantity) as abc
from qList_Ingredients_Sum
group by ingredient_name, ingredient_unit
</cfquery>
<!--- END This is the new SUM query from the approved_ingredients table.--->

[kingjes]

Here is what I have so far, with the results under that:

<cfquery name="qList_Ingredients_Sum" datasource="#Application.DataSource#">
SELECT recipes.recipe_name AS recipe_name,
recipes.recipe_id AS recipe_id,
recipes.number_served AS number_served,
recipes.last_day_to_sign_up_for AS last_day_to_sign_up_for,
recipes.recipe_instructions AS recipe_instructions,
recipes.active_recipe,
recipe_ingredients.recipe_ingredient_id,
recipe_ingredients.recipe_id,
recipe_ingredients.ingredient_id,
recipe_ingredients.active_ingredient AS active_ingredient,
ingredients.ingredient_id,
ingredients.ingredient AS ingredient_name,
SUM(approved_ingredients.ingredient_quantity) AS ingredient_quantity,
approved_ingredients.ingredient_id,
approved_ingredients.region_id,
approved_ingredients.ingredient_unit AS ingredient_unit,
recipes_signed_up_for.recipe_id,
recipes_signed_up_for.user_id,
users.user_id
FROM recipes, recipe_ingredients, ingredients, approved_ingredients,
recipes_signed_up_for, users
WHERE recipes.recipe_id = recipe_ingredients.recipe_id
AND recipe_ingredients.ingredient_id = ingredients.ingredient_id
AND approved_ingredients.region_id = #url.region_id#
AND approved_ingredients.ingredient_id =
recipe_ingredients.recipe_ingredient_id
AND recipes.recipe_id = recipes_signed_up_for.recipe_id
AND recipes_signed_up_for.user_id = users.user_id
AND recipes_signed_up_for.user_id IN (SELECT user_id
FROM users
WHERE region_id = #url.region_id#)
AND recipes_signed_up_for.participate = 1
AND recipes_signed_up_for.authorized = 1
GROUP BY recipe_ingredients.ingredient_id,
recipes.recipe_ID,
recipes.recipe_name,
recipes.number_served,
recipes.last_day_to_sign_up_for,
recipes.recipe_instructions,
recipes.active_recipe,
recipe_ingredients.recipe_ingredient_id,
recipe_ingredients.recipe_id,
recipe_ingredients.active_ingredient,
ingredients.ingredient_id,
ingredients.ingredient,
approved_ingredients.ingredient_id,
approved_ingredients.ingredient_unit,
recipes_signed_up_for.recipe_id,
recipes_signed_up_for.user_id,
users.user_id,
approved_ingredients.region_id
ORDER BY ingredients.ingredient
</cfquery>

<cfquery name="qList_Ingredients_Sum_final" dbtype="query">
SELECT region_id,
ingredient_name,
ingredient_unit,
sum(ingredient_quantity) as abc
FROM qList_Ingredients_Sum
WHERE region_id = #url.region_id#
GROUP BY ingredient_name, ingredient_unit, region_id
</cfquery>

<cfoutput query="qList_Ingredients_Sum_final" group="ingredient_name">
#ingredient_name# #qList_Ingredients_Sum_final.abc# #ingredient_unit#<br>
</cfoutput>

[kingjes]

1 Percent Milk 16 Quart
Broccoli 70 Bundle
Brown Sugar 6 Box
Cornstarch 9 Box
Garlic Powder 16 Container
Lemon Juice 6 Container
Margarine 16 Container
Orange Juice (unsweetened) 9 Container
Potato 587 Each
Salsa (fresh) 35 Container
Salt 12 Container
Seasoned Salt 16 Container
Shredded Cheese 35 Bag
Sour Cream 35 Container
Sweet Potato 21 Pound
Vegetable Cooking Spray 6 Can
Vegetable Oil 16 Container
Yogurt (plain) 30 Container

[kingjes]

Here is a link to the relationships:

[url]http://cvwebsolutions.com/temp/MCOE_Relationships.gif[/url]

[kingjes]

If anyone can help me out here!!

[kingjes]

I got it worked out. Thank goodness! It was a pain but I finally got it. My
major issue was the ingreditnt_ id relationships.. it was changed up between
the different tables.
here is the way I didi it.

<cfquery name="qList_Ingredients_Sum" datasource="#Application.DataSource#">
SELECT DISTINCTROW
approved_ingredients.Approved_ingredients_id,
approved_ingredients.user_id,
approved_ingredients.recipe_id,
approved_ingredients.ingredient_id,
approved_ingredients.region_id,
approved_ingredients.ingredient_unit,
Sum(approved_ingredients.ingredient_quantity) AS Sum_Of_ingredient_quantity,
ingredients.ingredient AS ingredient_name
FROM approved_ingredients, ingredients, recipes, recipe_ingredients
WHERE recipes.recipe_id = recipe_ingredients.recipe_id
AND recipe_ingredients.ingredient_id = ingredients.ingredient_id
AND approved_ingredients.ingredient_id =
recipe_ingredients.recipe_ingredient_id
AND approved_ingredients.region_id = 33
GROUP BY approved_ingredients.Approved_ingredients_id,
approved_ingredients.user_id,
approved_ingredients.recipe_id,
approved_ingredients.ingredient_id,
approved_ingredients.ingredient_unit,
approved_ingredients.region_id,
ingredients.ingredient
ORDER BY approved_ingredients.ingredient_id
</cfquery>

<cfquery name="qList_Ingredients_Sum_final" dbtype="query">
SELECT ingredient_name,
ingredient_id,
region_id,
ingredient_unit,
sum(Sum_Of_ingredient_quantity) as abc
FROM qList_Ingredients_Sum
WHERE region_id = 33
GROUP BY ingredient_unit, region_id, ingredient_id, ingredient_name
</cfquery>

<cfoutput query="qList_Ingredients_Sum_final" group="ingredient_id">
#ingredient_name# #ingredient_id# -- #abc# #ingredient_unit#<br>
</cfoutput>