Locating MAX records off a query

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

My first query, qryAllExcptRates, calculates lots of
fields to get an Exception Rate.
ExcptRate:(field1+field2+field3)/field4)) One account may
have many exception rates.

I want only the MAX exception rate for each account.
Right now, I am creating a table from the first query and
then using the code:

(SELECT Max([Excpt_Rate]) FROM [tblAllExcptRates] AS X
WHERE X.[account]=[tblAllExcptRates].[Account])

How can I skip the "Make Table" step and apply the MAX
code to the first query or, if necessary, to a second
query? (actual code would be helpful because I keep
getting an incorrect syntax note)
 
How can I skip the "Make Table" step and apply the MAX
code to the first query or, if necessary, to a second
query?
Click to expand...

Use a Subquery: put

=(SELECT Max((field1+field2+field3)/field4)) FROM yourqueryname)

as a criterion under the calculated field.
 
I need the Max[Excpt_Rate] for each account number.
Click to expand...

ok, include the account number as a criterion in the Subquery. If
you'll post your SQL we might be able to help!
 
Back
Top