UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Highlight Top 5 Values Of List On A Report, Access 2016    
 
   
ArmyHRguy
post Oct 7 2019, 09:21 AM
Post#1



Posts: 7
Joined: 7-October 19



Greetings,

I am new to UA, so please bare with me. I hope I can appeal to some patriotic nature in here smile.gif

I am in the US Army and trying to modify my DB to meet my bosses' desires.

I have a SP list which consists of appx 150 rows. There are 11 columns. The columns are ORGANIZATION, COUNTRY, LOCATION and the remaining 7 columns are numbers which represent a number of items for each.

e.g.

ORG 1 // USA // NY // 100 // 134 // 35 // 23 // 18 // 12 // 94 // 18
ORG 2 // USA // NY // 50 // 123 // 13 and so on.

I have an Access front end to create reports/queries and etc. This particular report displays data, grouped by Country. So, if there are 7 lines for one country, it aggregates and displays as one line.

I would like to be able to highlight, on this report, the top 5 highest totals for all countries. I can use conditional formatting to highlight the single text box, but because the numbers change constantly, I will have to keep changing the conditions. Likewise, it doesn't highlight the whole row (I think I can figure this out, but any help is greatly appreciated).

I am not able to share the DB because of classification issues, but I would be willing to get in detail as possible.

Thanks for your help!

Go to the top of the page
 
GroverParkGeorge
post Oct 7 2019, 09:26 AM
Post#2


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


First, welcome to UtterAccess.

As is too often the case, trying to simplify by removing details makes it harder to guess what is really going on.

What are those 7 columns? If you can't give the actual names, can you at least give us a hint about their contents? I strongly suspect this is what we often call a "spreadsheet style" table because it has multiple columns, each of which represents one of a series of similar things. Read these blog articles on the problem of repeated columns. If this applies to your table, we can help fix that problem, but we need to know more about it to be sure.
This post has been edited by GroverParkGeorge: Oct 7 2019, 09:26 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Oct 7 2019, 09:43 AM
Post#3


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


Is my guess that these 7 columns might be ranks (pvt, spc, sgt, etc.) close?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
BruceM
post Oct 7 2019, 10:22 AM
Post#4


UtterAccess VIP
Posts: 7,985
Joined: 24-May 10
From: Downeast Maine


I share the concerns about the other columns, but won't get into that here.

In reference to your specific question about highlighting, one way to go about this is to set the control source of a text box (I will call it txtCount) to:
=1

Set the txtCount Running Sum property to Over All (or maybe Over Group if you are doing grouping on the report). This is a good way of numbering the records, if that is your intent.

Set the back style to Normal for txtCount, and to transparent for all other controls that are to be highlighted. Make txtCount the full width of the row. With it still selected, in the Report Design Tools group of tabs, select the Arrange tab, then select Send to back in the Sizing & Ordering group. Then apply conditional formatting if the value is <6. If the numbering is not to be seen, apply the same formatting to the background and the text. If the numbering is to be seen, don't apply condtional formatting to the text.
Go to the top of the page
 
arnelgp
post Oct 7 2019, 10:28 AM
Post#5



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


you add another calculated field (sumOfSeven) on your query that will Sum() the
7 columns.

add an Unbound textbox to your report. Set its Visible property to No, so it will not show in the report.
when the report opens, add code to it's Load event to get the top 5 from the query:
CODE
option compare database
option explicit

private sub report_Load()
dim strTopFive as string
with currentdb.openrecordset("select top 5 (organization & "|" & country & "|" & location) as Expr1 fom yourQueryName order by sumOfSeven desc;")
    if not (.bof and .eof) then .movefirst
    strTopFive = "|"
    while not .eof
        strTopFive = strTopFive & .fields(0) & "|"
        .movenext
    wend
end with
strTopFive = replace(strTopFive, "||", "|")
me.Unboundtextbox = strTopFive
end sub


