Compare Two types of records from one table

[pstrahan]

Hello,

I am trying to create a query based on a transaction table which compares the time between two types of transactions.



The initial record would have "A' in the type field and the second record would have "D" in the type field.

each pair of A and D records would be matched on o_time and idnum fields, and I would want to pull each pair and us the datediff function on c_time, using records marked with A as the intitial time and records marked with D as the followup time.

Is there any way to accomplish this in a single query or do I need to create two subqueries?

[rpeare]

Yes you can.

For the purposes of this example:

The table I set up is Tbl_Test
The fields were
IDNum (assuming you are only going to have one A record and one D record for each ID Num)
Transtype (A, D or anything else, text field)
O_Time (starting time)
C_Time (ending time)

this query will do what you want:

SELECT Tbl_Test.IDNum, Max(IIf([transtype]="A",[O_Time],Null)) AS StartTime, Max(IIf([transtype]="D",[C_Time],Null)) AS EndTime, DateDiff("s",[starttime],[endtime]) AS Expr1
FROM Tbl_Test
GROUP BY Tbl_Test.IDNum;