SUM in a UNION query


A

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;
 
Ad

Advertisements

J

John W. Vinson

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;
Change the

UNION SELECT

to

UNION ALL SELECT

This will stop Access from excluding the duplicates.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
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
 
Ad

Advertisements

A

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:


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]

Microsoft's replacements for these newsgroups:

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top