Counting time series records

  • Thread starter Thread starter Donagh Berry
  • Start date Start date
D

Donagh Berry

Hi all,
Say I have two columns as follows

Individual age
A 3
A 9
A 12
B 2
B 7
C 23
D 11

Does anyone know a short method of creating another column which
includes the number of preceeding records of the same individual i.e.,


Individual age count
A 3 1
A 9 2
A 12 3
B 2 1
B 7 2
C 23 1
D 11 1

Bear in mind that the number of records per individual may be large so
I rather not have to repeat running queries which I currently do to
achieve this.

Thanks in advance
Donagh
 
Hi,



SELECT a.Individual, a.age, COUNT(*)

FROM myTable As a INNER JOIN myTable As b
ON a.individual=b.individual AND a.age>= b.age

GROUP BY a.Individual, a.age




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top