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
Solved

Find the Max value of different fields in a row

Posted on 2006-11-09
5
305 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
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

840 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