By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,293 Members | 1,399 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,293 IT Pros & Developers. It's quick & easy.

Highest Record?

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
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.

Nov 12 '05 #3

P: n/a
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.
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.