get maximum value only 5

[PAULCALLAGHAN]

I'm using Access 2000 and having trouble developing a simple query.

I would like the user to select based on a part number but I only want the results to show the highest (most recent) date value of all the records returned for that particular part number.

Can someone please tell me how I would do this with SQL, or how my query grid would look like to achieve this?

 

[Lothario]

Something like this:

select part_id,part_name,part_date
from yourtable t1
WHERE t1.part_date In ( Select top 1 t2.part_date
from yourtable t2 Where t1.part_id=t2.part_id
Order by t2.part_date DESC )


-L

 

[PAULCALLAGHAN]

Thank you for the prompt reply Lothario.

But when I type it in I get an error message.

Syntax error. in query expression 't1.part_date In (Select top 1 t2.part_date ...DESC)'.

When I click OK to this message it then highlights the entire Select statement.

What am I doing wrong?

 

[PHV]

And what about this ?
SELECT part_id,part_name,part_date
FROM yourTable T1
WHERE T1.part_date = (SELECT Max(T2.part_date)
FROM yourTable T2 WHERE T2.part_id=T1.part_id)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Doogles]

Here's another possibility for you. It's very similar to Lothario's suggestion:

select t1.part_id, t1.part_name, t1.part_date
from yourtable t1
WHERE t1.part_date = ( Select max(t2.part_date)
from yourtable t2 Where t2.part_id = t1.part_id )

Hope this helps!