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. |
#2
|
|||
|
|||
unique records Access 2003
Something like the SQL statement below should work. If you aren't use to
using SQL, do this: Create a plain old select query based on the table and bring down the two fields in question. Next go to View, Totals and select that. You'll notice a new line under the select fields that says Group By under each. Change the Group By under Date to Max. See how that works. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "RogerBPWD" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|