Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: Return last row for each part number...

    Hiya folks.. I'm trying to figure out an excel only solution to duplicate an access query.

    I have a datasource with multiple entries for a given item number. These entries have a date and a quantity attached to them. I want to return the date and quantity for the most recent entry ONLY.

    In access I accomplish this like so:

    Code:
    SELECT SAFETY_STOCK.Item, _STOCK.[Effective Date], SAFETY_STOCK.UOM, SAFETY_STOCK.Quantity
    FROM SAFETY_STOCK
    WHERE (((SAFETY_STOCK.[Effective Date])=DMax("[Effective Date]","SAFETY_STOCK","Item = '" & [Item] & "'")))
    ORDER BY SAFETY_STOCK.Item;
    How can I duplicate this behavior in excel and produce a worksheet containing only the records where the "Effective Date" is the most recent for a given part?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The attached Excel file shows a simple way, using formulas, to isolate the most recent dates for part numbers into separate columns.

    By sorting the full list by part number and by date, you can identify the last cell for a specific part # (where the following cell does not equal the current cell). The formulas display part # and date for each part's last row.

    Jerry
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I spose I didn't think of doing that with formulas... That's an approach I had to take with a seperate projects requiring a bunch of "department codes" to be split into different sheets.

    I was hoping there would be something simpler I could teach my end users, but this is probably the best I can get without going into VBA.

    Thanks for the suggestion, I didn't think of going that route.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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