your Conditional Format:
CODE
Expression Is: Instr([UnboundTextbox], "|"  [organization] & "|" & [country] & "|" location & "|") > 0


--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
projecttoday
post Oct 7 2019, 10:59 AM
Post#6


UtterAccess VIP
Posts: 11,176
Joined: 10-February 04
From: South Charleston, WV


If you're using a report, you should be able to do this with a variable in the code. Define your counter at the top of the code as an integer or long. Set it to 0 in the on format event of the countries group. Add 1 to it in the on format event of the details section. Then test it and set the highlighting.

--------------------
Robert Crouser
Go to the top of the page
 
ArmyHRguy
post Oct 8 2019, 02:21 AM
Post#7



Posts: 7
Joined: 7-October 19



George,

thank you for your willingness to help, as well as all the other fine folks who replied!

The other seven columns are numbers of personnel (PAX), by Service. So here are my columns: Unit, Country, Location, #USA; #USAF; #USN; #USMC; #USCG; (CalcCol)#USMIL; #DoDCIV; #CTR; #Other; (calccol)#nonUSMIL; (calccol) #Total -- i guess I left out the CalCol on my initial question, but arnelgp had mentioned creating a calculated column. I just want to everyone who replied to know it exists.

It is absolutely a spreadsheet style table. in order for my subordinate organizations to submit their numbers, I created a SharePoint list and use that as the "Table". They can log in from their location(s), update the "table" and my office uses a MS Access "Front end" which allows us to manipulate the data for our needs.

Also, the data isn't necessarily classified-- it's the DB and that's ONLY because it's on a separate network. I can't move it to an un-secure network. I could, probably, re-create one if necessary.

I really appreciate everyone's help!

Regards
Josh
Go to the top of the page
 
projecttoday
post Oct 8 2019, 06:24 AM
Post#8


UtterAccess VIP
Posts: 11,176
Joined: 10-February 04
From: South Charleston, WV


Sample. Open the report in Print Preview. I did this fast, so if it's not what you're looking for I apologize.
Attached File(s)
Attached File  HighlightFirst5.zip ( 22.93K )Number of downloads: 5
 

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Oct 8 2019, 08:26 AM
Post#9


UtterAccess VIP
Posts: 7,985
Joined: 24-May 10
From: Downeast Maine


With sincere thanks to Robert for putting together a sample database, I shamelessly appropriated it to demonstrate a non-code version, as I described in an earlier posting. It is in the report with "linecount" at the end of the name.
Attached File(s)
Attached File  HighlightFirst5_V2.zip ( 29.59K )Number of downloads: 1
 
Go to the top of the page
 
ArmyHRguy
post Oct 8 2019, 09:36 AM
Post#10



Posts: 7
Joined: 7-October 19



Robert (aka projecttoday)

This is fantastic! I just want to say that when it comes to code, I am not even playing the same sport, let alone in the same ball-park as you (and everyone who has been helping)... so thanks so much.

If it's not too much trouble, I made some mods to the table to more accurately represent what my table looks like and the report I am trying to achieve.

It's very nearly there-- but after I made some mods, I noticed that it is highlighting more than top 3 of all records in table and it's not highlighting the 3 highest numbers. That is to say, in the DB attached, it is not selecting the top 3 values, of all records in the table, with the highest number in the field (txtsummil)

Regards,
Josh
Attached File(s)
Attached File  HighlightFirst5__2_.zip ( 41.26K )Number of downloads: 5
 
Go to the top of the page
 
BruceM
post Oct 8 2019, 09:48 AM
Post#11


UtterAccess VIP
Posts: 7,985
Joined: 24-May 10
From: Downeast Maine


Did you order (sort) the report on that field?

Edit: I see you did not. In Sorting and Grouping, add a sort on the field.
Go to the top of the page
 
projecttoday
post Oct 8 2019, 10:57 AM
Post#12


