conditional formatting a crosstab

[Howard]

Hi,
I have a crosstab report that shows timeslots (1 to 5) as the columns and
days of the week (mon to fri) as the rows. The cells contain the count of an
incident at each time slot / day. These will have a range from zero up to an
unknown maximum (but generally less than 20).

I'd like to make it visually useful by colouring the cells showing the
lowest 25% of the range green and the highest 25% of the range red so that
it is eay to see where most incidents occur

I've played around with max() on the report to try to find out the range but
to no avail. I guess I'll have to put some code in the onFormat event but
not sure what

Anyone any ideas?

Howard

 

[Duane Hookom]

You need to help us out. Do your "ranges" go across a row or across a column?

 

[Howard]

Thanks for a speed reply Duane, Sorry it was unclear.

This is the query supplying the report

TRANSFORM nz(Count([exitsAndPeriods].[AdNum]),0) AS CountOfAdNum
SELECT exitsAndPeriods.Wkday, Count(exitsAndPeriods.AdNum) AS [Total Of
AdNum]
FROM exitsAndPeriods
GROUP BY exitsAndPeriods.Wkday
PIVOT exitsAndPeriods.period In (1,2,3,4,5);


Adnum: identifies a person involved in an incident - counting these provides
my cell value
Wkday: is the day of the week (Monday to Friday) that the incident occured
period: is the time slot in a day (1-5) when an incident could occur

There may be no incidents all week in a particular period so I force zeros
with NZ() and force five periods to be shown as columns with IN (1,2,3,4,5)

the report looks like this

Day | Total |P1 P2 P3 P4 P5
Monday | 9 |1 2 5 1 0
Tuesday | 14 |2 10 0 0 2
Thursday | 15 |1 2 0 9 3

in which case I'd like to format the 10 and 9 red and the 0,1 &2 green
(approx top and bottom 25%)

