HIGHLIGHTING MAXIMUM VALUE IN A ROW-ACCESS REPORTS-REPLY TO DHOOKUM

[STRATMAN1]

ORIGINAL THREAD:
I have a report based on a crosstab query, with rows as "month" and columns as employee sales dollars for that month. I am trying to shade the highest value in each month row. The shading is not hard, but I dont know how to construct the code (have tried dmax function) when trying to find the max of column fields in each row. Have tried the dmax function, DMax("[joe]" "[sally], ... etc but cant figure out how to make an array out of the column field names. Perhaps there is an easier way. All good help appreciated. 1st reply was from dhookum, who asked for SQL view of my crosstab query:
HERE IS THE SQL VIEW OF MY CROSSTAB QUERY:
TRANSFORM Sum(TBLSALES.SALESDOLLARS) AS SumOfSALESDOLLARS
SELECT TBLSALES.MONTH
FROM TBLSALES
GROUP BY TBLSALES.MONTH
PIVOT TBLSALES.EMPLOYEE;

employees is the column header in the crosstab. If DHookum or anyone else might have suggestions, they are much appreciated.

 

[dhookom]

Try create another query
====qtotSalesByMthEmp=======
SELECT [Month], Employee, Sum(SalesDollars) AS SumOfSalesDollars
FROM tblSales
GROUP BY [Month], Employee;

and then another query
====qtotMaxMth=============
SELECT [Month], Max(SumOfSalesDollars) AS MaxOfSumOfSalesDollars
FROM qtotSalesByMthEmp
GROUP BY [Month];

And then your crosstab:
TRANSFORM Sum(tblSales.SalesDollars) AS SumOfSalesDollars
SELECT tblSales.Month, qtotMaxMth.MaxOfSumOfSalesDollars
FROM qtotMaxMth INNER JOIN tblSales ON qtotMaxMth.Month = tblSales.Month
GROUP BY tblSales.Month, qtotMaxMth.MaxOfSumOfSalesDollars
PIVOT tblSales.Employee;

This will get a new column with the max which you can compare to the other columns with conditional formatting or code.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]