I am just in the process of creating a report which gives the weekly breakdown of nationalities in our language school.
I have managed to highlight the highest value each week by using the MAX function, however, I would like the top three nationalities to be highlighted in the same colour, just as can be done in Excel. Is there a way this can be done in Access too?
I am using a crosstab query as the underlying data, and the date fields are in the column headings. The nationality field is a row heading, and sorted in alphabetical order.
It is a quarterly analysis, so there are 13 dates (fields) going across, and for each date I would like to set up a conditional format which highlights the top 3 values for that particular date. Does that make sense? Do you think that's possible at all?
1. Create a query that counts how many pupils there are per nationality. From indication I think you already have this.
2. In another query based on the query in (1), return only two fields CountPerPupil and NationalityID. You probably are already doing this.
3. Join this query to query of your report via the NationalityID and do a LEFT JOIN so it will return ALL records from your main query (i.e. your report's query) and only those records where the query in (2) match.
4. Use conditional formatting to state that if the CountPerPupil field IS NOT NULL or NOT IS NULL (whichever way you prefer) then highlight it with some colour.
I think I sort of see what you mean, but I'm not sure if this will work in my case.
The query in step 1 is a crosstab query, giving me a nationality breakdown for 13 different dates (13 columns), and it looks like this:
country Date1 Date2 Date3 etc...
Austrian
Belarusian
Brazilian 1 2 3
Bulgarian 1
Chinese
Dominica
Ecuadorian
Emirati 1 1
Estonian
French 1 1 22
etc...
In step 2, should I be sorting the Date1 field in descending order, and then only return the top 3 values?
The problem I envisage is that I have 13 different columns in my first query, and if I understand you correctly that might mean having to set up 13 different subqueries.
I've tried to do what you suggested, but I don't think this works for me. Maybe I have misunderstood, but your suggestion would highlight all values where there are students, however, I was looking to only highlight the three highest numbers in each column.
I have managed to conditional format the highest nationality count in each column, by using the Max function in the footer, and then highlighting those numbers in the detail section that match that. (i had to set up a conditional format for each column)
I was wondering if there are other functions that return the 2nd or 3rd highest value, based on which I could then set up more conditional formats.
I guess a way around it could be to work out the average in the footer and then create a conditional format that highlights everyone above the average. That would not necessarily highlight the top three values but anything that is relatively high.
Thanks, I've done that - the problem is although it highlights the top three countries, they are the same for each of my 13 columns (Date). Also, these are the countries with the highest number of students overall, not the highest number of students per week.
I have now worked out the average under each column in the footer of the report and then set a conditional format in each detail field to highlight every number that is above the average. That sometimes gives me more than 3 top scorers, but works well for the purpose of my report.
I would like to highlight the top three scores in each of my column headings of the report's underlying crosstab table (Fieldname: QuarterWk in the uploaded DB. There are 13 in total). In my sample DB I have only set up conditional format for the first 5 columns so far as described above (using the max and average function in the footer).
This was more troublesome than I imagined mainly because you've got only 1 table and it's in an Excel style format with no primary keys and no indexes. What this means is that your database is not at all normalised and it has seriously impacted on the performance of your report because of the calculations that needed to be done. Your report opens in 7 or seconds.
In any case see attached. On the report you can see an example of the QuarterW's 1 to 3, you just need to replicate exactly what I've done. So there's a listbox in grey that holds the smallest of the top 3 values grouped by Quarter and QuarterWk. The textboxes in grey gets their values from the listbox and does so based on every Quarter and QuarterWk. The values returned by these textboxes then drive the Conditional Format.
Here's the code in the textbox (i.e. smallest of the top 3 values) whose value is used for the Conditional Format:
Code:
=[lstQ].[Column]((13*([Quarter]-1))+1,2)
A listbox is zero based, that means the first column starts from 0 and so does the first row. Let's say Quarter is 1, therefore 13*(1-1) = 0, 0+1 = 1, which means that we want the second row and if we put that in the Column property of the listbox we have Column(1,2) meaning second row (i.e. 1) in the third column (i.e. 2). I've noticed an oddity with the Column property as well but I won't bug you with the details.
The code in the Conditional Format for QuarterWk 3 (for example) is:
Code:
([txtTop3_3]*IIf([Quarter],1,1))
The IIF() part doesn't affect the calculation but it helps drive the Conditional Format and it's required because the report is Grouped By Quarter. If that IIF() part isn't present, the value of txtTop3_3 won't update when the report moves to the next group.
The quickest way to get the values is to do the calculations in code, save it to a temp table and use the temp table as the row source of the listbox in the report.
