top id by date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mel - 08 Dec 2005 03:47 GMT

Hey does anyone know how to get the say top 1 by id with the hightest date,
for example:
date name id
22/11/5 anna 1
1/1/4 john 2
5/5/4 sasha 3
4/8/5 anna 1
6/9/5 John 2
4/8/5 john 2

the result should display:
22/11/5 anna 1
5/5/4 sasha 3
6/9/5 John 2

displaying all the id by the top date. does any one know how to do this?

Reply to this Message

Van T. Dinh - 08 Dec 2005 04:41 GMT

SELECT Max([Date]), [Name], [ID]
FROM [YouTable]
GROUP BY [Name], [ID]

If you Field names are actually "Date" and "Name", suggest you change them
to something elsr as both are reserved words in Access / VBA.

Signature

HTH
Van T. Dinh
MVP (Access)

> Hey does anyone know how to get the say top 1 by id with the hightest
> date,
[quoted text clipped - 13 lines]
>
> displaying all the id by the top date. does any one know how to do this?

Reply to this Message

mel - 08 Dec 2005 06:26 GMT

Hey thanks,

but i got another problem, it has price, when i add another column with
price in it, it shows everything. I should have mention this before? is there
a way to get around it!

> SELECT Max([Date]), [Name], [ID]
> FROM [YouTable]
[quoted text clipped - 20 lines]
> >
> > displaying all the id by the top date. does any one know how to do this?

Reply to this Message

Van T. Dinh - 08 Dec 2005 09:46 GMT

Yes, you definitely need to mention that you want to det the values of
related Fields and include this in the sample data.

See The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm

for different techniques.

Signature

HTH
Van T. Dinh
MVP (Access)

> Hey thanks,
>
[quoted text clipped - 29 lines]
>> > displaying all the id by the top date. does any one know how to do
>> > this?

Reply to this Message