query help with multiple entries in a log

[Guest]

I have a database set up to track help desk tickets. The table for the
tickets is called HD_Tickets. There is a 'log' table that keeps track of all
the changes that were made to the tickets (status, notes, etc..) called
HD_Log which is linked to the HD_tickets by the field Ticket_ID. I want to
calculate how long each ticket was in a certain status. The status field is
on both the HD_Tickets (for current status) and the HD_LOG (for all the
changes). So if my status' are A, B, C, and D how can I calculate the total
time the ticket spent in each status?

example:
Status Date Changed
A Nov 1st 10am
A Nov 1st 12pm
B Nov 2nd 5pm
C Nov 2nd 6pm
A Nov 3rd 3am
D Nov 5th 6pm
B Nov 6th 10am

I need to know the total amount of time this ticket spent in status A, B, C
and D.

Thanks

 

[Dale Fye]

Patricia,

The following should do the job for you. What this does is use the DMIN()
function to find the minimum value of Date_Changed that is greater that the
current value for each record. If there is no final record, it uses the
Now() function to indicate that it is still in that status. I then use the
datediff function to compute the number of minutes between each state
change, and then sum those up by Ticket Number and Status.

SELECT L.Ticket_Num, L.Status,
Sum(DateDiff("n",[Date_Changed],NZ(DMin("Date_Changed","HD_LOG","HD_Log.Tick
et_Num = " & [L].[Ticket_Num] & " AND HD_Log.Date_Changed > #" &
[L].[Date_Changed] & "#"),Now()))) AS Duration
FROM HD_LOG AS L
GROUP BY L.Ticket_Num, L.Status;

HTH
Dale