Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2014
    Posts
    4

    Question Unanswered: SELECT statement which doesn't display zero sum records

    I've created a simple report which lists all of my customers products, and sums the "incoming stock minus outgoing stock", to give me their stock levels at any date. I want to make a second version of this report, which hides those products whose "incoming stock minus outgoing stock" sums to zero.

    My problem is purely down to syntax. I thought I could just add the following to the end of my reports Record Source statement, but it doesn't work. I'm being told that I can't use an aggregate function (the sum) in a WHERE clause.

    Code:
    AND Sum((tblOrderDetails.QuantityIncoming)-(tblOrderDetails.QuantityOutgoing)<>0);
    The working SQL query (without the statement above) is :

    Code:
    SELECT
    tblClients.ClientName, tblClients.ClientActive, tblOrders.TransactionDate, tblOrderDetails.ProductCode, tblOrderDetails.QuantityIncoming, tblOrderDetails.QuantityOutgoing, tblOrders.BassettJobNumber, tblOrders.CustomerName, tblOrders.HaulierCompanyName, tblOrders.CustomerRef, tblOrders.OrderID, tblOrderDetails.OrderID, tblProducts.ProductDescription 
    FROM
    (tblClients INNER JOIN tblProducts ON tblClients.[ClientName] = tblProducts.[ClientName]) INNER JOIN (tblOrders INNER JOIN tblOrderDetails ON tblOrders.[OrderID] = tblOrderDetails.[OrderID]) ON tblProducts.[ProductCode] = tblOrderDetails.[ProductCode] 
    WHERE
    (((tblClients.ClientActive)=True) AND ((tblOrders.TransactionDate)<[Up To Which Date?] Or (tblOrders.TransactionDate)=[Up To Which Date?]));
    Please can someone tell me where to put the statement, so that it doesn't include any stock records on my summary report, which sum to zero.

    Thank you for your help!
    Last edited by TheGnome; 06-03-14 at 08:35.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a left join in place of an inner join on the join between the orders and the products table and make cecertain the products tabel is the first table referenced in that join)
    LEFT JOIN, RIGHT JOIN Operations (Microsoft Access SQL)*[Access 2007 Developer Reference]

    an inner join returns all rows where they match
    a left join returns all rows from the first able in the join, and those rows in the second table mentioned that match
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    4
    Hi, thanks for your help

    The problems seems to be that I can't use an aggregate function as part of the WHERE statement.

    I'm summing the values of each product's incoming and outgoing stock history in order to find the total at any given date.

    So, if I ask to see the stock totals from 01/01/2013 it only sums the incoming stock figures, minus outgoing stock figures, for records with a date up to the date I chose.

    As far as I recall, the joins were all created by the wizard in MS Access. Would changing the joins allow me to use aggregate functions in the WHERE clause, since that seems to be my problem?

    Thank you for your knowledge

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Nothing prevents you from using several subqueries including a WHERE clause each, then perform a JOIN on there subqueries to get the result.
    Have a nice day!

  5. #5
    Join Date
    Jun 2014
    Posts
    4
    Thank you both for your answers. I've got a lot of reading to do before I understand how to do what you suggest! The joins were all created by the wizard.

    Everything I've read so far has told me that my problem is that I can't use an aggregate function (summing all of the incoming and outgoing stock records for each product) in a WHERE statement.
    Last edited by TheGnome; 06-03-14 at 08:24.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in access use a series of queries, each to do a specific function, then join to those queries

    a query (or depedning on your design, queries) calculates the stock
    then join that query to whatever else you need

    you could probablyget the same result by usign a sub query in access
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2014
    Posts
    4
    Oh dear. It looks like I'm in over my head. I hoped it would be a simple fix, like record suppression in Crystal Reports, but I have a lot to read and rework to get this sorted.

    I've just attempted to add a subquery to the end of the working SQL statement, but I've got a syntax error saying that I've missed a bracket out. I suppose that's what I get for using the wizards

    SELECT tblClients.ClientName, tblClients.ClientActive, tblOrders.TransactionDate, tblOrderDetails.ProductCode, tblOrderDetails.QuantityIncoming, tblOrderDetails.QuantityOutgoing, tblOrders.BassettJobNumber, tblOrders.CustomerName, tblOrders.HaulierCompanyName, tblOrders.CustomerRef, tblOrders.OrderID, tblOrderDetails.OrderID, tblProducts.ProductDescription FROM (tblClients INNER JOIN tblProducts ON tblClients.[ClientName] = tblProducts.[ClientName]) INNER JOIN (tblOrders INNER JOIN tblOrderDetails ON tblOrders.[OrderID] = tblOrderDetails.[OrderID]) ON tblProducts.[ProductCode] = tblOrderDetails.[ProductCode] WHERE (((tblClients.ClientActive)=True) AND ((tblOrders.TransactionDate)<[Up To Which Date?] Or (tblOrders.TransactionDate)=[Up To Which Date?])

    AND

    (SELECT tblProducts.ProductDescription FROM tblProducts WHERE Sum(([tblOrderDetails.QuantityIncoming]-[tblOrderDetails.QuantityOutgoing])<>0));

Tags for this Thread

Posting Permissions

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