Grouped Access Report - Conditional Formatting

Question

I have an Access crosstab query as data source for a report grouped into two parts, A and B.  I would like to conditionally format the maximum values in each column of the part A group only.  I have searched the web and tried several items discovered in the search but have not found a solution that applies.  For reference, a snapshot of the report is posted below.  Any assistance is greatly appreciated.

Answer 1

Below is a report based on the following simple crosstab query:

TRANSFORM Nz(Sum(Amount),0)

SELECT Format(SaleDate,"mmmm yyyy") AS [Month],

SUM(Amount*IIF(Salestype IN ("Cash","Credit"),1,0)) AS TotalSales

FROM Sales

GROUP BY FORMAT(SaleDate,"mmmm yyyy")

ORDER BY Year(SaleDate), Month(SaleDate)

PIVOT Amount IN ("Cash","Credit");

The maximum Amount in each of the three columns is shown in red by means of the following conditional formatting expressions:

[TotalSales]=DMax("TotalSales","qrySalesByMonth")

[Cash]=DMax("TotalCash","qrySalesByMonth")

[Credit]=DMax("TotalCredit","qrySalesByMonth")

The qrySalesByMonth query is as follows:

SELECT Format(SaleDate,"mmmm yyyy") AS SaleMonth,

SUM(Amount) AS TotalSales,

SUM(Amount * IIF(SalesType="Cash",1,0)) AS TotalCash,

SUM(Amount * IIF(SalesType="Credit",1,0)) AS TotalCredit

FROM Sales

GROUP BY Format(SaleDate,"mmmm yyyy");

In your case you'd need to include some criterion in the conditional formatting expressions, and in your eqivalent of the qrySalesByMonth query to restrict the formatting and the  query to group A only.

Answer 2

Ken thank you very much.  I have been stumped with this for several hours and can now move forward.

Answer 3

Here is the SQL view of the query.

TRANSFORM Count([XXX 28 Day].Area) AS CountOfArea

SELECT [XXX 28 Day].Sort, [XXX 28 Day].Classification, [XXX 28 Day].Part, Count([XXX 28 Day].Classification) AS Total

FROM [XXX 28 Day]

GROUP BY [XXX 28 Day].Sort, [XXX 28 Day].Part, [XXX 28 Day].Classification, [XXX 28 Day].Part

ORDER BY [XXX 28 Day].Sort

PIVOT [XXX 28 Day].Area In (511,512,513,514,515,516,521,522,523,524,525);

Answer 4

Below is a datasheet view of the crosstab query.  I appreciate your help.

Answer 5

I don't see any snapshot of the report. Can you share the SQL view of your crosstab and/or some sample datasheet view of the crosstab?