Re: Difference in days from multiple records

From: Bill Taylor (wtaylor51537_at_earthlink.net)
Date: 04/29/04 

Date: Thu, 29 Apr 2004 19:47:47 GMT

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
"Fredrick Smith" <anonymous@discussions.microsoft.com> wrote in message
news:D6864FF3-CF26-4A52-8467-442D8270547F@microsoft.com...
> 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:
>
> Assigned TO ActDoneDate Days
> Howard 3/9/2004 0
> Crawford 3/9/2004 0
> Smith 3/9/2004 7
> Paramore 3/2/2004 1
> Howard 3/1/2004 21
> Aliyy 2/9/2004 115
> Moultrie 10/17/2003 396
> Walker 9/16/2002 201
> Green 2/27/2002 22
> Crum 2/5/2002 25
> Green 1/11/2002 24
> Dessommes 12/18/2001 40
> Gordy 11/8/2001 2
> Moultrie 11/6/2001
>
> Can anyone help out with this?

Relevant Pages

  • Re: sub query count
    ... I tried to to inner join but it does not work like following ... FROM tblDetails D INNER JOIN tbl_Employees D ... I call it ActivityDetails which means is a child of Employee ... One employee can have different meetings on the same date. ...
    (microsoft.public.access.queries)
  • Re: Paul Thomas hears, but doesnt get it
    ... > the view of any particular employee - that is, ... > assuming that the matching contributions are intended to vest in at least ... > the power under a plan that is in fact qualified at the time of election, ... > the separate limitations on the maximum elective deferral amount). ...
    (misc.taxes)
  • Re: Subqueries and LAST function
    ... FROM (HalfYearSales INNER JOIN Dept ON HalfYearSales.Dept_ID = Dept.Dept_ID) ... INNER JOIN Employee ON HalfYearSales.Employee_ID = Employee.Employee_ID ... >Here is the View in SQL Server 2000 which doesn't give me what I want. ...
    (microsoft.public.sqlserver.programming)
  • Re: SELECT in a JOIN query results in Syntax Error in FROM clause
    ... By not optimal database design the field for employeeID in tblArbCtr reads ... As for the second solution, perhaps you should add the contracts table ... FROM tblEmp INNER JOIN tblContracts ... record for each employee ID ...
    (microsoft.public.access.queries)
  • Re: Re:The lawyers statement regarding my tax-on-nothing
    ... > In other words, if I can paraphrase, the VCR assumed that the amount by ... > amount contributed by the employee rather than by the employer (i.e., if, ... > income for the year of distribution). ...
    (misc.taxes)