Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Third query or another solution? (Access 2000)

    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 ?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Third query or another solution? (Access 2000)

    Just a quick look at your SQL suggests that you're trying to subtract records where the customerID <> 118 from records where the customerID = 118. If you want to subtract the total liters sold, wouldn't you want to include customerID 118 in the second query? You say you can't subtract the liters in the second query from the liters in the first and get the answer. What happens? DO you get a zero, and error message or a completely incorrect number?
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Third query or another solution? (Access 2000)

    The first query groups by three fields:
    <pre>GROUP BY products.Productid, products.grade, orders.orderid</pre>

    while the second groups by only two:
    <pre>GROUP BY products.Productid, products.grade</pre>

    I am not sure, but I would have thought that the first query should only have the two of the second query, because you want product totals .
    I am surpised that you need any grouping in the third query. If the first two queries each get the right info, all the third has to do is subtract.

    Do you think that either of the first two queries give the right answers?
    Regards
    John



  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Third query or another solution? (Access 2000)

    I want to thank you ,both of you,for the thoughts you have given to my problems.I am really delighted by the kind attention i have received.
    I have not yet studied your answers in detail, but in the first place,yes, i do not need to have any grouping in the thrist query,it should just substract
    and may be here is the reason for my failures.

    Please accept mu best regards

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Third query or another solution? (Access 2000)

    I refer to your replies for i would like to thank you. Actuallly you have solved my problem and the queries work, and i have another question for which,
    as i understand from the rules of your site, i must send a new thread.But since you are already acquanited with the background oif my problem,
    i wonder shall i put this question under the present thread.If not, please let me know shall i send another new thread.

    On the basis of the three sqls i have made a function, but on the OnCLick event i received the following message:

    "a run sql action requires an argument consisting of an sql statement"

    May i know the reason why my function is wrong"

    Here is my function:


    Public Function FncInventory()
    Dim StrIn As String
    Dim StrOut As String
    Dim StrStock As String


    StrIn = " 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 (((orders.CustomerID) = 118) And ((orders.orderdate) > #1/1/2002#)) " & _
    " GROUP BY products.Productid, products.grade " & _
    " ORDER BY products.grade;"


    StrOut = " 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) = 2) And ((orders.CustomerID) <> 118)) " & _
    " GROUP BY products.Productid, products.grade " & _
    " ORDER BY products.grade;"


    StrStock = " SELECT StrIn.grade, StrLIn.SumOfliters AS [In], StrOut.SumOfliters AS Out, [In]-[Out] AS Stock, StrIn.Productid " & _
    " FROM StrIn LEFT JOIN StrOut ON StrIn.Productid = StrOut.Productid " & _
    " ORDER BY StrIn.grade;"

    DoCmd.RunSQL StrStock

    End Function

    Please accept my best regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Third query or another solution? (Access 2000)

    DoCmd.RunSQL is meant to execute an action query. Action queries are queries that change something in a table; there are several types, for example
    <UL><LI>Update queries change the values in some fields. The SQL is of the form
    UPDATE tablename SET fieldname1 = value1, fieldname2 = value2 WHERE ...
    <LI>Delete queries remove records. The SQL is of the form
    DELETE FROM tablename WHERE ...
    <LI>Append queries add records to a table. The SQL is of the form
    INSERT INTO tablename (fieldname1, fieldname2) SELECT fieldname1, fieldname2 FROM sourcetable WHERE ...
    or
    INSERT INTO tablename (fieldname1, fieldname2) VALUES (value1, value2)
    <LI>Make table queries create a new table. The SQL is of the form
    SELECT fieldname1, fieldname2 INTO tablename FROM sourcetable WHERE ...[/list]The SQL statement you define doesn't change anything, it is a SELECT query. You can't use DoCmd.RunSQL with a select query.
    Instead, you can display the records returned by the select query. To do this, you can use the SQL statement as record source of a form or report, or as row source for a list box or combo box.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •