Solved

Find the Max value of different fields in a row

Posted on 2006-11-09
5
314 Views
Last Modified: 2012-06-27
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.

Thanks

0
Comment
Question by:vbhargav80
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17910311
Wouldnt your table definiton be more suited to be like this

tblCustomer
CustID
Name
etc

tblAccounts
AcctID
CustID
Balance


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
GROUP BY 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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17910313
bedtime, night all
0
 

Author Comment

by:vbhargav80
ID: 17910433
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!
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
ID: 17912401
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
0
 

Author Comment

by:vbhargav80
ID: 17927694
Thanks for the solution. Works great!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question