Highest Record?

[Peter D]

I have another table with (no id), shipnumber, name of ship, date of
activation ship

Example : 123456 , APOLLO , 01-01-2003
222222 , ZEUS , 01-02-2003
333333 , DIANE 2, 06-06-2003
123456 , MONTOYE V , 06-02-2004

I would like a query that shows me every ship number once and every
ship name once based on the latest date. So in case of 123456 that
would be Montoye V.
But i don't find it.

TX anyone, anywhere, anyway, anyhow, anytime.

[Brendan Reynolds]

SELECT ShipNumber, ShipName, Max([DateField]) AS LastDate FROM TableName
GROUP BY ShipNumber, ShipName

--
Brendan Reynolds
"Peter D" <da*********@pandora.be> wrote in message
news:2f*************************@posting.google.co m...

I have another table with (no id), shipnumber, name of ship, date of
activation ship

Example : 123456 , APOLLO , 01-01-2003
222222 , ZEUS , 01-02-2003
333333 , DIANE 2, 06-06-2003
123456 , MONTOYE V , 06-02-2004

I would like a query that shows me every ship number once and every
ship name once based on the latest date. So in case of 123456 that
would be Montoye V.
But i don't find it.

TX anyone, anywhere, anyway, anyhow, anytime.

[Mike Storr]

Turn on Totals, Set group by on all fields except Date, for this one use
Max. Should return the most recent date for each record "group".

Mike Storr
www.veraccess.com
"Peter D" <da*********@pandora.be> wrote in message
news:2f*************************@posting.google.co m...

I have another table with (no id), shipnumber, name of ship, date of
activation ship

Example : 123456 , APOLLO , 01-01-2003
222222 , ZEUS , 01-02-2003
333333 , DIANE 2, 06-06-2003
123456 , MONTOYE V , 06-02-2004

I would like a query that shows me every ship number once and every
ship name once based on the latest date. So in case of 123456 that
would be Montoye V.
But i don't find it.

TX anyone, anywhere, anyway, anyhow, anytime.

[Mark Reed]

try this,
You will need to query's:

Query 1:

SELECT ur_table_name.shipnumber, Max(ur_table_name.[date of activation
ship]) AS [date of activation ship]
FROM ur_table_name
GROUP BY ur_table_name.shipnumber;

Query 2:

SELECT Query1.shipnumber, ur_table_name.[name of ship], Query1.[date of
activation ship]
FROM ur_table_name INNER JOIN Query1 ON (ur_table_name.[date of activation
ship] = Query1.[date of activation ship]) AND (ur_table_name.shipnumber =
Query1.shipnumber);
Mark

"Peter D" <da*********@pandora.be> wrote in message
news:2f*************************@posting.google.co m...
I have another table with (no id), shipnumber, name of ship, date of
activation ship

Example : 123456 , APOLLO , 01-01-2003
222222 , ZEUS , 01-02-2003
333333 , DIANE 2, 06-06-2003
123456 , MONTOYE V , 06-02-2004

I would like a query that shows me every ship number once and every
ship name once based on the latest date. So in case of 123456 that
would be Montoye V.
But i don't find it.

TX anyone, anywhere, anyway, anyhow, anytime.