• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

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?
0
hkgal
Asked:
hkgal
  • 2
  • 2
  • 2
  • +3
4 Solutions
 
Rey Obrero (Capricorn1)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
 
mbizupCommented:
hkgal,

Assuming you have a linked or imported table:

SELECT CustID, Max([Date])
FROM YourTable
GROUP BY CustID
0
 
mbizupCommented:
You should also consider renaming your Date field to something like SalesDate (or whatever), since Date is a reserved word in Access.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
gbanikCommented:
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
 
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
 
TracyVBA 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now