UtterAccess VIP
Posts: 11,176
Joined: 10-February 04
From: South Charleston, WV


You're welcome. It highlights the first 3 that print. If you want to highlight the top 3 values regardless of where they are in the report, this won't work. So not very nearly there, I'm afraid.
I think arnelgp's code flags the top x in the load event. You might want to give that a try.
There are (at least) 2 other ways to approach this. The method that I am partial to is a temporary table. Another way is with a ranking query. If you google "ms access ranking query" you will find lots of examples.

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Oct 8 2019, 11:38 AM
Post#13


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


Here's my take on normalizing the data and using a crosstab to populate the report.

I suppose that this is one of those cases where we would compromise on table design because of external requirements. The fact that it has a very limited purpose and context helps.Attached File  HighlightFirst5.zip ( 32.2K )Number of downloads: 3

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ArmyHRguy
post Oct 9 2019, 08:49 AM
Post#14



Posts: 7
Joined: 7-October 19



I do appreciate all the help. Unfortunately I am getting no where. I can ID the top 5 in a query, I can sort the report so highest values are on top and I can set conditional formatting to highlight based on a consistent range (between # and #), but I still cannot get the report to:

Make txt[Country Total] RED with WHITE FONT if value of txt[Country Total] is TOP 5 of all records in tbl/qry

Unfortunately, I am not classically trained in SQL or Access. I'm self taught.

arnelgp's code seems to have no effect on the DB, either the mock up that we've been using or the one I am trying to modify. I am probably doing something wrong.

GroverParkGeorge's version puts the numbers in desc order, but still highlights first 3 from each group and would require a significant "overhaul" of the existing DB. While that's possible, it would take me a lot of time and I don't know that I have a very big window.

I researched MS Access Ranking Query and I can do it, but I'm still not see the application.

If anyone would still like to assist, or would like to help me off-line, I can provide my email or call you (if this is even allowed)

Kind Regards,
Josh
Go to the top of the page
 
GroverParkGeorge
post Oct 9 2019, 09:02 AM
Post#15


UA Admin
Posts: 36,023
Joined: 20-June 02
From: Newcastle, WA


As I noted above, I would normally urge normalization more forcefully, but your specific situation--a SharePoint list that is exclusively used for this one narrow purpose--might be an exception. As a SharePoint list, it is possible for many people to update their specific data points fairly easily this way. And you can create that single required report, albeit with some complexity.



--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Oct 9 2019, 09:10 AM
Post#16


UtterAccess VIP
Posts: 11,176
Joined: 10-February 04
From: South Charleston, WV


Josh,

This post is a situation exactly like yours. You want to rank by a value and within a group. I think the query near the end is what you want. You can ignore the last post in the thread. Once your data come out with the rankings you set your report to highlight if the rank is <= 5. You should be able to use conditional formatting for that. Or code. Sorry I don't have time to do this myself.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Oct 9 2019, 09:12 AM
Post#17


UtterAccess VIP
Posts: 11,176
Joined: 10-February 04
From: South Charleston, WV


Is this a Sharepoint list? Did I miss something?

--------------------
Robert Crouser
Go to the top of the page
 
ArmyHRguy
post Oct 9 2019, 09:14 AM
Post#18



Posts: 7
Joined: 7-October 19



Yes, the table is a SharePoint list and I use MS Access to analyze/present the data.
Go to the top of the page
 
projecttoday
post Oct 9 2019, 09:17 AM
Post#19


UtterAccess VIP
Posts: 11,176
Joined: 10-February 04
From: South Charleston, WV


I'm sorry. I don't know anything about Sharepoint, so maybe I'm out on this one.

--------------------
Robert Crouser
Go to the top of the page
 
ArmyHRguy
post Oct 9 2019, 09:23 AM
Post#20



Posts: 7
Joined: 7-October 19



SharePoint list just replaces the MS Access table. MS Access operates the same, just reaches to SP site for data from the table.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2019 - 04:47 PM