third query or another solution?

In short,I cannot make up a query that substracts the quantities from 2 queries. In order to get help. i'd better explain what my task is, and thento show how i tried to solve it.May be somebody might propose a better solution to this task.So, i have to make a query that enumerats all the product quantities imported by customer number 118. This query will represent the input informationThen i have to make a second query that enumerates all the product quantities ordered by customers that have an affiliate number 2 ( afid = 2)This query will represent the output information

My task is to make a query showing the total liters for all the products having been imported by customer 118 (thats is the input) and afterthat substracting the total liters being sold (the output information)

I have made three queries, but it seems that the relationships is not right since the result i receive are wrong

So my first query with the input information is the following, called InVa

And my third query is the following:SELECT InVa.grade, Sum(InVa.SumOfliters) AS [In], Sum(OutVa.SumOfliters) AS Out, [In]-[Out] AS Stock, InVa.ProductidFROM InVa INNER JOIN OutVa ON InVa.Productid = OutVa.ProductidGROUP BY InVa.grade, InVa.Productid, InVa.ProductidORDER BY InVa.grade;I receive wrong figures that have nothing to do with the real figures.

To my regret i cannot substract the sum of the liters in the second query form the sum of the liters in the first query and thus receivethe net figure of the stock.My knowledge of Access is not enough for that.can somebody help me ?

Comments

That's a lot to try to digest, considering I can't see your table structures or relationships. But the GroupBy clause can be very tricky, so try simplifying your queries and getting rid of the GroupBy clause to start with. You won't be getting the results you want right away, but it might give you a better idea of what's going on when you see the results of a simpler query, without a GroupBy clause. Then you can start adding to that simpler query and work your way back up to what you're looking for here.

: : Help with a query: : : In short,I cannot make up a query that substracts the quantities from 2 queries. In order to get help. i'd better explain what my task is, and then: to show how i tried to solve it.May be somebody might propose a better solution to this task.: So, i have to make a query that enumerats all the product quantities imported by customer number 118. This query will represent the input information: Then i have to make a second query that enumerates all the product quantities ordered by customers that have an affiliate number 2 ( afid = 2): This query will represent the output information: : My task is to make a query showing the total liters for all the products having been imported by customer 118 (thats is the input) and after: that substracting the total liters being sold (the output information): : I have made three queries, but it seems that the relationships is not right since the result i receive are wrong: : So my first query with the input information is the following, called InVa: : : SELECT products.Productid, products.grade, Sum([order details].liters) AS SumOfliters, orders.orderid: FROM (orders INNER JOIN customers ON orders.customerid = customers.Customerid) INNER JOIN ([order details] INNER JOIN products ON [order details].ProductID = products.Productid) ON orders.orderid = [order details].OrderID: WHERE (((orders.customerid)=118)): GROUP BY products.Productid, products.grade, orders.orderid: ORDER BY products.grade;: : My second query with the output information is the folowing, called OutVa: : SELECT products.Productid, products.grade, Sum([order details].liters) AS SumOfliters: FROM (orders INNER JOIN customers ON orders.customerid = customers.Customerid) INNER JOIN ([order details] INNER JOIN products ON [order details].ProductID = products.Productid) ON orders.orderid = [order details].OrderID: WHERE (((customers.afid)=7) AND ((customers.Customerid)<>118)): GROUP BY products.Productid, products.grade: ORDER BY products.grade;: : And my third query is the following:: SELECT InVa.grade, Sum(InVa.SumOfliters) AS [In], Sum(OutVa.SumOfliters) AS Out, [In]-[Out] AS Stock, InVa.Productid: FROM InVa INNER JOIN OutVa ON InVa.Productid = OutVa.Productid: GROUP BY InVa.grade, InVa.Productid, InVa.Productid: ORDER BY InVa.grade;: I receive wrong figures that have nothing to do with the real figures.: : To my regret i cannot substract the sum of the liters in the second query form the sum of the liters in the first query and thus receive: the net figure of the stock.My knowledge of Access is not enough for that.: can somebody help me ?: : :

Something else I just noticed is that you're using [In] and [Out] as fields in your third query, but the third query is where they get their names. I'm not really troubleshooting your whole problem here, because it's a bit hard to follow, but it looks like a fourth query, using the third query as a source, might allow you to subtract In from Out or whatever you want to do.

: That's a lot to try to digest, considering I can't see your table structures or relationships. But the GroupBy clause can be very tricky, so try simplifying your queries and getting rid of the GroupBy clause to start with. You won't be getting the results you want right away, but it might give you a better idea of what's going on when you see the results of a simpler query, without a GroupBy clause. Then you can start adding to that simpler query and work your way back up to what you're looking for here.: : : : : Help with a query: : : : : : In short,I cannot make up a query that substracts the quantities from 2 queries. In order to get help. i'd better explain what my task is, and then: : to show how i tried to solve it.May be somebody might propose a better solution to this task.: : So, i have to make a query that enumerats all the product quantities imported by customer number 118. This query will represent the input information: : Then i have to make a second query that enumerates all the product quantities ordered by customers that have an affiliate number 2 ( afid = 2): : This query will represent the output information: : : : My task is to make a query showing the total liters for all the products having been imported by customer 118 (thats is the input) and after: : that substracting the total liters being sold (the output information): : : : I have made three queries, but it seems that the relationships is not right since the result i receive are wrong: : : : So my first query with the input information is the following, called InVa: : : : : : SELECT products.Productid, products.grade, Sum([order details].liters) AS SumOfliters, orders.orderid: : FROM (orders INNER JOIN customers ON orders.customerid = customers.Customerid) INNER JOIN ([order details] INNER JOIN products ON [order details].ProductID = products.Productid) ON orders.orderid = [order details].OrderID: : WHERE (((orders.customerid)=118)): : GROUP BY products.Productid, products.grade, orders.orderid: : ORDER BY products.grade;: : : : My second query with the output information is the folowing, called OutVa: : : : SELECT products.Productid, products.grade, Sum([order details].liters) AS SumOfliters: : FROM (orders INNER JOIN customers ON orders.customerid = customers.Customerid) INNER JOIN ([order details] INNER JOIN products ON [order details].ProductID = products.Productid) ON orders.orderid = [order details].OrderID: : WHERE (((customers.afid)=7) AND ((customers.Customerid)<>118)): : GROUP BY products.Productid, products.grade: : ORDER BY products.grade;: : : : And my third query is the following:: : SELECT InVa.grade, Sum(InVa.SumOfliters) AS [In], Sum(OutVa.SumOfliters) AS Out, [In]-[Out] AS Stock, InVa.Productid: : FROM InVa INNER JOIN OutVa ON InVa.Productid = OutVa.Productid: : GROUP BY InVa.grade, InVa.Productid, InVa.Productid: : ORDER BY InVa.grade;: : I receive wrong figures that have nothing to do with the real figures.: : : : To my regret i cannot substract the sum of the liters in the second query form the sum of the liters in the first query and thus receive: : the net figure of the stock.My knowledge of Access is not enough for that.: : can somebody help me ?: : : : : : : :