MS-Access design pattern for last value for a grouping

Question

It's common to have a table where for example the the fields are account, value, and time. What's the best design pattern for retrieving the last value for each account? Unfortunately the last keyword in a grouping gives you the last physical record in the database, not the last record by any sorting. Which means IMHO it should never be used. The two clumsy approaches I use are either a subquery approach or a secondary query to determine the last record, and then joining to the table to find the value. Isn't there a more elegant approach?

Answer 1

The subquery option sounds best to me, something like the following psuedo-sql. It may be possible/necessary to optimize it via a join, that will depend on the capabilities of the SQL engine.

select * 
from table 
where account+time in (select account+max(time) 
                       from table 
                       group by account 
                       order by time)

Answer 2

could you not do:

select account,last(value),max(time)
from table
group by account

I tested this (granted for a very small, almost trivial record set) and it produced proper results.

Edit:

that also doesn't work after some more testing. I did a fair bit of access programming in a past life and feel like there is a way to do what your asking in 1 query, but im drawing a blank at the moment. sorry.

Answer 3

This is a good trick for returning the last record in a table:

SELECT TOP 1 * FROM TableName ORDER BY Time DESC

Check out this site for more info.

Answer 4

@shs yes, that select last(value) SHOULD work, but it doesn't... My understanding although I can't produce an authorative source is that the last(value) gives the last physical record in the access file, which means it could be the first one timewise but the last one physically. So I don't think you should use last(value) for anything other than a really bad random row.

Answer 5

@Tom It might be easier for me in general to do the "In" query that you've suggested. Generally I do something like

select T1.account, T1.value
from table T as T1
where T1 = (select max(T2.time) from table T as T2 where T1.account = T2.Account)

Answer 6

Perhaps the following SQL is clumsy, but it seems to work correctly in Access.

SELECT a.account, a.time, a.value FROM tablename AS a INNER JOIN [ SELECT account, Max(time) AS MaxOftime FROM tablename GROUP BY account ]. AS b ON (a.time = b.MaxOftime) AND (a.account = b.account) ;

Answer 7

I'm trying to find the latest date in a group using the Access 2003 query builder, and ran into the same problem trying to use LAST for a date field. But it looks like using MAX finds the lates date.