Report using "Max" command

R

Roger Bell

I have designed a report based on a query which includes, among other fields,
2 fields call "Amount Pledged" and a field called "countofFrequency". EG of
entries as follows
Amount Pledged $100, Frequency 2
Amount Pledged $200, Frequency 4
I have created a text box to try and get it to list the Amount Pledged based
on the Maximum Frequency number, but cannot seem to get the code right. Any
help would be appreciated.
 
D

Douglas J. Steele

You could create a new query based on that query:

SELECT TOP 1 [Amount Pledged], countofFrequency
FROM MyQuery
ORDER BY countOfFrequency Desc

Alternatively, you could use:

SELECT [Amount Pledged], countofFrequency
FROM MyQuery
WHERE countofFrequency IN
(SELECT Max(countofFrequency FROM MyQuery)

Note that if the highest frequency applies to more than one Amount Pledged,
both queries will return more than one row.
 
Top