Cross tab query - group hours (1 Viewer)

[tezread]

I have a table with

ID
Time (hh:mm)
Hospital

Is there a way I can set up a cross tab query that counts the number of episodes for each hospital between the hours of

00:00 - 00:59
01:00 - 01:59


??

Cheers in advance

 

[vbaInet]

* In the Select Query, create a column to return the respective range for each field. Hint: Use IIF() and Format()
* Change to Crosstab query and count based on the new Range field. Obviously don't call the field Range

 

[tezread]

* In the Select Query, create a column to return the respective range for each field. Hint: Use IIF() and Format()
* Change to Crosstab query and count based on the new Range field. Obviously don't call the field Range

Funny that - I thought about doing it that way but that means an awfully long IIF statement doesn't it? - I would have to criteria for all 24hr

Hourofday: IIf([Expr1] Between '00:00' And '00:59',"0","other")

that is just for 1 hour

 

[tezread]

Hourofday: IIf([Expr1] Between '00:00' And '00:59',"0",IIf([Expr1] Between '01:00' And '01:59',"1","other"))

for testing 2 hour groups

 

[vbaInet]

You didn't say if you are doing it for the entire 24 hours? From your first post I thought you had only two cases?

 

[tezread]

No - I need it for 24 hours - sorry - should have said in my example

Hourofday: IIf([Expr1] Between '00:00' And '00:59',"0",
IIf([Expr1] Between '01:00' And '01:59',"1",
IIf([Expr1] Between '02:00' And '02:59',"2",
IIf([Expr1] Between '03:00' And '03:59',"3",
IIf([Expr1] Between '04:00' And '04:59',"4",
IIf([Expr1] Between '05:00' And '05:59',"5",
IIf([Expr1] Between '06:00' And '06:59',"6",
IIf([Expr1] Between '07:00' And '07:59',"7",
IIf([Expr1] Between '08:00' And '08:59',"8",
IIf([Expr1] Between '09:00' And '09:59',"9",
IIf([Expr1] Between '10:00' And '10:59',"10",
IIf([Expr1] Between '11:00' And '11:59',"11",
IIf([Expr1] Between '12:00' And '12:59',"12",
IIf([Expr1] Between '13:00' And '13:59',"13",
IIf([Expr1] Between '14:00' And '14:59',"14",
IIf([Expr1] Between '15:00' And '15:59',"15",
IIf([Expr1] Between '16:00' And '16:59',"16",
IIf([Expr1] Between '17:00' And '17:59',"17",
IIf([Expr1] Between '18:00' And '18:59',"18",
IIf([Expr1] Between '19:00' And '19:59',"19",
IIf([Expr1] Between '20:00' And '20:59',"20",
IIf([Expr1] Between '21:00' And '21:59',"21",
IIf([Expr1] Between '22:00' And '22:59',"22",
IIf([Expr1] Between '23:00' And '23:59',"23","Other"))))))))))))))))))))))))

this is too long a criteria

 

[vbaInet]

It sure is tez

You can create a Function and use a SELECT case statement in the function

 

[tezread]

by the way vbainet - i noitice you are really active on this forum - do you get any remuneration? it is deserved!

 

[vbaInet]

Probably too active

Nope, no renumeration... just free advice.

 

[spikepl]

Base the range-column on the hour. Extract the hour of the post , and that's it.

 

[vbaInet]

Base the range-column on the hour. Extract the hour of the post , and that's it.

The question is how to get the range. Range is not an existing column in the table.

 

[spikepl]

Huh? Hourofday, as per your suggestion. Instead of all that IIF etc

If you look at the IIF's in #6, that's a lot of code to get to the number which in fact already is there in the hour-part of the record.


BTW: Do not use Time as column name. That's a resreved word.

 

[vbaInet]

I know what you mean, but he needs the Column headings to be a range, as in "00:00 - 00:59" for example.

 

[vbaInet]

If you look at the IIF's in #6, that's a lot of code to get to the number which in fact already is there in the hour-part of the record.

Alright, I get your point. Good eyes!

 

[vbaInet]

By the way tezread, that was not the impression I got from your intial post. Is this what you're after?

You can use the HOUR() function.

 

[tezread]

deleted deleted

 

[spikepl]

Okay. Then what's this with all the ifs? We can construct a string based on the hour and use that for the "rannge" field for the crosstab.

If Time=00:37, then hour is 0, then it's simple to make a string "00:00-00:59"

Format(Datepart("h", [Time]), "00") & ":00-" & Format(Datepart("h", [Time]), "00") &":59" or something like that.

Or just use Hour instead of datepart , DUUUUUUUUH

 

[tezread]

By the way tezread, that was not the impression I got from your intial post. Is this what you're after?

You can use the HOUR() function.

I see - that would work! I use Hour() instead of format()??

 

[tezread]

Got it! here is the crosstab for others to learbn from

TRANSFORM Count(qryFAST.ID) AS CountOfID
SELECT qryFAST.HourOfCall, Count(qryFAST.ID) AS [Total Of ID]
FROM qryFAST
GROUP BY qryFAST.HourOfCall
PIVOT qryFAST.[Hospital Attended];

 

[tezread]

many thanks guys