Time Of Day range Cross Tab Report

[Guest]

Hello,

I have a table of Jobs (tblJobs) and want to be able to create a Report
where for each date to Count the Total number of Jobs for each Time Period.
Example:

Date 12-1PM 1-2PM 2-3PM .....
1/1/06 3 2 5
1/2/06 5 15 1
1/3/06 7 8 2
....


I tried using a Cross Tab Query but that gives me each column for each time.
I need to Count when the time is between hours.

Is their a Query that can produce these results or does how is this done in
a Report.

Any help would be greatly apprecaited.

Thank you,
Jeff

 

[John Spencer]

You should be able to do this with a crosstab. You just need to use the
HOUR function against the time field.

Suggestion. Post the SQL of the crosstab and someone can edit it to show
you the exact modification needed.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

 

[Guest]

Hi John,

Thank you for your response.

That sounds like a good idea but what if for example there are no Jobs that
are from 1-2PM, I still would like that column to appear but just have a
Count of 0.
If you use a Cross Tab query it only Counts based on the Times it finds.

Example:

Date 12-1PM 1-2PM 2-3PM .....
1/1/06 3 0 5
1/2/06 5 0 1
1/3/06 7 0 2

Any other ideas ?

Thank you,
Jeff

 

[John Spencer]

In the crosstab query you can specify the field name(s) using an In clause
in the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("01","02", ...,"12")

In the query grid, you do this by
double clicking on the grey area at the top
Inputting your values in Column Headings separated by commas

The values in the query will be null, not zero. But you can control that
with formatting or by using the NZ function in the transform statement.

 

[Guest]

Thank you John,

Exactly what I was looking for !

Thnx,
Jeff