By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,414 Members | 2,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,414 IT Pros & Developers. It's quick & easy.

Setting conditional formatting on the top values in a series of columns in a single record

P: n/a
I am working on a sales report where I show weekly sales by category
for each of the 52 weeks in the year. Each record in my table/report
has the 52 weeks of sales in it. I want to highlight to top five weeks
of sales for each category by using a conditonal format to highlight
these top five weeks. I've been trying to find a way to do this but
have not been sucessful looking through many books on Access and in
reading the user groups. If anyone has any suggestions, I would be
very happy to hear them.

Chuck
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In the report's OnOpen procedure I'd run a separate query, on the same
data, that used the TOP 5 statement in the SELECT clause & ORDER BY
<numeric value field> DESC. I'd save the "bottom" of the top 5 values
in a report variable and in the conditional formatting function I'd
compare the saved value to the current record value:

If current_value >= saved_value Then highlight record

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX4AmIechKqOuFEgEQLiuQCbBYrO2X2jZcWhFf2eyNUc/69lQlEAn3u5
qpaJQ6Xez79rreAvfzALcV7D
=FzNW
-----END PGP SIGNATURE-----
Chuck Reed wrote:
I am working on a sales report where I show weekly sales by category
for each of the 52 weeks in the year. Each record in my table/report
has the 52 weeks of sales in it. I want to highlight to top five weeks
of sales for each category by using a conditonal format to highlight
these top five weeks. I've been trying to find a way to do this but
have not been sucessful looking through many books on Access and in
reading the user groups. If anyone has any suggestions, I would be
very happy to hear them.


Nov 13 '05 #2

P: n/a
Thanx for your post. I understand the logic of how to make the
conditional formatting work for me from your example. That will work
great. However, what I don't understand how to do is to run a Top 5 on
a table or query where the data by week is all in the same record
rather than in individual records. Below is the query that I'm working
off of:

SELECT Top25_2003.Store, Top25_2003.Description, Top25_2003.Sales,
Top25_2003.[1], Top25_2003.[2], Top25_2003.[3], Top25_2003.[4],
Top25_2003.[5], Top25_2003.[6], Top25_2003.[7], Top25_2003.[8],
Top25_2003.[9], Top25_2003.[10], Top25_2003.[11], Top25_2003.[12],
Top25_2003.[13], Top25_2003.[14], Top25_2003.[15], Top25_2003.[16],
Top25_2003.[17], Top25_2003.[18], Top25_2003.[19], Top25_2003.[20],
Top25_2003.[21], Top25_2003.[22], Top25_2003.[23], Top25_2003.[24],
Top25_2003.[25], Top25_2003.[26], Top25_2003.[27], Top25_2003.[28],
Top25_2003.[29], Top25_2003.[30]
FROM Top25_2003
WHERE (((Top25_2003.Store)=getstore()))
ORDER BY Top25_2003.Store, Top25_2003.Sales DESC;

What I'm wanting to do is within the same record, identify the top 5
weeks of the 30 weeks displayed in one record. I understand how to use
the top values if the weeks were in individual records (vertically in
a table). But I do not know how to identify the same type of top value
logic with in a series of columns in the same record.

In my case, I want to highlight the the five highest sales weeks in
the series of columns Top25_2003.[1] through Top25_2003.[30]. If you
have any ideas on how to do that, I would be very appreciative to
receive such help.

Chuck

MGFoster <me@privacy.com> wrote in message news:<4h*****************@newsread1.news.pas.earth link.net>...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In the report's OnOpen procedure I'd run a separate query, on the same
data, that used the TOP 5 statement in the SELECT clause & ORDER BY
<numeric value field> DESC. I'd save the "bottom" of the top 5 values
in a report variable and in the conditional formatting function I'd
compare the saved value to the current record value:

If current_value >= saved_value Then highlight record

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX4AmIechKqOuFEgEQLiuQCbBYrO2X2jZcWhFf2eyNUc/69lQlEAn3u5
qpaJQ6Xez79rreAvfzALcV7D
=FzNW
-----END PGP SIGNATURE-----
Chuck Reed wrote:
I am working on a sales report where I show weekly sales by category
for each of the 52 weeks in the year. Each record in my table/report
has the 52 weeks of sales in it. I want to highlight to top five weeks
of sales for each category by using a conditonal format to highlight
these top five weeks. I've been trying to find a way to do this but
have not been sucessful looking through many books on Access and in
reading the user groups. If anyone has any suggestions, I would be
very happy to hear them.

Nov 13 '05 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Looks like you've got a table in "spreadsheet mode," or the data source
"Top25_2003" is a cross-tab query. Not what I was expecting when I gave
my solution, which means a more complicated solution.

In this new case I'd put something in the Detail section OnPrint event
procedure (you are printing each record in the Detail section, aren't
you?) that'd find the top 5 values in fields [1] thru [30]. Then get
the minimum value of those top 5. Then go thru the fields again, this
time comparing each field value to the minimum of the top 5 values. If
any of the field values is less-than or equal to the minimum of the top
5 values I'd highlight that field, probably using the control's
BackgroundColor or FontWeight properties.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYE02YechKqOuFEgEQK0ogCffWb2RqlngwQe5L2JwABuP3 GVl18AnRns
EJXZ2cyBfY1Jjrsufpfj59Fz
=QCcr
-----END PGP SIGNATURE-----
Chuck Reed wrote:
Thanx for your post. I understand the logic of how to make the
conditional formatting work for me from your example. That will work
great. However, what I don't understand how to do is to run a Top 5 on
a table or query where the data by week is all in the same record
rather than in individual records. Below is the query that I'm working
off of:

SELECT Top25_2003.Store, Top25_2003.Description, Top25_2003.Sales,
Top25_2003.[1], Top25_2003.[2], Top25_2003.[3], Top25_2003.[4],
Top25_2003.[5], Top25_2003.[6], Top25_2003.[7], Top25_2003.[8],
Top25_2003.[9], Top25_2003.[10], Top25_2003.[11], Top25_2003.[12],
Top25_2003.[13], Top25_2003.[14], Top25_2003.[15], Top25_2003.[16],
Top25_2003.[17], Top25_2003.[18], Top25_2003.[19], Top25_2003.[20],
Top25_2003.[21], Top25_2003.[22], Top25_2003.[23], Top25_2003.[24],
Top25_2003.[25], Top25_2003.[26], Top25_2003.[27], Top25_2003.[28],
Top25_2003.[29], Top25_2003.[30]
FROM Top25_2003
WHERE (((Top25_2003.Store)=getstore()))
ORDER BY Top25_2003.Store, Top25_2003.Sales DESC;

What I'm wanting to do is within the same record, identify the top 5
weeks of the 30 weeks displayed in one record. I understand how to use
the top values if the weeks were in individual records (vertically in
a table). But I do not know how to identify the same type of top value
logic with in a series of columns in the same record.

In my case, I want to highlight the the five highest sales weeks in
the series of columns Top25_2003.[1] through Top25_2003.[30]. If you
have any ideas on how to do that, I would be very appreciative to
receive such help.

Chuck

< SNIP PREVIOUS POSTS >

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.