Getting time into crosstab?

[indyaries]

Greetings,

Management wants to see who is doing what type of input in a given period of time.

I've gotten the information saved to Excel, which returns the timestamp that the employee input a certain document into the system (document types can be PO, INV, RR, MOD).

I've managed to split the General Date field into a Date field and a Time field (short date-short time).

Employee 1 could have entries as such:

VE DateofWork TimeofWork Work
Empl1 03/23/03 07:00 PO
Empl1 03/23/03 07:20 RR
Empl1 03/23/03 07:55 RR
Empl1 03/23/03 08:23 INV
Empl1 03/23/03 15:12 PO
Empl2 03/23/03 06:45 RR
Empl2 03/23/03 07:16 INV
Empl2 03/23/03 08:55 PO
.....and so on

I need to show each employee's input times and the Work performed by the hour!

I need to somehow relate all work performed between a certain time into something simple for a timeframe. Hence, all work performed between 07:00 and 07:59 would be something like 07, work between 08:00 and 08:59 = 08, and so on, out to at least 18:00, which would be 18. I also need to show/count what work was input.

For the times, I had thought of adding an extra field to my table, and use an update query for each hour (06:00 to 06:59 UPDATE to 06). I would need to have an update query for each hour I wanted to track (6AM - 6PM)

I had thought of using another table holding the time values, something like this?
StartTime EndTime Value
06:00 06:59 06
07:00 07:59 07
14:00 14:59 14
.....and so on.

Unfortunately, I don't know how I would use this table to link to the main table. I know that I can use the Update queries, but was looking for a more efficient method, if one exists.

Thanks in advance,

Bob in Indy
(Access 97 at work, Access 2000 at home)

 

[PauloRico]

Try wrapping your Date/Time field in a Format$ function - i.e

Format$([YourDateTimeField],"HH&quot

This will give you the hour value of your field and can be used as the Column Heading in your cross-tab.