473,834 Members | 2,009 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
3 2321
-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQX4AmIechKq OuFEgEQLiuQCbBY rO2X2jZcWhFf2ey NUc/69lQlEAn3u5
qpaJQ6Xez79rreA vfzALcV7D
=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
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.Stor e, Top25_2003.Desc ription, Top25_2003.Sale s,
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.S tore)=getstore( )))
ORDER BY Top25_2003.Stor e, Top25_2003.Sale s 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******* **********@news read1.news.pas. earthlink.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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQX4AmIechKq OuFEgEQLiuQCbBY rO2X2jZcWhFf2ey NUc/69lQlEAn3u5
qpaJQ6Xez79rreA vfzALcV7D
=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
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Looks like you've got a table in "spreadshee t 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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQYE02YechKq OuFEgEQK0ogCffW b2RqlngwQe5L2Jw ABuP3GVl18AnRns
EJXZ2cyBfY1Jjrs ufpfj59Fz
=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.Stor e, Top25_2003.Desc ription, Top25_2003.Sale s,
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.S tore)=getstore( )))
ORDER BY Top25_2003.Stor e, Top25_2003.Sale s 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
8963
by: John Kreps | last post by:
(acc 2002) I've got six unbound text boxes on a subform that has a white background. Each of those six boxes has an expression that when true, will change its background from white to another color. These text boxes need to go behind a single line of other bound text boxes (which show the actual record). I.e., the purpose of the unbound text boxes w/conditional formatting is to highlight the record/background with one of six colors.
3
5942
by: Jouke Langhout | last post by:
Hello all! For quite some time now, I've got the following problem: Access won't close properly when a user closes the application. An ACCESS process stays active and that process can only be terminated by pressing ++ and then terminate the process. I searched the entire internet and found out that there could be two things wrong (both of them are mentioned in the bug list on the access
4
3149
by: Bradley | last post by:
I have an A2000 database in which I have a continuous form with a tick box. There is also a text box with a conditional format that is based on the expression , if it's true then change the background colour. In A2000 it works great, but in A2003 the background doesn't always change and when it does it only changes when the record looses the focus. Any way around this? Is it a bug? Or have they "improved" it?
1
2251
by: WindAndWaves | last post by:
Hey Folks I seem to be asking millions of questions. Truth is, I just discovered this awesome research and having been typing by myself for years the questions are all coming out. Here is another favourite: Can you highlight the current record using conditional formatting.
8
12062
by: Dimitri Furman | last post by:
Given: Access 2002/2003 A subform in datasheet or continuous view, placed on a tab page (this last may or may not matter) Conditional formatting applied to some controls on the subform - format expressions are the same for all controls Under some undetermined circumstances, when such subform is displayed, the controls on the subform start to visibly flicker, the cursor in the subform stops blinking, and CPU utilization goes to 100%....
12
2370
by: dmieluk | last post by:
Problem: When moving between records, I need to control which combo/text boxes are displayed on the current form, dependent upon data in the current record. More: I building my first access database.
2
2356
by: jodyblau | last post by:
I'm not certain that what I am trying to do is possible; in any event I haven't been able to figure it out. Here is what I am trying to do: I have one table that has a list of cases I'm working on. I have a second table that contains a list of tasks to be performed and relates to the Case_List through a Case_ID. So currently, I can bring up a form that has a list of all my cases, I can then select a case and it opens a new form that...
4
8982
by: slinky | last post by:
Thanks in advance... I have a continuous style form with a field for each record called "STATUS". I simply want to have the form load and if the value of the textbox is "Inactive" I want the textbox background to display yellow, else I want it to be just white. Here's my code... but it is not working Private Sub Form_Load() If Me!STATUS = "Inactive" Then Me!STATUS.BackColor = RGB(255, 255, 0) Else
10
9713
by: afromanam | last post by:
Regards, Please help What I'm trying to do is this: (and I can't use reports since I must export to Excel) I export some queries to different tabs in an excel workbook I then loop through each tab and apply autowidth to columns and apply autofilter to the first row of every tab in the workbook. I've this down
0
9651
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10802
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10516
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10557
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7763
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6961
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5630
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4429
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3085
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.