Sort or Conditional Formatting based on =Count(*)

[djfuego]

Hello,



I have a report that displays the number of jobs a person has done based on the rows counted that equal a field name as well as that persons name. I have tried to find a simple expression that weeds out the Highest row count and Moves this to the top of the report list or changes the colour based on the Highest value.
The Report group header is based on the name field so I don't have a long list of the same names just one name per row.
The Text box just has the following

=[fullname] & " Number of jobs worked" & " (" & Count(*) & " " & IIf(Count(*)=1,"Job"," Jobs") & ")"

So how can I make it check that it's the highest value and modify it thus?

Ps: I've tried making another text field with the code

=Count(*)

So it only gives the number of jobs and then trying to work out just from a simple value but still have the same problem.

[bcofie]

Try this,

Open report in design view.Right click the "WhatEverTextBox" that displays the count and select Conditional Formatting. Click New Rule and choose "Expression is" from the drop down of Condition 1. In the adjacent text box type IsMax([WhatEverTextBox]) and the choose a 'Fill/Back Color" from the dropdown of colors.Choose whichever color you want.

I hope this helps.
Ben

[djfuego]

I just get a dialog box now asking for a IsMax value. Could it be that the value hasn't populated in the box and that is why it's asking? I need it to make it blatently obvious what the maximum value is.

[bcofie]

Sorry, I think there is no "IsMax" inspection function. I I was don't know what I was thinking. This page http://www.everythingaccess.com/tuto...st()-functions has a code for max and min.
Once you have a function in place you can use it to inspect for the Max in a column.

[bcofie]

Try this work around to achieve inspection for max;
Choose expression is and enter
([WhatEverTextBox])=Max([WhatEverTextBox])

OR

Choose Value is, choose equal to, and enter Max([WhatEverTextBox])

Don't leave out the parenthesis/square brackets

[djfuego]

Have you tried to replicate this as If I create 2 count text boxes that get the same count and compare them ([Text18])=Max([Text18]) I tried with a single text box and copy of the text box with the count in it too. Any more ideas?

[bcofie]

Mya be I am misunderstanding you. Could you post a sample of data, in excel 2007 or Access 2007, then I will create a report or form that highlights the highest count in a column.
I have Office 2007 at home but I use 2010 at work so for me to be able to work on it at home I need a sample in 2007.

[bcofie]

Dj,

See attached.
Add some more data to the table so that "John Smith" is not the one with the highest count of Jobs done.Double click on form "frm_JobsDone" to see who is highlighted. I used a form not a report 'cos its nicer to me.

The form was created using Wizard and its based off the query "qry_JobsDone"--i.e. the record source of the form is the query listed above. You can use Wizard to create a report in same way.

If this is not what you want, let me know. What I have attached is my understanding of your orignal post, so if its not right give me a clearer picture.

Ben

[djfuego]

That's a form not a report. So I have to generate a form to get the information? Also can I group by jobs done from each Person? I wanted to have the job name and then the persons name next to the job count on how many jobs they did.

[djfuego]

I did it! My query wasn't quite right. I can now sort by count decending based on the number of fullname duplicates.The report still doesnt highlight the Max number but Its showing the person with the most jobs at the top of the list.

[bcofie]

see the attached. I am at work, so this was done in 2010. I did more than 1-way.

Cheers,
Ben

[djfuego]

I have no Idea how you managed to get it to work but I still can't get it to work for me. I see you have created a SQL query as well now. and as if by magic it's now highlighting.. I had to pick the field from the list in the expression creator bit. It's now highlighting too (probably my bad typo).. Great! Thanks very much for your help!