Problems with Totals

[Guest]

I want produce a query that does a very simple stock report. I have tried to
follow Allen Browne's Stock in Hand but I think it is too complicated for
what I need.

What I have are 3 tables - Products, Sold & Bought. Products Primary Key =
ProductID. Sold & Bought both have ProductID in.
I want to sum the quantity bought of all ProductID and then subtract from
the sum of the quantity sold of all ProductID.

Part of the data is as follows:
ProductID QtyBought QtySold QtyBought-Sold
5575 1 1 0
5575 4 1 3

If I use the Sum function in the total Field the result is as follows:
ProductID QtyBought QtySold QtyBought-Sold
5575 25 10 15

Please can somebody help out??!!

 

[PC Datasheet]

What's the problem? 25-10 does equal 15!!

Apparently there are more records for ProductID 5575 than what you show in
the sample of the data.

 

[John Vinson]

I want produce a query that does a very simple stock report. I have tried to
follow Allen Browne's Stock in Hand but I think it is too complicated for
what I need.

What I have are 3 tables - Products, Sold & Bought. Products Primary Key =
ProductID. Sold & Bought both have ProductID in.
I want to sum the quantity bought of all ProductID and then subtract from
the sum of the quantity sold of all ProductID.

Part of the data is as follows:
ProductID QtyBought QtySold QtyBought-Sold
5575 1 1 0
5575 4 1 3

If I use the Sum function in the total Field the result is as follows:
ProductID QtyBought QtySold QtyBought-Sold
5575 25 10 15

Please can somebody help out??!!

My guess is that you have the Joins in your query set up incorrectly,
so that the Products record is appearing five times (because it's
joined to the two other tables and there is more than one match).

Please open the Query in SQL view and post the SQL text here.

John W. Vinson[MVP]

 

[Guest]

Here is the SQL text as requesed:

SELECT Products.ProductID, Products.ProductTitle, Sum([Usborne Orders
2004].BoughtQuantity2004) AS SumOfBoughtQuantity2004, Sum([Customer Orders
2004].OrderQuantity2004) AS SumOfOrderQuantity2004,
Sum([BoughtQuantity2004]-[OrderQuantity2004]) AS Expr1
FROM (Products LEFT JOIN [Usborne Orders 2004] ON Products.ProductID =
[Usborne Orders 2004].ProductID) INNER JOIN [Customer Orders 2004] ON
Products.ProductID = [Customer Orders 2004].ProductID
GROUP BY Products.ProductID, Products.ProductTitle
ORDER BY Products.ProductID;

Hope this helps.

Ian

 

[John Vinson]

Here is the SQL text as requesed:

SELECT Products.ProductID, Products.ProductTitle, Sum([Usborne Orders
2004].BoughtQuantity2004) AS SumOfBoughtQuantity2004, Sum([Customer Orders
2004].OrderQuantity2004) AS SumOfOrderQuantity2004,
Sum([BoughtQuantity2004]-[OrderQuantity2004]) AS Expr1
FROM (Products LEFT JOIN [Usborne Orders 2004] ON Products.ProductID =
[Usborne Orders 2004].ProductID) INNER JOIN [Customer Orders 2004] ON
Products.ProductID = [Customer Orders 2004].ProductID
GROUP BY Products.ProductID, Products.ProductTitle
ORDER BY Products.ProductID;

If the [Usborne Orders 2004] and [Customer Orders 2004] are
independent queries/tables, with no link beteen them, you will need to
sum their quantities separately: you can do it with subqueries, but it
may be more understandable to use three queries:

qrySumOrders:
SELECT ProductID, Sum(OrderQuantity2004) AS SumOfOrderQuantity2004
FROM [Customer Orders]
GROUP BY ProductID;

qrySumBought:
SELECT ProductID, Sum([BoughtQuantity]) AS SumOfBoughtQuantity2004
FROM [Usborne Orders 2004]
GROUP BY ProductID;

qryBoth:

SELECT Products.ProductID, Products.ProductTitle,
qrySumOrders.SumOfOrderQuantity2004,
qrySumBought.SumOfBoughtQuantity2004
FROM (Products Left Join [qrySumBought] ON Products.ProductID =
qrySumBought.ProductID) INNER JOIN [qrySumOrders] ON
Products.ProductID = [qrySumOrders].[ProductID]
ORDER BY ProductID;

John W. Vinson[MVP]

 

[Guest]

Thanks John. Problem solved.

Here is the SQL text as requesed:

SELECT Products.ProductID, Products.ProductTitle, Sum([Usborne Orders
2004].BoughtQuantity2004) AS SumOfBoughtQuantity2004, Sum([Customer Orders
2004].OrderQuantity2004) AS SumOfOrderQuantity2004,
Sum([BoughtQuantity2004]-[OrderQuantity2004]) AS Expr1
FROM (Products LEFT JOIN [Usborne Orders 2004] ON Products.ProductID =
[Usborne Orders 2004].ProductID) INNER JOIN [Customer Orders 2004] ON
Products.ProductID = [Customer Orders 2004].ProductID
GROUP BY Products.ProductID, Products.ProductTitle
ORDER BY Products.ProductID;

If the [Usborne Orders 2004] and [Customer Orders 2004] are
independent queries/tables, with no link beteen them, you will need to
sum their quantities separately: you can do it with subqueries, but it
may be more understandable to use three queries:

qrySumOrders:
SELECT ProductID, Sum(OrderQuantity2004) AS SumOfOrderQuantity2004
FROM [Customer Orders]
GROUP BY ProductID;

qrySumBought:
SELECT ProductID, Sum([BoughtQuantity]) AS SumOfBoughtQuantity2004
FROM [Usborne Orders 2004]
GROUP BY ProductID;

qryBoth:

SELECT Products.ProductID, Products.ProductTitle,
qrySumOrders.SumOfOrderQuantity2004,
qrySumBought.SumOfBoughtQuantity2004
FROM (Products Left Join [qrySumBought] ON Products.ProductID =
qrySumBought.ProductID) INNER JOIN [qrySumOrders] ON
Products.ProductID = [qrySumOrders].[ProductID]
ORDER BY ProductID;

John W. Vinson[MVP]