(Incidentally there may be no incidents at all on a particular day all in
which case I'd still like that day shown as a row with all zeros in the
columns but can't solve that one either! any help here would be appreciated)


Howard

 

[Duane Hookom]

I don't think this would be an easy task. Conditional formatting the single
highest and single lowest could be much easier as would values above or below
a specific number.

In order for all days to show up, you would need to have a table (or derived
query) that contains ALL days of the week. You could then perform a join to
your crosstab that includes all records from all days of the week.


--
Duane Hookom
Microsoft Access MVP

Thanks for a speed reply Duane, Sorry it was unclear.

This is the query supplying the report

TRANSFORM nz(Count([exitsAndPeriods].[AdNum]),0) AS CountOfAdNum
SELECT exitsAndPeriods.Wkday, Count(exitsAndPeriods.AdNum) AS [Total Of
AdNum]
FROM exitsAndPeriods
GROUP BY exitsAndPeriods.Wkday
PIVOT exitsAndPeriods.period In (1,2,3,4,5);


Adnum: identifies a person involved in an incident - counting these provides
my cell value
Wkday: is the day of the week (Monday to Friday) that the incident occured
period: is the time slot in a day (1-5) when an incident could occur

There may be no incidents all week in a particular period so I force zeros
with NZ() and force five periods to be shown as columns with IN (1,2,3,4,5)

the report looks like this

Day | Total |P1 P2 P3 P4 P5
Monday | 9 |1 2 5 1 0
Tuesday | 14 |2 10 0 0 2
Thursday | 15 |1 2 0 9 3

in which case I'd like to format the 10 and 9 red and the 0,1 &2 green
(approx top and bottom 25%)

(Incidentally there may be no incidents at all on a particular day all in
which case I'd still like that day shown as a row with all zeros in the
columns but can't solve that one either! any help here would be appreciated)


Howard

 

[Howard]

OK thanks, I thought it might involve something like using on format to
gather all the values into a sorted array so that I could find the quartiles
and then format accordingly. Maybe a bit OTT for this application. Perhaps
I'll just tell the user to look closely at the numbers!

Thanks for the help

Howard

I don't think this would be an easy task. Conditional formatting the single
highest and single lowest could be much easier as would values above or
below
a specific number.

In order for all days to show up, you would need to have a table (or
derived
query) that contains ALL days of the week. You could then perform a join
to
your crosstab that includes all records from all days of the week.


--
Duane Hookom
Microsoft Access MVP

Thanks for a speed reply Duane, Sorry it was unclear.

This is the query supplying the report

TRANSFORM nz(Count([exitsAndPeriods].[AdNum]),0) AS CountOfAdNum
SELECT exitsAndPeriods.Wkday, Count(exitsAndPeriods.AdNum) AS [Total Of
AdNum]
FROM exitsAndPeriods
GROUP BY exitsAndPeriods.Wkday
PIVOT exitsAndPeriods.period In (1,2,3,4,5);


Adnum: identifies a person involved in an incident - counting these
provides
my cell value
Wkday: is the day of the week (Monday to Friday) that the incident
occured
period: is the time slot in a day (1-5) when an incident could occur

There may be no incidents all week in a particular period so I force
zeros
with NZ() and force five periods to be shown as columns with IN
(1,2,3,4,5)

the report looks like this

Day | Total |P1 P2 P3 P4 P5
Monday | 9 |1 2 5 1 0
Tuesday | 14 |2 10 0 0 2
Thursday | 15 |1 2 0 9 3

in which case I'd like to format the 10 and 9 red and the 0,1 &2 green
(approx top and bottom 25%)

(Incidentally there may be no incidents at all on a particular day all in
which case I'd still like that day shown as a row with all zeros in the
columns but can't solve that one either! any help here would be
appreciated)


Howard

You need to help us out. Do your "ranges" go across a row or across a
column?
--
Duane Hookom
Microsoft Access MVP


:

Hi,
I have a crosstab report that shows timeslots (1 to 5) as the columns
and
days of the week (mon to fri) as the rows. The cells contain the count
of
an
incident at each time slot / day. These will have a range from zero up
to
an
unknown maximum (but generally less than 20).

I'd like to make it visually useful by colouring the cells showing the
lowest 25% of the range green and the highest 25% of the range red so
that
it is eay to see where most incidents occur

I've played around with max() on the report to try to find out the
range
but
to no avail. I guess I'll have to put some code in the onFormat event
but
not sure what

Anyone any ideas?

Howard

 

[Duane Hookom]

You would need to create recordset or whatever based on your non-crosstabbed
data since you need to reference every "cell" value. It could be done by
simply allowing your user to store/enter a couple values in a table to
compare against.

<reading between the lines> I'm either too busy or lazy to create and post a
dynamic solution at this time.</reading between the lines>
--
Duane Hookom
Microsoft Access MVP

OK thanks, I thought it might involve something like using on format to
gather all the values into a sorted array so that I could find the quartiles
and then format accordingly. Maybe a bit OTT for this application. Perhaps
I'll just tell the user to look closely at the numbers!

Thanks for the help

Howard

I don't think this would be an easy task. Conditional formatting the single
highest and single lowest could be much easier as would values above or
below
a specific number.

In order for all days to show up, you would need to have a table (or
derived
query) that contains ALL days of the week. You could then perform a join
to
your crosstab that includes all records from all days of the week.


--
Duane Hookom
Microsoft Access MVP

Thanks for a speed reply Duane, Sorry it was unclear.

This is the query supplying the report

TRANSFORM nz(Count([exitsAndPeriods].[AdNum]),0) AS CountOfAdNum
SELECT exitsAndPeriods.Wkday, Count(exitsAndPeriods.AdNum) AS [Total Of
AdNum]
FROM exitsAndPeriods
GROUP BY exitsAndPeriods.Wkday
PIVOT exitsAndPeriods.period In (1,2,3,4,5);


Adnum: identifies a person involved in an incident - counting these
provides
my cell value
Wkday: is the day of the week (Monday to Friday) that the incident
occured
period: is the time slot in a day (1-5) when an incident could occur

There may be no incidents all week in a particular period so I force
zeros
with NZ() and force five periods to be shown as columns with IN
(1,2,3,4,5)

the report looks like this

Day | Total |P1 P2 P3 P4 P5
Monday | 9 |1 2 5 1 0
Tuesday | 14 |2 10 0 0 2
Thursday | 15 |1 2 0 9 3

in which case I'd like to format the 10 and 9 red and the 0,1 &2 green
(approx top and bottom 25%)

(Incidentally there may be no incidents at all on a particular day all in
which case I'd still like that day shown as a row with all zeros in the
columns but can't solve that one either! any help here would be
appreciated)


Howard

You need to help us out. Do your "ranges" go across a row or across a
column?
--
Duane Hookom
Microsoft Access MVP


:

Hi,
I have a crosstab report that shows timeslots (1 to 5) as the columns
and
days of the week (mon to fri) as the rows. The cells contain the count
of
an
incident at each time slot / day. These will have a range from zero up
to
an
unknown maximum (but generally less than 20).

I'd like to make it visually useful by colouring the cells showing the
lowest 25% of the range green and the highest 25% of the range red so
that
it is eay to see where most incidents occur

I've played around with max() on the report to try to find out the
range
but
to no avail. I guess I'll have to put some code in the onFormat event
but
not sure what

Anyone any ideas?

Howard