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
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
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. |
#3
|
|||
|
|||
unique records Access 2003
If you need to be able to change data in the records after they are returned
then you will need to use a correlated sub-query to return the records. SELECT * FROM SomeTable WHERE SomeTable.Date = (SELECT Max(Temp.Date) FROM SomeTable As Temp WHERE Temp.ValveID = SomeTable.ValveID) In Design view == Add your table == Select all the fields you want to see == Add criteria under the Date field that looks like the above. Replace SomeTable with the name of your table. (SELECT Max(Temp.[Date]) FROM [SomeTable] As Temp WHERE Temp.ValveID = [SomeTable].ValveID) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County 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 | |
|
|