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

    Sum in a query (Access 2000)

    I have a query called StockFlow that derives its values from three queries.However if one of the queries is blank, then then query
    StockFlow is also not populated.Is there any possibility to use mine query StockFlow when one of the queries is blank?

    My query is the following :
    SELECT qryOnOrder.Productid, qryOnOrder.grade, qryOnOrder.size, Sum(qryOnOrder.SumOfcartons) AS OnOrder, Sum(qrySold.SumOfcartons) AS Sold,

    Sum(qryInflow.SumOfcartons) AS Inflow
    FROM (qryOnOrder INNER JOIN qrySold ON qryOnOrder.Productid = qrySold.Productid) INNER JOIN qryInflow ON qrySold.Productid = qryInflow.Productid
    GROUP BY qryOnOrder.Productid, qryOnOrder.grade, qryOnOrder.size;

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

    Re: Sum in a query (Access 2000)

    "Inner Joins" return records only where fileds match on both sides. You need to replace these by left or right joins.
    If you work with the query grid, then double click the join line, and you will see the dialog box shown below.

    You will also need to add to some NZ functions to replace blanks field by zeros.
    Attached Images Attached Images
    Regards
    John



Posting Permissions

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