Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    find latest inventory information (A97)

    Given a table named uTblInventoryHistory with the following fields:
    InventoryHistoryID (PK - autonumber, not required for the query, but it is available to use if necessary)
    TankID (long)
    InventoryDate (Date)
    Outstanding (Long)
    InventorySourceID (long)

    The table contains many Inventory records for each TankID. Each record stores (among other items) the Date, outstanding amount (i.e. airspace in the tank), and InventorySourceID when each inventory was taken for the tank

    I want to find the latest date when an inventory was taken for each tank, plus the outstanding amount and InventorySourceID. The results of this query will be used for a subsequent query, so I need to return a query with all the TankIDs. I tried the following, but it does not return the correct data (I am not sure what record it returns for each tank, but visual inspection shows that it's not the latest date.)

    SELECT uTblInventoryHistory.TankID, Last(uTblInventoryHistory.InventoryDate) AS LastDate, Last(uTblInventoryHistory.Outstanding) AS LastOutstanding, last(InventoryDataSourceID) as LastDataSourceID FROM uTblInventoryHistory GROUP BY uTblInventoryHistory.TankID

    Also tried approximately the same query, but using MAX instead of LAST, but it does not work either. Can anybody show me the right syntax to get the information that I need? Thanks.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: find latest inventory information (A97)

    Don't you want the record with the latest (LAST) inventory date - I think the Last(uTblInventoryHistory.Outstanding) and the Last(InventoryDataSourceID) are causing things to be confused. I presume that you don't have two or more inventory records for the same tank on the same date.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find latest inventory information (A97)

    You're correct - I do want the last inventory date. I agree that the two LAST's are probably confusing it -- but I don't know how to unconfuse it...

    This returns the 1093 correct records (latest date for each tankID)
    SELECT uTblInventoryHistory.TankID, Max(uTblInventoryHistory.InventoryDate) AS LastDate FROM uTblInventoryHistory GROUP BY uTblInventoryHistory.TankID;
    but I don't know how to add more fields to the recordset and still get the correct records.

    This returns 2898 records (wrong)
    SELECT uTblInventoryHistory.TankID, Max(uTblInventoryHistory.InventoryDate) AS LastDate, uTblInventoryHistory.InventoryDataSourceID FROM uTblInventoryHistory
    GROUP BY uTblInventoryHistory.TankID, uTblInventoryHistory.InventoryDataSourceID;

    This returns the correct date, but the incorrect InventorySourceID (often the latest InventorySourceID is not the largest value)
    SELECT uTblInventoryHistory.TankID, Max(uTblInventoryHistory.InventoryDate) AS LastDate, Max(uTblInventoryHistory.InventoryDataSourceID) AS LastInventoryDataSourceID FROM uTblInventoryHistory GROUP BY uTblInventoryHistory.TankID;

    This returns the incorrect date (records *should* be chronological, but something is causing them not to be. Besides, I understand that it's not good practice to assume a sorting order in a table.)
    SELECT uTblInventoryHistory.TankID, Last(uTblInventoryHistory.InventoryDate) AS LastDate, Last(uTblInventoryHistory.InventoryDataSourceID) AS LastOfInventoryDataSourceID FROM uTblInventoryHistory GROUP BY uTblInventoryHistory.TankID;

    If I could return the PK for the appropriate records, I could create a second query, but I don't know how to do that. Besides, if I could extract the PK for each record, then I should be able to extract the fields that I *really* want... I am starting to think the solution is to create one query that returns the tankID and the latest date, then relate that back to the original table to extract the other fields. I was hoping to find a solution that did involve a single query.

    There may be cases where two inventories occur on the same date -- I was planning to cross that bridge when I come to it. Trying to get the basic's running first.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

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

    Re: find latest inventory information (A97)

    Your thought is correct, you WILL need two queries to do this, even though you may not like it. First, create a query that returns the latest (Max) date for each TankID:

    SELECT TankID, Max(InventoryDate) AS LastDate
    FROM uTblInventoryHistory
    GROUP BY TankID

    Save it as, say, qryLastDate. Then, create a query based on this query and the table, joined on TankID and the date field, and return the fields from the table you need:

    SELECT uTblInventoryHistory.TankID, uTblInventoryHistory.InventoryDate, uTblInventoryHistory.Outstanding, uTblInventoryHistory.InventoryDataSourceID
    FROM uTblInventoryHistory INNER JOIN qryLastDate ON (uTblInventoryHistory.InventoryDate = qryLastDate.LastDate) AND (uTblInventoryHistory.TankID = qryLastDate.TankID)

    But if you have multiple records for a TankID and a date, you'll have to decide which record to use - how do you determine which is the latest reading for a certain day?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find latest inventory information (A97)

    Thanks for the verification, Hans. Sometimes its hard to know if I have bumped into a technology limitation (SQL) or a knowledge limitation (me!) I will build it the way that you suggest.

    Not yet sure how I will handle the case of multiple inventories on a single day. I recognize that it's something I will need to deal with...
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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