Re: Help with query on Audit Trail

In Access 2000 you could do

select a.SR, sum(a.duration) as SumOfDuration
from
(
select m1.SR, datediff("d",m1.DateTime,
(
select min(m2.DateTime) from myTable as m2
where m2.SR = m1.SR
and m2.DateTime > m1.DateTime
)) as duration
from myTable as m1
where m1.NewValue = '2nd Line'
) as a
group by a.SR



krieshen@xxxxxxxxx wrote:
Hi,

The entity I work with has a 'lifetime' as follows:
Visualized*:

Start:2-1-2007 16:49 End:6-3-2007 11:34
|--------------|--------------|------------|-------------|-----------|--------------|
Incomp In progr 2nd Clsd In progr 2nd Clsd

*note: when you see the data table, you'll understand this better

If I split these phases you get;
I am only interested int he 2nd line phases:

8-2-2007 14-2-2007
|-------------------|
2nd Line Closed |--------------------|
2nd Line Closed

I have the following table:
SR# DateTime OldValue NewValue
1-414 2-1-2007 16:49 Incomplete In Progress
1-414 3-1-2007 16:53 In Progress 2nd Line
1-414 8-2-2007 10:04 2nd Line Closed
1-414 14-2-2007 8:41 Closed In Progress
1-414 14-2-2007 16:35 In Progress 2nd Line
1-414 6-3-2007 11:34 2nd Line Closed

From this table I would like to query the duration of the 2nd Line
status.
The way I have tried it is:
I have:
1. queried all records with NewValue 2nd Line renaming the DateTime
field to
DateTimeNew
2. queried all records with OldValue 2nd Line renaming the DateTime
field to
DateTimeOld
3. created a new query based on the queries of step 1 and 2. In this
query I
have linked the other two queries on SR# and taken the
DateDiff("d";[DateTimeOld];[DateTimeNew]) where there are 2nd Line
values.

The query is not adequate, it only works when there is only one value
of 2nd
Line in the column OldValue or NewValue, i.e. the concerning entity
was only
once in the 2nd Line and twice as represented in the table data
above.

I have split the DateTime field in Date and Time...
As you can see, the 1st and last record are actually incorrect.

The output of the query is:
SR# DaySpentTimeSpent DateNew DateOld TimeNew TimeOld
1-414 -6 6:31 14-2-2007 8-2-2007 16:35:00 10:04:00
1-414 20 5:01 14-2-2007 6-3-2007 16:35:00 11:34:00
1-414 36 6:49 3-1-2007 8-2-2007 16:53:00 10:04:00
1-414 62 5:19 3-1-2007 6-3-2007 16:53:00 11:34:00

Any help on this would be greatly appreciated.

Thanks,
K.

.

Relevant Pages

  • Help with query on Audit Trail
    ... queried all records with NewValue 2nd Line renaming the DateTime ... created a new query based on the queries of step 1 and 2. ... Line in the column OldValue or NewValue, ...
    (comp.databases.ms-access)
  • Re: User-defined functions in where clause
    ... function expression directly into your queries. ... datetime), and you should see an improvement. ... >UDF is used to row by row process similar how cursors work. ... >>Run this query in query analyzer with the server trace option on and you ...
    (microsoft.public.sqlserver.programming)
  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)