Results 1 to 6 of 6

Thread: A Query that only shows the lastest/Last record for each vendor

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,665
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default A Query that only shows the lastest/Last record for each vendor

    If I had a table with vendor history of each purchase they made (the table has the same customer listed numerous times under VendorName). The table has a "CreatedDate" field that puts the date the record was created in it and the table also has an AutoCount field "ID_NISTSL". So there are two ways to determine which is the last record entered into the table.

    Is there a way to query (or only see) the last record (most recent) for each customer?

    Thanks!
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access: A Query that only shows the lastest/Last record for each vendor

    I would recommend using the date field. If the AutoCount field is an Autonum field, you cannot rely on it always to be increasing (though it usually is, it cannot guarantee it).

    Here is SQL code for a query that should do what you want:
    Code:
    SELECT Table1.*
    FROM Table1
    INNER JOIN
    (SELECT Table1.VendorName, Max(Table1.CreatedDate) AS MaxOfCreatedDate
    FROM Table1
    GROUP BY Table1.VendorName) as Lst
    ON Table1.VendorName = Lst.VendorName AND Table1.CreatedDate = Lst.MaxOfCreatedDate;
    Just change all references of "Table1" with the name of your table.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,665
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: A Query that only shows the lastest/Last record for each vendor

    Thanks, Joe

    Wht if I wanted to group by two fields VendorName and CommType? Is it possible to group by more than one?

    And can you add Criteria?

    WHERE (((tbl_NIS_TSL.VendorName) Like [Forms]![frm_NIS_TSL]![Combo227]) AND ((tbl_NIS_TSL.CommType) Like [Forms]![frm_NIS_TSL]![Combo232]) AND ((tbl_NIS_TSL.Debarred) Like [Forms]![frm_NIS_TSL]![Combo229]) AND ((tbl_NIS_TSL.Approval_Status) Like [Forms]![frm_NIS_TSL]![Combo234]))
    ORDER BY tbl_NIS_TSL.VendorName, tbl_NIS_TSL.CreatedDate DESC;
    Last edited by gheyman; Oct 15th, 2019 at 12:18 PM.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access: A Query that only shows the lastest/Last record for each vendor

    Sure. You would just first add it to both the SELECT and GROUP BY clauses of the query in blue, and then add another AND to the end of the JOIN to join on this field too.

    Note that instead of using an embedded query like this, you can do this in two queries, which allows you to do it all using the Query Builder.

    Query 1:
    This is your Aggregate Query, where you only select the fields you want to group by and the field you want to aggregate by.
    You select those fields, and hit the Totals button (looks like a Sigma).
    This adds a Totals row with the phrase "Group By" under all three fields.
    Change "Group By" to "Max" under the CreatedDate field to return the latest date for each grouping.
    So, when this is finished, you have a listing of every unique Grouping, and the latest date for each grouping.

    Query 2:
    If you want other fields displayed from your table other than the three fields in the previous query, simply join the original table to Query 1, joining on ALL three fields from Query 1.
    Then you can elect to return any fields you want from the original table.

    That is what the embedded query I originally created does. It can be done either way.
    Once built, you do not have to open Query 1 at all, just Query 2.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,665
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: A Query that only shows the lastest/Last record for each vendor

    Thank you!
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access: A Query that only shows the lastest/Last record for each vendor

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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