Difference in days from multiple records

[Fredrick Smith]

I am trying to write a enhance one of my queries. My Boss needs a report that will show the amount of days each employee had a case. So I wrote a query to show all the employees, the actions and the dates for each

SELECT To.LastName AS [Assigned TO], A.ActDoneDate FROM tbl_AdminUsers AS [by] INNER JOIN (tbl_AdminUsers AS [To] INNER JOIN tblCaseActivities AS A ON To.UserID = A.AssignedTO) ON by.UserID = A.AssignedB
WHERE (((A.ActDone)=1) AND ((A.CaseID)=[Forms]![frmCaseDetail]![CaseID])
ORDER BY A.ActDoneDate DESC

Which produces thos output

Assigned TO ActDoneDate
Howard 3/9/2004
Crawford 3/9/2004
Smith 3/9/2004
Paramore 3/2/2004
Howard 3/1/2004
Aliyy 2/9/2004
Moultrie 10/17/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/200
Walker 10/13/200
Walker 10/1/200
Walker 6/29/200
Walker 3/11/200
Walker 2/5/200
Walker 2/5/200
Walker 2/4/200
Walker 2/4/200
Walker 2/4/200
Walker 1/30/200
Walker 9/16/200
Green 2/27/200
Crum 2/12/200
Crum 2/11/200
Crum 2/6/200
Crum 2/5/200
Crum 2/5/200
Crum 2/5/200
Crum 2/5/200
Green 1/11/200
Dessommes 12/31/200
Dessommes 12/28/200
Dessommes 12/18/200
Gordy 11/8/200
Moultrie 11/6/200

I would like to filter out a few all of the first duplicates and Ex. (Dessommes will only show the last record) and would also like another column with the amount of days between the previous record as below

Assigned TO ActDoneDate Day
Howard 3/9/2004
Crawford 3/9/2004
Smith 3/9/2004
Paramore 3/2/2004
Howard 3/1/2004 2
Aliyy 2/9/2004 11
Moultrie 10/17/2003 39
Walker 9/16/2002 20
Green 2/27/2002 2
Crum 2/5/2002 2
Green 1/11/2002 2
Dessommes 12/18/2001 4
Gordy 11/8/2001
Moultrie 11/6/2001

Can anyone help out with this?

 

[Bill Taylor]

You will need a date the case is returned. from this date you can us
Rdate(field name for date returned) - ADate( assigned date). this will
return the number of days.
HTH

I am trying to write a enhance one of my queries. My Boss needs a report

that will show the amount of days each employee had a case. So I wrote a
query to show all the employees, the actions and the dates for each:

SELECT To.LastName AS [Assigned TO], A.ActDoneDate FROM tbl_AdminUsers AS

[by] INNER JOIN (tbl_AdminUsers AS [To] INNER JOIN tblCaseActivities AS A ON
To.UserID = A.AssignedTO) ON by.UserID = A.AssignedBY

WHERE (((A.ActDone)=1) AND ((A.CaseID)=[Forms]![frmCaseDetail]![CaseID]))
ORDER BY A.ActDoneDate DESC;

Which produces thos output:

Assigned TO ActDoneDate
Howard 3/9/2004
Crawford 3/9/2004
Smith 3/9/2004
Paramore 3/2/2004
Howard 3/1/2004
Aliyy 2/9/2004
Moultrie 10/17/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/13/2003
Walker 10/1/2003
Walker 6/29/2003
Walker 3/11/2003
Walker 2/5/2003
Walker 2/5/2003
Walker 2/4/2003
Walker 2/4/2003
Walker 2/4/2003
Walker 1/30/2003
Walker 9/16/2002
Green 2/27/2002
Crum 2/12/2002
Crum 2/11/2002
Crum 2/6/2002
Crum 2/5/2002
Crum 2/5/2002
Crum 2/5/2002
Crum 2/5/2002
Green 1/11/2002
Dessommes 12/31/2001
Dessommes 12/28/2001
Dessommes 12/18/2001
Gordy 11/8/2001
Moultrie 11/6/2001

I would like to filter out a few all of the first duplicates and Ex.

(Dessommes will only show the last record) and would also like another
column with the amount of days between the previous record as below: