Crostab query for Summary report

[elena]

Hi, All
How can i accomplish following task to create summary report?
Report has following fields like badge and time fields: time on the field
will be always the same.

Badge 9:00-12:00 12:00-15:00 15:00-18:00
101 2 0 0
102 1 1 0
_______________________________________

I have table with fields:
Badge TicketNo Date Time
101 000002 2/2/09 9:00
101 000003 2/2/09 9:30
102 000001 2/2/09 11:00
102 000002 2/2/09 13:00

Please, advice

 

[Michel Walsh]

since

CDate(int(now*8)/8)

return the start of the 3 hours period to which belong the date_time value,
such as now,


TRANSFORM Nz(COUNT(*))
SELECT badge
FROM tableNameHere
GROUP BY badge
PIVOT Format(CDate( INT(dateTimeField * 8) /8 ), "yyyy.mmm.dd hh:nn" )



should return what you want, but only with the starting date_time:



Badge 2/2/09 9:00 2/2/09 12:00 2/2/09 15:00
101 2 0 0
102 1 1 0



You can add 3 hours to get the final time, such as modifying the PIVOT
clause into:



PIVOT CDate( INT(dateTimeField * 8) /8 ) & " to " & Format( #03:00:00#
+ CDate( INT(dateTimeField * 8) /8 ) , "hh:nn")





ah, note the magical value 8 is because there is 8 periods of 3 hours per
day.



Vanderghast, Access MVP

 

[elena]

Thank you Michel for such detailed explainations.
It works great.