calculate range between like records

[bowldog]

ID Name
21 jones
22 smith
23 arnold
24 michael
25 johnson
26 davis
27 jones

I would like to count the number of records since jones last appeared.(6)

Thanks in advance for your help.

 

[PHV]

SELECT Count(*)
FROM yourTable WHERE ID>(SELECT Max(ID) FROM yourTable WHERE [Name]='jones');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bowldog]

I would like to add a new field that would indicate the range the count between all records not just 'jones'. Total records 2500+. Sorry for not making myself clearer.
Thanks for any help

 

[PHV]

???

 

[bowldog]

ID Name Range
21 jones ?
22 smith ?
23 arnold ?
24 michael ?
25 johnson ?
26 davis ?
27 jones 6
28 arnold 5
29 jones 2
30 smith 8
31 ellis ?

 

[PHV]

Provided that ID is sequential without gaps:
SELECT A.ID, A.Name,A.ID-Nz(Max(B.ID),A.ID) AS Range
FROM tblNames AS A LEFT JOIN tblNames AS B ON A.ID>B.ID AND A.Name=B.Name
GROUP BY A.ID, A.Name;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bowldog]

Works great! a little slow but the results came out fine. Thanks again for your time.