LAST RECORD IN A QUERY

Question

Hi All,

I'm quite new in access, I have a table with several fields.

My table have a unique ID, a Branch ID( may contains duplicate values), Mileage, Authority No, and a Date field

I want to extract only the last value inserted per Branch ID for Mileage, Authority No and the Date.

I don't have any clue have to do it, can you please help

if you need more info , don't hesitate

Thanking you

Louix

Answer 1

In a relational database, there is no such thing as "last", except in the context of a given sort order.  When you say you want "the last value inserted", do you mean the one with the most recent Date?  Do you want the query to return one record per Branch ID, with the Mileage, Authority No, and Date, where the Date is the most recent for that Branch ID?  That would one way to do that:

SELECT YourTable.*
FROM YourTable 
INNER JOIN 
(SELECT YourTable.[Branch ID], Max(YourTable.Date) AS MaxDate FROM YourTable GROUP BY YourTable.[Branch ID])  AS MaxDates 
ON (YourTable.Date = MaxDates.MaxDate) AND (YourTable.[Branch ID] = MaxDates.[Branch ID]);

But that may not be what you had in mind.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

In a relational database, there is no such thing as "last", except in the context of a given sort order.  When you say you want "the last value inserted", do you mean the one with the most recent Date?  Do you want the query to return one record per Branch ID, with the Mileage, Authority No, and Date, where the Date is the most recent for that Branch ID?  That would one way to do that:

SELECT YourTable.*
FROM YourTable 
INNER JOIN 
(SELECT YourTable.[Branch ID], Max(YourTable.Date) AS MaxDate FROM YourTable GROUP BY YourTable.[Branch ID])  AS MaxDates 
ON (YourTable.Date = MaxDates.MaxDate) AND (YourTable.[Branch ID] = MaxDates.[Branch ID]);

But that may not be what you had in mind.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Thanks sir your your message, I believe your sql is right too

but after some research on the net, I got what I need, you will see the sql below

SELECT tblGoodwillSub.ID, tblGoodwillSub.BranchID, tblGoodwillSub.strLabWarranty, tblGoodwillSub.strPartWarranty, tblGoodwillSub.strLabLeal, tblGoodwillSub.strPartLeal, tblGoodwillSub.strLabCustomer, tblGoodwillSub.strPartCustomer, tblGoodwillSub.txtVin7, tblGoodwillSub.txtMileage, tblGoodwillSub.txtAuthority, tblGoodwillSub.dtValid, tblGoodwillSub.Count
FROM tblGoodwillSub
WHERE (((tblGoodwillSub.ID)=DMax("ID","tblGoodwillSub","BranchID = " & [branchid])));

Answer 3

That will work, so long as ID will always have increasing values for each record added to the table.  That is true for Access autonumbers that are defined as consecutive rather than random, so long as nothing ever disturbs the sequence.  I've seen rare cases where the autonumber sequence has been disturbed, and consecutive records do not have increasing autonumbers -- usually caused by the insertion of records with their ID fields already assigned.  So while looking for the highest autonumber within the group will probably work in almost all cases, it isn't 100% reliable.  In general, it's best not to rely on an autonumber having any specific value or sequence of values; I'd recommend treating them as unique ID values without any other meaning.

It's also a little bit less efficient to use the VBA DMax() function in a query if you can get the same value with a SQL subquery.  In your case, that could be accomplished by modifying the WHERE clause of your query to this:

    WHERE ID=(SELECT Max(T.ID) FROM tblGoodwillSub T WHERE T.BranchID = tblGoodwillSub.BranchID)

That's still relying on having ascending values in the ID field, but should be more efficient than calling DMax(). 

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

Hi Louix44,

You had mentioned that,"but after some research on the net, I got what I need, you will see the sql below".

If your issue is solved by the query that you had posted in your last reply then I suggest you to mark your own post as an answer.

It will help us to close this thread.

Until you mark the answer, This thread will remain open on our side.

If you want more efficient and robust solution then try to refer the suggestion given by Dirk Goldgar

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].