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]

Re: Highest Record?

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

--
Brendan Reynolds


"Peter D" <dauwe.peter@pa ndora.be> wrote in message
news:2fe8deb6.0 402160729.61a36 cf@posting.goog le.com...[color=blue]
> 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.[/color]

[Mike Storr]

Re: Highest Record?

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

https://www.veraccess.com

"Peter D" <dauwe.peter@pa ndora.be> wrote in message
news:2fe8deb6.0 402160729.61a36 cf@posting.goog le.com...[color=blue]
> 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.[/color]

[Mark Reed]

Re: Highest Record?

try this,
You will need to query's:

Query 1:

SELECT ur_table_name.s hipnumber, Max(ur_table_na me.[date of activation
ship]) AS [date of activation ship]
FROM ur_table_name
GROUP BY ur_table_name.s hipnumber;

Query 2:

SELECT Query1.shipnumb er, 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.shipnumb er);


Mark

"Peter D" <dauwe.peter@pa ndora.be> wrote in message
news:2fe8deb6.0 402160729.61a36 cf@posting.goog le.com...
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.