Prev: Update Query not updating selected table
Next: How to use txtboxes and Combo boxes in a form
From: Jerry Whittle on 27 May 2010 15:58
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: Jerry Whittle on 27 May 2010 16:34
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.
 | 
Pages: 1
Prev: Update Query not updating selected table
Next: How to use txtboxes and Combo boxes in a form