Help with query on Audit Trail

[Kries]

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-41435439 2-1-2007 16:49 Incomplete In Progress
1-41435439 3-1-2007 16:53 In Progress 2nd Line
1-41435439 8-2-2007 10:04 2nd Line Closed
1-41435439 14-2-2007 8:41 Closed In Progress
1-41435440 14-2-2007 16:35 In Progress 2nd Line
1-41435441 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.