If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
unique records Access 2003
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. |
Thread Tools | |
Display Modes | |
|
|