Prev: Update Query not updating selected table
Next: IF ISNUMBER FIND in query design expression
From: RogerBPWD on 27 May 2010 17:08
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.
From: RogerBPWD on 27 May 2010 17:46
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.
From: John Spencer on 28 May 2010 11:32
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.
 | 
Pages: 1
Prev: Update Query not updating selected table
Next: IF ISNUMBER FIND in query design expression