Summation Query

[handbtys]

Hi, I'm a novice user of ACCESS and SQL.

I'm having trouble getting the following SQL to work the way I meant it to. I wanted to for each product, 1) SUM all the quantities ordered 2)SUM all the quantities manufactured, 3)Take the difference to find out my inventory holding.

This is the code that gives me weird results.

SELECT DISTINCTROW Products.ProductID, Sum(Manu_Details.UnitsCompleted) AS [Sum Of UnitsCompleted], Sum(Order_Details.Quantity) AS [Sum Of Quantity], [Sum Of UnitsCompleted]-[Sum Of Quantity] AS Inventory
FROM (Products INNER JOIN Manu_Details ON Products.ProductID = Manu_Details.ProductID) INNER JOIN Order_Details ON Products.ProductID = Order_Details.ProductID
GROUP BY Products.ProductID;


Apparently, if I had 3 orders for productid=2, totalling qty=10, The total quantity ordered will be given as 3 X 10. [Number of records multiplied by the correct number I want]

Can anybody point out what's wrong in the SQL i've written above which makes this happen?

Thanks.

 

[mkelly]

Why not do the calculation in your report or on a form? sum the amounts and then subtract them to come up with the difference in a text box.

 

[Jon K]

Originally posted by handbtys
Apparently, if I had 3 orders for productid=2, totalling qty=10, The total quantity ordered will be given as 3 X 10. [Number of records multiplied by the correct number I want]

Can anybody point out what's wrong in the SQL i've written above which makes this happen?

The Manu_Details and Orders_Details tables are not related to each other. You can't put them in one query for summation.


You can sum the two tables in two separate queries. Then combine the two queries in a third query.

If there are products that have not been manufactured or ordered, you will also need to link the Products table in the third query with outer joins and use the Nz() function to convert the empty quantities to zeros. (See the query "qryInventory" in the attached database.)
.

 

[3239]

Adding a date field to your sample

This example you have posted has really help me out in trying to create a database that keeps track of employee's vacation time. The calculations add up perfectly until I tried to add a date field in the Order Details Table(I renamed It ALUsed) in the database Im trying to create. When ever there are different dates, the numbers in the qryInventory(I renamed it AnnualLeave)don't add up. Is there a way to write an sql statement that will correct this.


Thanks, Tony

 

[vengsiva]

Inserting date for summation

Referring to the post by 3239
I think the problem is because there is no date field in the table AlAccued_Details.I inserted a date field Date_Acrued in that table and built a new query from alUsed_details as below
SELECT a.ProductID, a.Date, a.Quantity, (Select sum(quantity) from AlUsed_Details where date<=a.date) AS SumQuantity, (Select sum(UnitsCompleted) From AlAccrued_details where ProductID=a.ProductId and DateAcrued<=a.date) AS SumAccrued, [SumAccrued]-[SumQuantity] AS LeaveBalance
FROM ALUsed_Details AS a
ORDER BY a.Date;

This seems to work.Incidentally I have not altered the name of the field Date in AlUsed_details.it is advisable to avoid using date as field name because it isa reserved word
vengsiva