Sum within Union Query

[MelF]

I have the following query:

SELECT[OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM[UnionQuery1]
WHERE (OrderDate Between [Enter begin date:] And [Enter end date:]) AND ProductName LIKE "*" & [Enter Product Name:] & "*"

UNION SELECT[OrderID],[OrderDate],[ProductID],[ProductName],[Quantity]
FROM[UnionQuery2]
WHERE (OrderDate Between [Enter begin date:] And [Enter end date:]) AND ProductName LIKE "*" & [Enter Product Name:] & "*";

I need to SUM the Quantity field. I've tried many different ways to do this, but keep getting the error: "You tried to execute a query that does not include the specified expression "OrderID" as part of an aggregate function."

Can anyone tell me what I'm doing wrong??

Thanks!!!!!!

 

[ghubbell]

Hi Mel,
Make another query based off of your union query. (The query wizard will do this for you in a second as it just did for me):

SELECT DISTINCTROW TheUnionQuery.OrderID, TheUnionQuery.OrderDate, TheUnionQuery.ProductID, TheUnionQuery.ProductName, Sum(TheUnionQuery.Quantity) AS SumQuantity
FROM TheUnionQuery
GROUP BY TheUnionQuery.OrderID, TheUnionQuery.OrderDate, TheUnionQuery.ProductID, TheUnionQuery.ProductName;

"TheUnionQuery" is the name I gave to your posted query. It works like a champ!
Gord
ghubbell@total.net

 

[MelF]

GHUBBELL - Thanks so much for your post. I copied your query and substituted the name of my query for the name you gave it, so it came out like this:

SELECT DISTINCTROW ProductsUsedbyDateQuery.OrderID, ProductsUsedbyDateQuery.OrderDate, ProductsUsedbyDateQuery.ProductID, ProductsUsedbyDateQuery.ProductName, Sum( ProductsUsedbyDateQuery.Quantity) AS SumQuantity
FROM ProductsUsedbyDateQuery
GROUP BY ProductsUsedbyDateQuery.OrderID, ProductsUsedbyDateQuery.OrderDate, ProductsUsedbyDateQuery.ProductID, ProductsUsedbyDateQuery.ProductName;

I ran it, and everything works except the Sum. It's still giving me output like this:

ProductName SumQuantity
Serial Card Reader - GP510 1
Serial Card Reader - GP510 1
Serial Card Reader - GP510 1
Serial Card Reader - GP510 1
Serial Card Reader - GP510 1
Serial Card Reader - GP510 1
Serial Card Reader - GP510 1
Serial Card Reader - GP510 3
Serial Card Reader - GP510 1
PS/2 Card Reader - GP420 1
Serial Card Reader - GP510 3
PS/2 Card Reader - GP420 2

when I'd like just one amount for Serial Card Reader - GP510, one amount for PS/2, etc. Thanks again for your help!!

 

[ghubbell]

Hi Mel, good to see your progress!
The problem is that in the results of the second query we also include the order and the date. As this makes each record unique, your results will be as received. If you only want to count the product, (even though the date criteria has been established) you'll have to remove orderId and Date from the query. (I included the productID too as it is unique to the product):

SELECT DISTINCTROW ProductsUsedbyDateQuery.ProductID, ProductsUsedbyDateQuery.ProductName, Sum( ProductsUsedbyDateQuery.Quantity) AS SumQuantity
FROM ProductsUsedbyDateQuery
GROUP BY ProductsUsedbyDateQuery.ProductID, ProductsUsedbyDateQuery.ProductName;

Give 'er a go. Let me know how you do!
Gord
ghubbell@total.net