none
getting the latest record in access RRS feed

  • Question

  • Hi,

    I have a simple table which uses primary key as access generated id, below is the table structure

    ID -Primary key (number)

    Customernumber

    Customerlocation

    activecustomer

    I want to get the latest id record using Customernumber,Customerlocation and activecustomer combination.. Is there any way to get it.

    E.g. 

    in the below table 


    ID customerNumber State activecustomer
    18586 43278 AL N
    50326 43278 AL N
    60901    43278 AL Y
    18587 43278 AR Y
    50327 43278 AR Y
    60902  43278 AR N

    I wanted to get outcome as 

    ID customerNumber State activecustomer
    60901 43278            AL Y
    60902 43278            AR N

    Tuesday, January 8, 2019 9:13 PM

All replies

  • Hi,

    Assuming by "last record" you mean the largest value for the ID field, then yes, we can get it by using a Totals query with the Max() function. But to get any other additional information from the table matching the that ID, we'll need to use a subquery. For example:

    SELECT T1.* FROM TableName T1
    INNER JOIN (SELECT Max(ID) AS LastID
    GROUP BY CustomerNumber) T2
    ON T1.CustomerNumber=T2.CustomerNumber

    (untested) Hope it helps...

    Tuesday, January 8, 2019 9:29 PM
  • Thanks for your reply 

    You referred T1 and T2 in your query, but i just have one table 

    Tuesday, January 8, 2019 10:55 PM
  • T1 and T2 would be aliases of the same table. Btw, that code is untested and would not work as-is. For example the subselect is missing a FROM clause.

    Here is a slightly different, maybe simpler way to get the same results:
    1. Create new query to get the Max ID for each CustomerNumber only:
    select CustomerNumber, Max(ID) as MaxID from SimpleTable group by CustomerNumber
    Save that as qryMaxIDperCustomer

    2. Create new query with SimpleTable and qryMaxIDperCustomer
    Join on both CustomerNumber and ID -> MaxID
    Select a.* from SimpleTable a inner join qryMaxIDperCustomer b on a.ID=b.MaxID and a.CustomerNumber=b.CustomerNumber
    This will give you the most current record for each customer.


    -Tom. Microsoft Access MVP

    Tuesday, January 8, 2019 11:54 PM
  • I want to get the latest id record using Customernumber,Customerlocation and activecustomer combination.. Is there any way to get it.









    You can do that simply by grouping a query by CustomerNumber, State and ActiveCustomer, and returning the MAX ID value per group:

    SELECT MAX(YourTable.ID) AS ID,
    CustomerNumber, State, ActiveCustomer
    FROM YourTable
    GROUP BY CustomerNumber, State, ActiveCustomer;

    But that's not what your example shows.  In that the grouping is on CustomerNumber and State only.  To return the MAX ID value could be done by restricting the query by a correlated subquery like this:

    SELECT *
    FROM YourTable AS T1
    WHERE ID =
        (SELECT MAX(ID)
          FROM YourTable AS T2
          WHERE T2.CustomerNumber = T1.CustomerNumber
          AND T2.State = T1.State);


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Wednesday, January 9, 2019 11:07 AM Typo corrected.
    Wednesday, January 9, 2019 10:51 AM
  • Hi,

    Sorry, I must have been in a hurry yesterday and missed a few things in my post. I hope the other suggestions work for you.

    Cheers!

    Wednesday, January 9, 2019 4:02 PM
  • Thanks Ken and other guru, your suggestion worked:)
    Wednesday, January 9, 2019 8:03 PM