Using Max

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

Sharon

I have three records with different types of collaterial
for the same account. I want the type of collaterial for
a specific account with the highest rate to appear in my
query. In this case, the account 3001 has CASH as the
collaterial type with the highest rate .

How and where do I use the MAX function in the query?

ACCT# RATE COLLATERIAL TYPE

3001 2.45 Residential Property
3001 0.00 Comercial Property
3001 8.40 Cash
 
The MAX function, by itself, will return the RATE which has the
desired maximum value. In order to see which rows have a RATE value
equal to the max, you would have filter by this value. That generally
means you would have a subquery to return this MAX value in the WHERE
clause.

SELECT [ACCT#], RATE, [COLLATERIAL TYPE]
FROM YourTable
WHERE RATE = (SELECT MAX(RATE)
FROM YourTable)

When the maximum rate is found in more than one row, all the rows that
"tie" for first place will be returned.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I have three records with different types of collaterial
for the same account. I want the type of collaterial for
a specific account with the highest rate to appear in my
query. In this case, the account 3001 has CASH as the
collaterial type with the highest rate .

How and where do I use the MAX function in the query?

ACCT# RATE COLLATERIAL TYPE

3001 2.45 Residential Property
3001 0.00 Comercial Property
3001 8.40 Cash
Click to expand...

One way is to use a Subquery: put a criterion on [Rate] of

=(SELECT Max([Rate]) FROM yourtable AS X WHERE X.[Acct#] =
[yourtable].[Acct#])
 
Another way. Although this is a little more complicated, I've found
this to be faster then the method recommended by Tom when used with
larger data sets. The concept here is that you create a subquery that
computes a recordset that contains an account# and the MaxRate for
that account number.

SELECT T.*
FROM yourTable T
INNER JOIN (SELECT Acct#, MAX(Rate) as MaxRate
FROM yourTable
GROUP BY Acct#) as M
ON T.Acct# = M.Acct#
AND T.Rate = M.MaxRate

--
HTH

Dale Fye


I have three records with different types of collaterial
for the same account. I want the type of collaterial for
a specific account with the highest rate to appear in my
query. In this case, the account 3001 has CASH as the
collaterial type with the highest rate .

How and where do I use the MAX function in the query?

ACCT# RATE COLLATERIAL TYPE

3001 2.45 Residential Property
3001 0.00 Comercial Property
3001 8.40 Cash
 
AccountNo ExcpRate Collaterial Type


I created a table,tblExcpRate from my initial query,
qryExcpRate, and was able to get the MAX ExcpRate from
each account with the following code: How can I cut out
the extra step of creating a new table and use the max
function in the query instead?

---NOTE--- The ExcpRate is a calculated field in my query.

ExcpRate: round(nz([ExcpSum]/[smple_sz])*100,2)
-------------------------------------------------------
SELECT tblExcpRate.ExcpRate, tblExcpRate.CollaterialType,
tblExcpRate.account
FROM tblExcpRate
WHERE (((tblExcpRate.ExcpRate)=(SELECT Max([ExcpRate])
FROM tblExcpRate AS X WHERE X.[account] = [tblExcpRate].
[Account])));
 
Back
Top