Find Max in crosstab

MackMan

Registered User.
Local time
Today, 13:20
Joined
Nov 25, 2014
Messages
174
Hello all.

I have a very simple CT query and it's been working fine....
Code:
TRANSFORM Sum(qryTRANSACTIONS_ALLPrevious.Amount) AS SumOfAmount
SELECT qryTRANSACTIONS_ALLPrevious.Category, Sum(qryTRANSACTIONS_ALLPrevious.Amount) AS Total
FROM qryTRANSACTIONS_ALLPrevious
WHERE (((qryTRANSACTIONS_ALLPrevious.Category)=[Forms]![frmCATEGORIES]![cboCategorySelect]))
GROUP BY qryTRANSACTIONS_ALLPrevious.Category
PIVOT Format([TransDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
...but now I'd like to add some statistical analysis by finding out what the maximum and minimum values are by a given month.

Is this possible? I've tried a max but it returns a particular record value, and not totalled by month. I've also tried to run a query a second time, this time by Max, but again, gives me a total for a particular record.

many thanks as always.
 
That is what Max() does, returns the unique value that is greater than the others in the given field. What you might need to do is use this query as the input table for another query.
 
[deleted]* not a solution
 
Last edited:

Users who are viewing this thread

Back
Top Bottom