Grouped query - last function , how is working ?

[Guest]

Dear All,

Please help ,

I have the next table with recording sequence

Id date Info
a 2007.09.01. a first
a 2007.09.30. a third
a 2007.09.15. a second
b 2007.09.30. b second
b 2007.09.01. b first

I need only two records , the last one of Id=a and Id=b in the latest time
Id date Info
a 2007.09.30. a third
b 2007.09.30. b second

I have made a grouped query by Id, and sorted by Date, last Info ,
but the result is always according to the original table sequence like
Id LastOfdate LastOfInfo
a 2007.09.15. a second
b 2007.09.01. b first

In sql view is:
SELECT Track.Id, Last(Track.date) AS LastOfdate, Last(Track.Info) AS
LastOfInfo
FROM Track
GROUP BY Track.Id
ORDER BY Track.Id, Last(Track.date);

It seems to be a really simple thing ... and I can not solve this ...


Thanks in advance for the answers

Imre

 

[Michel Walsh]

You can use one of the four techniques described at
http://www.mvps.org/access/queries/qry0020.htm

Note that LAST does NOT mean latest. And you cannot GROUP BY on an
aggregate.

If you want to do it in two queries, make the first one:

SELECT ld, MAX([date]) AS maxDate
FROM tableNameHere
GROUP BY ld


save it as q1.

Make a second query:

SELECT q1.ld, q1.maxDate, info
FROM q1 INNER JOIN yourTableName AS b
ON q1.ld=b.ld AND q1.maxDate = b.[date]


Call that second query to get your result.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Ahhh... Michel ,

It is working now


Many thanks for your help.

Imre

You can use one of the four techniques described at
http://www.mvps.org/access/queries/qry0020.htm

Note that LAST does NOT mean latest. And you cannot GROUP BY on an
aggregate.

If you want to do it in two queries, make the first one:

SELECT ld, MAX([date]) AS maxDate
FROM tableNameHere
GROUP BY ld


save it as q1.

Make a second query:

SELECT q1.ld, q1.maxDate, info
FROM q1 INNER JOIN yourTableName AS b
ON q1.ld=b.ld AND q1.maxDate = b.[date]


Call that second query to get your result.



Hoping it may help,
Vanderghast, Access MVP

Dear All,

Please help ,

I have the next table with recording sequence

Id date Info
a 2007.09.01. a first
a 2007.09.30. a third
a 2007.09.15. a second
b 2007.09.30. b second
b 2007.09.01. b first

I need only two records , the last one of Id=a and Id=b in the latest time
Id date Info
a 2007.09.30. a third
b 2007.09.30. b second

I have made a grouped query by Id, and sorted by Date, last Info ,
but the result is always according to the original table sequence like
Id LastOfdate LastOfInfo
a 2007.09.15. a second
b 2007.09.01. b first

In sql view is:
SELECT Track.Id, Last(Track.date) AS LastOfdate, Last(Track.Info) AS
LastOfInfo
FROM Track
GROUP BY Track.Id
ORDER BY Track.Id, Last(Track.date);

It seems to be a really simple thing ... and I can not solve this ...


Thanks in advance for the answers

Imre