hkgal
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mbizup
You should also consider renaming your Date field to something like SalesDate (or whatever), since Date is a reserved word in Access.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In Excel you could also do a pivot table, using max date as the data field.
ASKER
gbanik...sorry cannot open the file..
This is a 2007 version that I attached. What version r u using?
Meanwhile I downloaded that file and checked .. for 2007 it works!
Meanwhile I downloaded that file and checked .. for 2007 it works!
ASKER
2003...
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:
Book1.xls
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:
Book1.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.