Crosstab Query Help Wanted

[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.

[weekend00]

I don't think a crosstab can do it. you need VBA codes to scan the table.

[goodguy]

Thanks, weekend00. Any idea for a complete newbie?

[weekend00]

Finally, I think I got the solution, but please modify the field names according to your table:

SELECT a.ID, Max(a.inTime) AS inTime, b.outTime
FROM [SELECT Table2.ID, Table2.dTime AS inTime, Table2.IO FROM Table2 WHERE (((Table2.IO)="0")) ]. AS a INNER JOIN [SELECT Table2.ID, Table2.dTime AS outTime, Table2.IO FROM Table2 WHERE (((Table2.IO)="1"))]. AS b ON a.ID = b.ID
WHERE (((a.inTime)<[b].[outtime]))
GROUP BY a.ID, b.outTime;

[goodguy]

Weekend00, thanks again for the fast reply.
OK, this is what I ended up with (I had to omit the Max part as it was pairing across diverse dates):
SELECT DateValue(b.outTime) as CheckDate, a.USERID,
a.inTime, b.outTime, (b.outTime - inTime)*1440 as DURATION
FROM [SELECT USERID, CHECKTIME AS inTime, CHECKTYPE FROM CHECKINOUT WHERE (((CHECKTYPE)="0")) ]. AS a
INNER JOIN
[SELECT USERID, CHECKTIME AS outTime, CHECKTYPE FROM CHECKINOUT WHERE (((CHECKTYPE)="1"))]. AS b
ON a.USERID = b.USERID
WHERE (((a.inTime)<[b].[outtime]))
GROUP BY a.USERID, a.inTime, b.outTime;

Now, please help me (1) format the duration part as ShortTime and (2) introduce startdate and enddate parameters.

FYI, I have been playing around with crosstab query options and this is the best I could come up with:
TRANSFORM First(CHECKINOUT.CHECKTIME) AS FirstOfCHECKTIME
SELECT DateValue([CHECKINOUT.CHECKTIME]) AS CHECKDATE, DEPARTMENTS.DEPTNAME, USERINFO.Name
FROM DEPARTMENTS INNER JOIN (USERINFO INNER JOIN CHECKINOUT ON USERINFO.USERID = CHECKINOUT.USERID) ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID
WHERE (((DateValue([CHECKINOUT.CHECKTIME]))>#11/30/2010#) AND ((CHECKINOUT.CHECKTYPE)="0")) OR (((DateValue([CHECKINOUT.CHECKTIME]))>#11/30/2010#) AND ((CHECKINOUT.CHECKTYPE)="1"))
GROUP BY DateValue([CHECKINOUT.CHECKTIME]), DEPARTMENTS.DEPTNAME, USERINFO.Name
ORDER BY DateValue([CHECKINOUT.CHECKTIME]), DEPARTMENTS.DEPTNAME, USERINFO.Name
PIVOT CHECKINOUT.CHECKTYPE;

This query contains columns from two other related tables. It is the best so far I can get, but again, I am unable to finalize the same two issues stated above.

Any help will be appreciated.

[weekend00]

if you get rid of the MAX(), it won't work for those people who enter more than once.

1. you may use format(duration,"hh:mm:ss") to format duration in the report.
2. just add conditions in where clause:
SELECT DateValue(b.outTime) as CheckDate, a.USERID,
a.inTime, b.outTime, (b.outTime - inTime)*1440 as DURATION
FROM [SELECT USERID, CHECKTIME AS inTime, CHECKTYPE FROM CHECKINOUT WHERE (((CHECKTYPE)="0")) ]. AS a
INNER JOIN
[SELECT USERID, CHECKTIME AS outTime, CHECKTYPE FROM CHECKINOUT WHERE (((CHECKTYPE)="1"))]. AS b
ON a.USERID = b.USERID
WHERE (((a.inTime)<[b].[outtime])) and a.intime >=STARTDATE and a.intime<ENDDATE+1
GROUP BY a.USERID, a.inTime, b.outTime;

[goodguy]

Weekend00, if you check my original post, I need to see each individual step-in/step-out pair, even if there were more than one in a single day. Also, I am facing 2 peculiar problems:
1. my crosstab query is not accepting StartDate and EndDate parameters.
2. my report is not permitting me to display a sum of all job durations.

[weekend00]

I knew you want every entry pair, didn't you find that if you remove the MAX, you will get 3 pairs when actually 2 pairs, and maybe 6 pairs when actually 3.

I tried you crosstab query, but I got only one entry pair for each userid each day. did you get all entry pairs?

[goodguy]

No, I just cross-checked and can see that I am getting a single row per user per day. After auditing my data against required results, I found that only yours gives the correct results. So, I threw out the crosstab query and adapted yours. Thanks a lot mate.

[weekend00]

glad that you worked it out.

[goodguy]

:-). Cheers.

[goodguy]

Sorry, I need some more help:
1. The db I have modified is actually on the server and is accessed via the vendor's app. The new query and report are on that mdb, but the users need to view that report and work on it. One way is to create a blank mdb on client PCs and import the requisite tables, create the query and report and give users an option to access that report.

Is there any other way I can call up the report and display it for users?

2. I have two parameters, StartDate and EndDate, on my query. I want to give users two options to call up the report, one with the normal parameters and the other where the report takes the StartDate as the 1st of the current month and the EndDate as the date today. Short of making two underlying queries each called by its own report, how can I achieve this with a single query.

The prompt for StartDate should show the 1st of the current month, and for EndDate should show the date today. The user may accept these or overwrite them.

3. How do I code for error-checking that the EndDate is not earlier than the StartDate?

[weekend00]

1 you can import the report and link the tables (do not import table) into user's blank mdb. then user can run the report at any time.

2 you can set a filter in the report when opening it,
2.1 create 2 textboxes in your form: startDate and EndDate
2.2 set default value for these 2 textboxes to: =Date() - Day(Date()) + 1 and =date()
2.2 create a button in your form, button1
2.3 code in button1's cick event:

Private Sub Command1_Click()
    DoCmd.OpenReport "reportName", acViewPreview, , "intime>=#" & startDate & "# and intime< 1+#" & endDate & "#"
End Sub

2.4 remove and a.intime >=STARTDATE and a.intime<ENDDATE+1 from the query
2.5 in report's design view, set filter on.

3 the simplest way is to setup validation rule for the two textbox, e.g. rule for endDate: >=cdate([startDate])
if you also want to set validation rule for startDate: <=cdate([endDate])

the validation message poped by Access is not very friendly. if you want to check those two dates by yourself, you may use your code in the after_update event of the textboxes to check the validation.

[goodguy]

Thanks a million. Will try implementing all that you have suggested; if I get stuck, don't mind if I shout for help again.

[goodguy]

OK, here goes, buddy: I have compacted and compressed my updated db file with the latest enhancements. I'll be grateful if you can take a look at it, and help me on the following:
1. The ReportFilter form 'remembers' the last values provided for the StartDate and EndDate textboxes instead of taking the default values when form loads.
2. 'Duration' is a computed field from the base query, when I try to show its summary totals for report and group levels, I get an error that it is too complex. I want the duration to display in hh:mm format at record level and hhh:mm format for summary levels, where hhh means hours total in 3 digits. How can I do it?
3. Although I have defined my report to group records by CheckDate and then Department, the CheckDate group header is grouping by month instead of day, and it is repeating the CheckDate column in the detail section.
4. Dates are getting messed up once I started using the filter form. E.g. when I specify 01/12/2010 to 21/12/2010, I see Janury 2010 records included. Why?

All help truly appreciated.