unique records Access 2003
Yes I have duplicate valveIDs with different dates how can I remove all but
the latest date entry tied to a valveID as in my desired result?
"Jerry Whittle" wrote:
SELECT valveID, Max([Date])
FROM YourTable
GROUP BY valveID
ORDER BY valveID;
If you have a duplicate valveID/Mas of Date combos, it will return all ties.
You could try changing SELECT to SELECT DISTINCT .
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"RogerBPWD" wrote:
Problem: Eliminate duplicate values based on one field and still maintain the
availablity of otherfields for the retained record.
I sort on date to bring the most recent record to the top and then want to
eliminate the duplicates of valveID.
valveID Date
1 5-19-10
1 4-10-10
1 2-15-10
2 5-8-10 start point
2 3-15-10
3 5-19-10
3 3-15-10
_____________________
1 5-19-10
2 5-8-10 desired results
3 5-19-10
I have tried Unique value and end up with either ValveID only or no records
eliminated. Same for distinct
After the duplicates are eliminated I then need to do further
sorting/filtering based on date.
Thanks for the help.
|