Count IDS But include All Data

[tms05]

I have a query that lists each person then each persons department. Some can be in two or three departments. I need it to do a running count in the query. So for example,
Name Date Deparment Department Cnt
John Smith 01/01/03 #65 1
Susan Howl 01/15/99 #36 1
Susan Howl 12/15/00 #25 2
Susan Howl 04/18/02 #15 3

As it is now it counts every row as one because I have the group by on all fields. If I don't have the group by my department number doesn't show up and I need the number to show.

Any help would be so appreciated!!!!

 

[CMooreJr]

Bring department over twice in your query. have it set to group by on one, and turn on your totals and have the second one SUM....make sense?

 

[swampBoogie]

select nameColumn, dateColumn, department,
 (select count(*) from q
   where nameColumn = q1.nameColumn
     and dateColumn <= q1.dateColumn ) as DepartmentCnt
  from q as q1
 order by nameColumn,dateColumn

 

[tms05]

It can't sum department because it is a text field. It still is only putting a 1 across each line instead of doing a running sum.