Solved

Find the Max value of different fields in a row

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HasData 9 39
Ms Access Many To Many Relationship Explantion Help Please 5 43
subtract 1 in Access 2003 query 7 39
ms/access hyperlink/ftp 7 39
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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now