Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Warehouse query (Access 2000)

    I have a query called qrydiff which subtracts the reslts from 2 queries.the query is OK, but i want to extend its function and to use it as to point to the whole movement of the stock.The query shows only the substrated quantity, which is correct of course, but i want for the query to show all the items imported and exported. For example in my example Productid 321 is missing in the qryDiff. How can i include all the products in the qryDiff ?
    Attached Files Attached Files

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

    Re: Warehouse query (Access 2000)

    You can create a union query to select all products for which there has been input or output:

    SELECT ProductID
    FROM qryInput
    UNION SELECT ProductID
    FROM qryOutput;

    You can use this union query in qryDiff, using outer joins to qryInput and qryOutput on ProductID.

    See attached version.
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse query (Access 2000)

    Thank you Hans for your detailed reply.I couldnt use your example since it is difficult for me to handle this union query, i cannot do it in design view.I coulndt in fact convert the qryDiff into the extended union query .
    The result i have is erroneous.Would you have a look ? Thats is exactly what i want to do, to convert the qryDiff into such a query as to contain all the products importded and exported

    SELECT qryProducts.ProductID, Sum(qryInput.Sum1) AS imported, Sum(qryOutput.Sum2) AS exported
    UINION SELECT (qryProducts OUTER JOIN qryInput ON qryProducts.ProductID = qryInput.ProductID) OUTER JOIN qryOutput ON qryProducts.ProductID = qryOutput.ProductID
    GROUP BY qryProducts.ProductID;

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

    Re: Warehouse query (Access 2000)

    Your SQL makes no sense.
    The database I attached contains a working query, do you have a problem with it?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse query (Access 2000)

    No but as you said i could use this union query in qryDiff, using outer joins to qryInput and qryOutput on ProductID, but i cannot do it, i cannot put the outer join to qryInput and to modigy the qyDiff in such a way as to contain all the products contained in the qryInput and also all the produts sold according to qtyInput.. I am sorry i cannot do it, i cannot convert the qryDiff into an union query. May i ask for help ?

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

    Re: Warehouse query (Access 2000)

    Please see the database I attached to the first reply in this thread. It contains a modified version of qryDiff. This is not a union query itself, but it uses a union query.
    Attached Images Attached Images
    • File Type: png x.png (13.1 KB, 0 views)

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse query (Access 2000)

    Thank you Hans, i have succeeded with your help to rebuild my query

Posting Permissions

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