Query results: number incrementally 1

[lowtek]

Hello,

I have been trying to think of a way to build a query that returns results and then numbers each result incrementally for the number of times the result is the same.

For example, if I have two columns named "Employee#" and "Incident Date" and the results return employee number "1111" on three separate records--(each incident having a different incident date)--I want a third column (incident#) in the result set to number each instance of employee 1111 from 1 to 3 incrementally by date ascending, so:

(from table) (from table) (what I want!)
employee# incident date incident#
1111 02/12/2008 1
1111 05/27/2008 2
1111 10/04/2008 3
2222 08/14/2008 1
etc...

Hopefully this can be done...thank you in advance for any help,

Rey

 

[PHV]

SELECT A.[employee#], A.[incident date], Count(*) AS [incident#]
FROM yourTable AS A INNER JOIN yourTable AS B ON A.[employee#]=B.[employee#] AND A.[incident date]>=B.[incident date]
GROUP BY A.[employee#], A.[incident date]

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

 

[lowtek]

yes!! that did it...very nice!
Thank you so much for the help,
Rey