#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# third query or another solution?

Member Posts: 14

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

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

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
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 ?

• Member Posts: 140
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 ?
:
:
:

• Member Posts: 140
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 ?
: :
: :
: :
:
:

• USAMember Posts: 4,624 ✭✭

____ || http://forcoder.org || free video tutorials and ebooks about || C# PL/SQL PHP C JavaScript Delphi Objective-C Swift MATLAB R C++ Perl Go Assembly Scratch Python Ruby Visual Basic .NET Visual Basic Java Lisp Transact-SQL Bash SAS Apex Alice FoxPro Ada Prolog Crystal Kotlin COBOL F# Rust ABAP Awk Dart D Erlang Fortran Scala ML Hack LabVIEW Scheme Clojure VBScript Julia Lua Logo || _