Top Values & Grouping

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I hope someone can help?

Have a Notes table that has a unique id (Autonumber), Account number, notes
field
and datetime field.

I would like to be able to retrieve the top 3 most recent notes for each
customer

Many thanks

Colin
 
Here's the gist of what I think you want:

replace "yurtable" and field names
with your actual table name/field names...

SELECT
T.ID,
T.AccountNum,
T.Notes,
T.AccDate
FROM
yurtable As T
WHERE
T.ID IN
(
SELECT TOP 3 q.ID
FROM
yurtable AS q
WHERE
q.AccountNum = T.AccountNum
ORDER BY
q.AccDate DESC,
q.ID
)
ORDER BY
T.AccountNum,
T.AccDate DESC;
 
Back
Top