Solved

Find the Max value of different fields in a row

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Top 1 of each supplier 55 56
Access report groups with sums 5 28
Access Delete Query Not Working 13 30
Display <> "Yes" records 10 9
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

777 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