getting the latest record in access

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

Answer 1

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...

Answer 2

Thanks for your reply 

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

Answer 3

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

Answer 4

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

Answer 5

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!

Answer 6

Thanks Ken and other guru, your suggestion worked:)