Running Sum Access Query

[sandylou]

I have search the posts and can't seem to find a solution to the following:

I have just and ID and I want to create a query to automatically sequence these ids.

ID SEQUENCe
2 1
2 2
2 3
2 4
4 1
6 1
6 2
8 1

Does anyone know how this can be done in an Access query.

I can do this

 SEQ: ((Select Count(SYS_ID) from tmpQRY_empl R Where R.SYS_ID =  tmpQRY_empl.SYS_ID))

but when I try and add anything else to it, my value becomes 0. I am at a loss right now. any help?

 

[PHV]

What is the SQL code of tmpQRY_empl ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sandylou]

it is just a simple select from an employee table.

 

[sandylou]

nevermind i figured out how to do it..

 

[PHV]

And how did you it ?

 

[sandylou]

I couldn't do it by using just the one column - so a post from a previous search for "running sum access" that was provided helped me solve it. I added the employee and did:

SELECT A.SYS_ID, Count(B.EMPL_ID) AS SEQUENCE_NUMBER, A.STAT, A.EMPL_ID, A.START_DT, A.END_DT
FROM EMPL AS A INNER JOIN EMPL AS B ON (A.SYS_ID = B.SYS_ID) AND (A.EMPL_id <= B.EMPL_id)
GROUP BY A.SYS_ID, A.STAT, A.EMPL_id, A.START_DT,END_DT
ORDER BY A.SYS_ID, Count(b.EMPL_id)

 

[PHV]

Well, this SQL seems familiar to me ...