Tuesday, November 12, 2013

A challenge - Finding the last value in query

Peter Henreid writes

     Until recently, I have never been able to get the LAST function to work within a GroupBy and I need this a lot.  It returns random results, it seems no matter how the primary key is structured.  I always have to use a tri-level query because the sub-queries are too slow.  Yesterday, I just decided there must be a solution to it somewhere so I googled probably for the 10th time over the years – same articles, use tri-level query (one level to find the record that contains the max, next level to retrieve the desired field value, then join this query into the main query… a pain), but then in the very small print of one post, without explanation, I found this link.  As far as I can tell so far, it works and is just darn clever (not all that intuitive unless you really think about it).

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

And here is the second article that I ever wrote on consolidation queries for a bit more background on group by queries.  Garry Robinson
 
 

No comments: