Find the last record in a group?

  • Thread starter Thread starter Ken Eisman
  • Start date Start date
K

Ken Eisman

This is probably real simple and I'm just overlooking it. Here is a sample
of my data:

Acct# Year Balance
1001 2001 100.00
1001 2002 200.00
1001 2003 300.00
1002 2001 101.00
1002 2002 102.00

What I need to do is get the latest year's balance for each account (i.e.
the balance of 300.00 for year 2003 in account 1001 and the balance of
102.00 for the year 2002 in account 1002).

Thanks for any help you can provide.

Ken Eisman
(e-mail address removed)
 
You might try a query whose SQL looks something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Year] =
(SELECT
Max([Self].[Year])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Acct#] = [Your Table].[Acct#])

See

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

for a discussion of this and other possible approaches.
 
Back
Top