Last record update

[trusein]

Good Day,

I have a record which is updated by customer services dept. for collection of container. Every time we received the collection alert from client we update into system. Sometimes we got more than one collection alert per day. Although the Job No is same but the Date Received the Time Received is different.

Example :

ID Job No Date Time
--------------------------------
1 1000 1-8-2005 8:00
2 1000 1-8-2005 8:30
3 1000 2-8-2005 8:30
4 1001 1-8-2005 9:00
5 1001 1-8-2005 9:30

I just want the last update of data and the result shuld be

ID Job No Date Time
--------------------------------
3 1000 2-8-2005 8:30
5 1001 1-8-2005 9:30

TQ

 

[mresann]

You show your Date and Time as different fields. In the database, they should be stored as one field. Keep in mind you can format the display of the date-time field in any manner you choose.

IN addition, the ID field would normally be used to find latest entries. However, I"m not sure if you manually enter the dates and times, in which case the ID numbers may not reflect the latest entry.

Finally, your time field does not have "am/pm" so, although I assume you would use the 24 hour clock, I can't tell from the data you have if that's possible.

OK. Here is my solution, based on a combined Date/Time field called "DateTime".

SELECT Table1.JobNo, Max(Table1.DateTime) AS MaxOfDateTime
FROM Table1
GROUP BY Table1.JobNo
ORDER BY Table1.JobNo;

 

[rohanmalhotra83]

Thanks Mresann... ur solution helped me a lot. was looking for this from past one week.