Find the Max value of different fields in a row

Dear Experts,

I have a table that has 5 columns for balances of 5 different accounts a customer holds. So the table looks as follows

CustID     Acct1     Bal1     Acct2     Bal2     Acct3     Bal3     Acct4     Bal4     Acct5     Bal5

I need to write a query that returns all the custID's and the maximum balance of all the accounts. The resultset should look as follows

CustID     MAX_Balance

I know that MAX cannot be used in this case as it finds the max within a column. I need to find the max within a row. How can this be done? Please help.


Who is Participating?
rockiroadsConnect With a Mentor Commented:
ok, from what I gather, u want the value of the highest balance?

what then if u used a vba func to help you?

CustID     Acct1     Bal1     Acct2     Bal2     Acct3     Bal3     Acct4     Bal4     Acct5     Bal5

add this to a module

public function getMaxBalance(Bal1,Bal2,Bal3,Bal4,Bal5) as double
    getMaxBalance = Bal1
    if Bal2 > getMaxBalance then getMaxBalance = Bal2
    if Bal3 > getMaxBalance then getMaxBalance = Bal3
    if Bal4 > getMaxBalance then getMaxBalance = Bal4
    if Bal5 > getMaxBalance then getMaxBalance = Bal5
end function

Now in your query, u can do this

SELECT CustID,getMaxBalance(NZ(Ba1,0),NZ(Bal2,0),NZ(Bal3,0),NZ(Bal4,0),NZ(Bal5,0)) as MaxBalance
from mytable

note I used NZ so as to handle nulls
Wouldnt your table definiton be more suited to be like this



Now this is a one to many, u can have as many accounts per customer as u like and it can vary

all u now need to do is

select CustID, Max(Balance)
FROM tbLCustomer, tblAccounts
WHERE tblCustomer.CustID = tblAccounts.CustID

I believe your table definiton is wrong. What if u decide to have 6 accounts, think if the work involved, u have to change table defintion, your queries etc
using a table like I suggested, its merely adding another row

u can then extend tblAccounts to include account type etc
bedtime, night all
vbhargav80Author Commented:
Hi rockiroads,

thanks for the suggestion. I know this is how it should be done and seems sensible. But I've just taken this application over from a demoniac programmer who has now been sacked for his animalistic logic.

Unfortunately, we cannot change the table design since people have been using it for years. I have to work this design and come up with a solution.

Please HELP!
vbhargav80Author Commented:
Thanks for the solution. Works great!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.