How do I detect the latest record of same ID in Access or Excel?

I have a spreadsheet storing data and I plan to use Access to do data sorting
In the table, i have customer ID and  date field. where ID is not unique. say,
CustID       date
0001        10/1/2010
002          01/09/2009
0001        08/01/2006

how could I identify the latest record of same customer?
hkgalAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gbanikConnect With a Mentor Commented:
In Excel i can tell you... in order to find out the last date for an ID use the following array formula
=MAX(IF(A:A=A2,B:B))
In the attached example I have added 2 columkns for you to try out... 1st column says whether the current date is the last date for that ID, the 2nd column gives you the last date of that ID.
Max-Date.xlsx
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

to identify

select * from tablex
where [date]=(select max([date]) from tablex as T where CustID=tablex.custID)

to delete

delete * from tablex
where [date]=(select max([date]) from tablex as T where CustID=tablex.custID)
0
 
mbizupConnect With a Mentor Commented:
hkgal,

Assuming you have a linked or imported table:

SELECT CustID, Max([Date])
FROM YourTable
GROUP BY CustID
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mbizupCommented:
You should also consider renaming your Date field to something like SalesDate (or whatever), since Date is a reserved word in Access.
0
 
StephenJRCommented:
In Excel you could also do a pivot table, using max date as the data field.
0
 
hkgalAuthor Commented:
gbanik...sorry cannot open the file..
0
 
gbanikCommented:
This is a 2007 version that I attached. What version r u using?

Meanwhile I downloaded that file and checked .. for 2007 it works!
0
 
hkgalAuthor Commented:
2003...
0
 
TracyVBA DeveloperCommented:
NFP

For Excel 2003, I modified gbanik's formula a little and expanded the dataset.  See attached.

=MAX(IF($A$2:$A$7=A2,$B$2:$B$7))

Book1.xls
0
 
TracyConnect With a Mentor VBA DeveloperCommented:
Please note that the formula above is an array formula and should be entered with Ctrl + Shift + Enter.  This will add the {} around the formula.  The end result should be:

{=MAX(IF($A$2:$A$7=A2,$B$2:$B$7))}
0
All Courses

From novice to tech pro — start learning today.