Last 3 Records from a Group

[rpboll]

I am looking for a query that accounts for the last three of any group
by date. If I have a data sample as follows:

Unit Value Date
a 10 09/18/2005
a 12 10/17/2005
a 14 11/11/2005
a 20 11/21/2005
a 19 12/23/2005
b 19 10/14/2005
b 14 12/17/2005
b 18 02/10/2006
b 20 03/13/2006
b 21 03/29/2006

Results (last three dates average)
Unit Value
b 20
a 18

Any help with this greatly appreciated!
RPBOLL

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
for 4 ways to approach this.

Note that you will need a primary key on this table, so that Access is able
to distinguish which are the top 3 for any unit (in case there are duplicate
dates.)

 

[rpboll]

What about a query that only displays the last three entries by date.
So instead of

Unit Value Date
a 10 09/18/2005
a 12 10/17/2005
a 14 11/11/2005
a 20 11/21/2005
a 19 12/23/2005
b 19 10/14/2005
b 14 12/17/2005
b 18 02/10/2006
b 20 03/13/2006
b 21 03/29/2006

We see:

Unit Value Date
a 14 11/11/2005
a 20 11/21/2005
a 19 12/23/2005
b 18 02/10/2006
b 20 03/13/2006
b 21 03/29/2006

Where it only displays the last three entries for each unit.

What I have so far is:

SELECT L.Unit, L.DATE, L.Value
FROM Sheet1 AS L
WHERE (((L.DATE) In (select top 3 L.DATE
from
Sheet1 M
WHERE
M.unit= L.unit
ORDER BY L.unit, L.Date)));



But this isn't working. Thanks for any help!

RPBollin

 

[John Spencer]

Very Close.Most of the confusion was in the subquery where you were
referencing the wrong instance of Sheet1

SELECT L.Unit, L.DATE, L.Value
FROM Sheet1 AS L
WHERE L.DATE In
(SELECT Top 3 M.DATE
FROM
Sheet1 As M
WHERE
M.unit= L.unit
ORDER BY M.Date DESC)