Crosstab Query Help

[goodguy]

I have a table CHECKINOUT which contains three main columns, USERID (number), CHECKTIME (date/time), and CHECKTYPE (text), which contains sample data as below (CSV format):
1, 2010-12-01 08:12:11, I.
2, 2010-12-01 08:33:25, I.
1. 2010-12-01 09:25:45, 0.
2. 2010-12-01 11:15:15, 0.
1. 2010-12-01 14:33:55, 1.
2. 2010-12-01 15:11:22, 1.
2, 2010-12-01 15:35:44, 0.
2. 2010-12-01 16:22:33, 1.
1, 2010-12-01 17:44:01, O.
2, 2010-12-01 18:02:37, O.

CHECKTYPE 'I' and 'O' indicate normal clocking in and out respectively, while '0' and '1' indicate clocking out and back in for field jobs.

I want to write a report based on a crosstab query to return records for field jobs and the time taken as follows:
Date: 2010-12-01
USERID OUT IN DURATION
1 09:25:45 14:33:55 05:08:10
2 11:15:15 15:11:22 03:56:07.
2 15:35:44 16:22:33 00:56:49.

How can I write a crosstab query that will:
1. Filter for CHECKTYPES '0' and '1'.
2. Group records by date only (not date/time).
3. Pair step-out records with step-in records by user and date.

Any help will be appreciated.

 

[vbaInet]

Welcome to the forum!

You don't need a crosstab query for this. A select query should do just fine.

1. Create a query and filter out the OUTs so you're left with INs.
2. Drop the UserID and IN fields in the query
3. For the Out field you need an alias field which will be something like:
AliasOut: (SELECT TOP 1 Q.CheckTime FROM TableName AS Q WHERE Q.UserID = TableName.UserID AND Q.CheckTime > TableName.CheckTime ORDER BY Q.CheckTime)
4. For the Duration field you need a function. See this:
http://office.microsoft.com/en-us/access-help/on-time-and-how-much-has-elapsed-HA001110218.aspx

 

[goodguy]

Thanks, vbaInet.

 

[vbaInet]

You're welcome!

 

[MStef]

Comment only.
The last pair (15:35:44, 16:22:33) is duration 00:46:49, (not 00:56:49).

 

[goodguy]

My mistake, this was aircoded.