Help with crosstab query

• goodguy

  SSCertifiable

  Points: 5456

  More actions

  December 15, 2010 at 1:20 pm

  #388444

  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.

  ---

  Regards,

  goodguy

  Experience is a bad teacher whose exams precede its lessons