Solved

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

Posted on 2010-11-10
10
412 Views
Last Modified: 2012-05-10
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
Comment
Question by:hkgal
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
Comment Utility

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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 100 total points
Comment Utility
hkgal,

Assuming you have a linked or imported table:

SELECT CustID, Max([Date])
FROM YourTable
GROUP BY CustID
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
You should also consider renaming your Date field to something like SalesDate (or whatever), since Date is a reserved word in Access.
0
 
LVL 13

Accepted Solution

by:
gbanik earned 200 total points
Comment Utility
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
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
In Excel you could also do a pivot table, using max date as the data field.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:hkgal
Comment Utility
gbanik...sorry cannot open the file..
0
 
LVL 13

Expert Comment

by:gbanik
Comment Utility
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
 

Author Comment

by:hkgal
Comment Utility
2003...
0
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
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
 
LVL 24

Assisted Solution

by:broomee9
broomee9 earned 100 total points
Comment Utility
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now