SUM in a UNION query

[atledreier]

Ok, a little background.
I'm making a shopping list for my household. It is a two week shopping list where the user input a week number and the list comes out for that and the following week.

I have a table of ingredients, a table of recipes, and a many to many table connecting the two, with amount of each ingredient.

I then have a table with week, day and recipe to build my menu for each week.

So, I have made a query that sums up each ingredient per week to make, in essense, a shopping list for each of the two weeks.

I then UNION the two queries and sum on each ingredient to make a complete, two week shopping list.

Now, this works fine for the most part. The issue is that if the two weeks end up with the same sum of the same ingredient the results are grouped and my totals come out wrong.

My week one query comes out with ingredient ID=1 and a sum of 0,5 units and the week 2 query have the exact same, only one result is shown, and the total is wrong for that ingredient.

Let me know if you need my tabledefs as well...



The query in question:

Sum
SELECT Oppskrift_ingrediens.Ingrediens, Sum(Oppskrift_ingrediens.Mengde) AS SumAvMengde
FROM Oppskrift_uke INNER JOIN Oppskrift_ingrediens ON Oppskrift_uke.Oppskrift = Oppskrift_ingrediens.Oppskrift
GROUP BY Oppskrift_uke.Uke, Oppskrift_ingrediens.Ingrediens
HAVING (((Oppskrift_uke.Uke)=[Skjemaer]![Hovedmeny]![Ukenummerboks]))
ORDER BY Oppskrift_ingrediens.Ingrediens

UNION SELECT Oppskrift_ingrediens.Ingrediens, Sum(Oppskrift_ingrediens.Mengde) AS SumAvMengde
FROM Oppskrift_uke INNER JOIN Oppskrift_ingrediens ON Oppskrift_uke.Oppskrift = Oppskrift_ingrediens.Oppskrift
GROUP BY Oppskrift_uke.Uke, Oppskrift_ingrediens.Ingrediens
HAVING (((Oppskrift_uke.Uke)=[Skjemaer]![Hovedmeny]![Ukenummerboks]+1))
ORDER BY Oppskrift_ingrediens.Ingrediens;

[John W. Vinson]

Post by atledreier
Ok, a little background.
I'm making a shopping list for my household. It is a two week shopping list where the user input a week number and the list comes out for that and the following week.
I have a table of ingredients, a table of recipes, and a many to many table connecting the two, with amount of each ingredient.
I then have a table with week, day and recipe to build my menu for each week.
So, I have made a query that sums up each ingredient per week to make, in essense, a shopping list for each of the two weeks.
I then UNION the two queries and sum on each ingredient to make a complete, two week shopping list.
Now, this works fine for the most part. The issue is that if the two weeks end up with the same sum of the same ingredient the results are grouped and my totals come out wrong.
My week one query comes out with ingredient ID=1 and a sum of 0,5 units and the week 2 query have the exact same, only one result is shown, and the total is wrong for that ingredient.
Let me know if you need my tabledefs as well...
Sum
SELECT Oppskrift_ingrediens.Ingrediens, Sum(Oppskrift_ingrediens.Mengde) AS SumAvMengde
FROM Oppskrift_uke INNER JOIN Oppskrift_ingrediens ON Oppskrift_uke.Oppskrift = Oppskrift_ingrediens.Oppskrift
GROUP BY Oppskrift_uke.Uke, Oppskrift_ingrediens.Ingrediens
HAVING (((Oppskrift_uke.Uke)=[Skjemaer]![Hovedmeny]![Ukenummerboks]))
ORDER BY Oppskrift_ingrediens.Ingrediens
UNION SELECT Oppskrift_ingrediens.Ingrediens, Sum(Oppskrift_ingrediens.Mengde) AS SumAvMengde
FROM Oppskrift_uke INNER JOIN Oppskrift_ingrediens ON Oppskrift_uke.Oppskrift = Oppskrift_ingrediens.Oppskrift
GROUP BY Oppskrift_uke.Uke, Oppskrift_ingrediens.Ingrediens
HAVING (((Oppskrift_uke.Uke)=[Skjemaer]![Hovedmeny]![Ukenummerboks]+1))
ORDER BY Oppskrift_ingrediens.Ingrediens;

Change the

UNION SELECT

to

UNION ALL SELECT

This will stop Access from excluding the duplicates.

[atledreier]

Post by John W. Vinson

Post by atledreier
Ok, a little background.
I'm making a shopping list for my household. It is a two week shopping list where the user input a week number and the list comes out for that and the following week.
I have a table of ingredients, a table of recipes, and a many to many table connecting the two, with amount of each ingredient.
I then have a table with week, day and recipe to build my menu for each week.
So, I have made a query that sums up each ingredient per week to make, in essense, a shopping list for each of the two weeks.
I then UNION the two queries and sum on each ingredient to make a complete, two week shopping list.
Now, this works fine for the most part. The issue is that if the two weeks end up with the same sum of the same ingredient the results are grouped and my totals come out wrong.
My week one query comes out with ingredient ID=1 and a sum of 0,5 units and the week 2 query have the exact same, only one result is shown, and the total is wrong for that ingredient.
Let me know if you need my tabledefs as well...
Sum
SELECT Oppskrift_ingrediens.Ingrediens, Sum(Oppskrift_ingrediens.Mengde) AS SumAvMengde
FROM Oppskrift_uke INNER JOIN Oppskrift_ingrediens ON Oppskrift_uke.Oppskrift = Oppskrift_ingrediens.Oppskrift
GROUP BY Oppskrift_uke.Uke, Oppskrift_ingrediens.Ingrediens
HAVING (((Oppskrift_uke.Uke)=[Skjemaer]![Hovedmeny]![Ukenummerboks]))
ORDER BY Oppskrift_ingrediens.Ingrediens
UNION SELECT Oppskrift_ingrediens.Ingrediens, Sum(Oppskrift_ingrediens.Mengde) AS SumAvMengde
FROM Oppskrift_uke INNER JOIN Oppskrift_ingrediens ON Oppskrift_uke.Oppskrift = Oppskrift_ingrediens.Oppskrift
GROUP BY Oppskrift_uke.Uke, Oppskrift_ingrediens.Ingrediens
HAVING (((Oppskrift_uke.Uke)=[Skjemaer]![Hovedmeny]![Ukenummerboks]+1))
ORDER BY Oppskrift_ingrediens.Ingrediens;

Change the
UNION SELECT
to
UNION ALL SELECT
This will stop Access from excluding the duplicates.
--
John W. Vinson [MVP]
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

Brillant! i knew it had to be something simple! Thank you!