Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Calculate inventory only on last StockTake Date

    Hello! It's been a long time since I've been on the Lounge, but I am working on updating our database and need some help. I need to calculate the inventory on hand only for the last stock take date, not all dates. This query separates the quantities by date, but I only want the last date, not all of them.

    SELECT tblProducts.ProductID, Sum([tblStockTake].[UnitsInStock]*[tblProducts].[QtyPerBox]) AS InStock, tblStockTake.StockDate, Max(tblStockTake.StockDate) AS MaxOfStockDate
    FROM tblProducts INNER JOIN tblStockTake ON tblProducts.ProductID = tblStockTake.ProductID
    GROUP BY tblProducts.ProductID, tblStockTake.StockDate;

    ProductID InStock Date Stock Taken MaxOfStockDate
    TRAENGHHA 108,000.00 9/26/13 9/26/13
    TRAENGHHA 90,000.00 10/1/13 10/1/13
    TRAENGPDIQAA 11,250.00 9/26/13 9/26/13
    TRAENGPOMIATW 198,000.00 9/26/13 9/26/13


    The values in red are what I want. In this case I only have 1 product that was entered twice, to use as an example. Anybody have an answer for me?

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Maybe this will do it:

    Code:
    SELECT P.ProductID, Sum([T].[UnitsInStock]*[tblProducts].[QtyPerBox]) AS InStock, T.StockDate, Max(T.StockDate) AS MaxOfStockDate
    FROM tblProducts P INNER JOIN tblStockTake T ON P.ProductID = T.ProductID
    WHERE T.StockDate = (SELECT Max(StockDate) FROM tblStockTake T2 WHERE T2.ProductID = P.ProductID)
    GROUP BY tblProducts.ProductID, tblStockTake.StockDate;
    Rui
    -------
    R4

  3. #3
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Ruirib,

    I tried your SQL statement, after creating the two tables and entering some test data, but I received an error message that reads:

    "You tried to execute a query that does not include the specified expression 'ProductID' as part of an aggregrate function."

    I thought you were close, so I tried a slight variation of your suggested SQL statement. This seems to return the desired result, with the very limited test data at hand:

    Code:
    SELECT P.ProductID, 
         Sum([T.UnitsInStock]*[P.QtyPerBox]) AS InStock, 
         T.StockDate, Max(T.StockDate) AS MaxOfStockDate
    
    FROM tblProducts AS P INNER JOIN tblStockTake AS T ON P.ProductID = T.ProductID
    
    WHERE T.StockDate=
          (SELECT Max(StockDate) FROM tblStockTake T2 WHERE T2.ProductID = P.ProductID)
    
    GROUP BY P.ProductID, T.StockDate;
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    My apologies for not responding! We've been having some network issues and I've been involved with that.

    I took what ruirib suggested and tweaked it to the code below and that works:

    Code:
    SELECT tblProducts.ProductID, tblProducts.ProductName, Sum([tblStockTake].[UnitsInStock]*[tblProducts].[QtyPerBox]) AS InStock
    FROM tblProducts INNER JOIN tblStockTake ON tblProducts.ProductID = tblStockTake.ProductID
    WHERE (((tblStockTake.StockDate)=(Select Max(StockDate) From [tblStockTake] where [tblProducts].[ProductID]=[tblStockTake].[ProductID])))
    GROUP BY tblProducts.ProductID, tblProducts.ProductName;

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Yes, my code, without testing, had a mistake that you both found. I would have used the first solution, with the Group By P.ProductID, posted by Tom, simply because it's more easy to read and that is what I wanted to type initially.

    Sorry about the mistake, these things usually get caught if you are able to test it and I confess I did not .
    Rui
    -------
    R4

  6. #6
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi 79shultz,

    You might want to have a look at these two subquery help topics, written by former Access MVP Allen Browne:

    Subquery basics
    http://allenbrowne.com/subquery-01.html

    Surviving Subqueries
    http://allenbrowne.com/subquery-02.html

    Allen's site is a fantastic site for learning about Microsoft Access.
    http://allenbrowne.com/tips.html
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

Posting Permissions

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