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.
|