December 15, 2010 at 1:20 pm
Hi, all. This is not a SQL Server issue but Access 2007.
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply