SQL Need to query for Max (amount) from approx 30 fields within same record. How would I create a SQL statement to do this?

Advertisement

May 17, 2008 05:12am pdt

Experts Exchange

Announcements Feedback

Advertisement

1. capricorn1 1,301,290
2. DatabaseMX 890,113
3. LSMConsu... 482,407
4. boag2000 335,153
5. harfang 313,826
6. peter57r 272,376
7. TextReport 229,085
8. GRayL 209,614
9. ee_rlee 170,073
10. cactus_data 154,536
11. jimhorn 151,903
12. matthews... 147,637
13. JezWalters 137,435
14. jefftwilley 126,171
15. puppydog... 102,935

1. capricorn1 8,958,328
2. nico5038 5,685,831
3. rockiroads 5,398,237
4. peter57r 3,716,871
5. LPurvis 3,361,835
6. shanesue... 3,288,317
7. LSMConsu... 3,217,997
8. harfang 2,930,659
9. stevbe 2,838,604
10. DatabaseMX 2,773,449
11. jimhorn 2,571,495
12. GRayL 2,369,458
13. flavo 2,104,083
14. mbizup 1,976,668
15. jadedata 1,963,977

05.02.2008 at 12:05PM PDT, ID: 23372679

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

SQL Need to query for Max (amount) from approx 30 fields within same record. How would I create a SQL statement to do this?

Zones: Microsoft Access Database, SQL Query Syntax

Need to write a SQL query to find MAX (amount) from approx 30 or more fields within the same record. How would I write that query?

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: deer3777

Solution Provided By: capricorn1

Participating Experts: 4

Solution Grade: A

Views: 19

Translate:

Loading Advertisement...

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Operating Systems
Hardware

Programming
Networking

Software

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

05.02.2008 at 12:11PM PDT, ID: 21489273

Rank: Genius

select queries >new
select the table >add

place all the fields you want to get the max value
click the big (sigma symbol) Totals
select max for each field in the line which says Group By

05.02.2008 at 12:14PM PDT, ID: 21489301

Rank: Genius

or if you want just the sql

select max(amt1),max(amt2),max(amt3)
from TableX

05.02.2008 at 12:32PM PDT, ID: 21489422

the problem is this:

I am only looking for the MAX amount from all of those fields, not the MAX amount per field

05.02.2008 at 12:34PM PDT, ID: 21489438

Rank: Genius

If you want to determine which of 30 fields in a record is the largest, a query is not the way to go. You need code. Way beyond the nesting limit of 7 iif()'s. Is it 30 consecutive fields in the record - datatype - just the max value, or also the field name? ie. Start at field 3 and compare out to 33?

05.02.2008 at 12:39PM PDT, ID: 21489471

Rank: Genius

then you will need a function to do that.

place this codes in a module

Function fGetMax(ParamArray sArr()) As Double
Dim j, xMax
xMax = CDbl(Trim(sArr(0)))
For j = 1 To UBound(sArr)
If CDbl(Trim(sArr(j))) > xMax Then
xMax = Trim(sArr(j))
End If
Next
fGetMax = xMax
End Function

to use in a query

select fGetMax(f1,f2,f3,f4,....... ,f30) as MaxAmount
from tableX

f1,f2,f3..... f30 are the names of the 30 fields

Accepted Solution

05.02.2008 at 12:40PM PDT, ID: 21489475

it is 30 or more fields within a record that I am trying to pull one MAX amount from and the names of the fields are numbers as well. How would I write the VBA code for this?

05.02.2008 at 12:42PM PDT, ID: 21489497

Rank: Genius

use the codes from my last post

05.02.2008 at 12:54PM PDT, ID: 21489569

Rank: Genius

Public Sub GetMaxFld(myTable as String)

Dim db as DAO.Database, rs as DAO.RecordSet, fldVal as Double, fldName as String, i as integer
Set db=CurrentDB
Set rs=db.OpenRecordSet(myTable)
do while not rs.eof assuming we start at the third field and go to number 33
For i=2 to 32
If fldVal< rs.field(i).value Then
fldVal - rs.fields(i).value
fldName=rs.fields(i).name
End If
Next i
debug.print "Max Value:", fldName, fldVal
Loop
End Sub

Go to the Immediate Pane of the VB Editor and type:

GetMaxFld("myTableName")

This is a quick and dirty. If you want the results as part of a recordset - too easy.

05.03.2008 at 12:26AM PDT, ID: 21491611

Rank: Genius

> the names of the fields are numbers as wel

You can still use a function similar to what Cap showed. But you will need to enclose the field names in square brackets.

TheMax: MaxOf([1],[2],[3],...)

Note that you are above the limit of 30-nesting (so 29 here) parameters for a function. You will need to split it up:

TheMax: MaxOf( MaxOf([1],[2],...[20]), MaxOf([21],[22],...[30]) )

(°v°)

                       
                                Function MaxOf(ParamArray Values())
 
    Dim i As Integer
 
    MaxOf = Values(0)
    For i = 1 To UBound(Values)
        If IsNull(MaxOf) Then
            MaxOf = Values(i)
        ElseIf Values(i) > MaxOf Then
            MaxOf = Values(i)
        End If
    Next
 
End Function

Open in New Window

05.03.2008 at 11:07AM PDT, ID: 21493213

Rank: Genius

using my approach, it would be best to have a primary key (datatype long) field in the table followed by the 30 (I'm assuming numeric) fields. You would step through each of fields 1 through 31 (field numbers are zero based), incrementing the record until EOF.

Call the function with:

SELECT pk, GetMaxFld() AS MaxFldVal From myTable;

using this code:

Public Function GetMaxFld(mypk as Long) AS String

Dim db as DAO.Database, rs as DAO.RecordSet, fldVal as Double, fldName as String, i as integer
Set db=CurrentDB
Set rs=db.OpenRecordSet(myTable)
rs.movelast
rs.movefirst
rs.findfirst "pk = mypk
For i=1 to 31
If fldVal< rs.field(i).value Then
fldVal - rs.fields(i).value
fldName=rs.fields(i).name
End If
Next i
GetMaxFld = fldName & " - " & fldVal
End Sub

05.03.2008 at 11:08AM PDT, ID: 21493216

Rank: Genius

Of course incrementing the record is done in this case by the query.

05.04.2008 at 03:26PM PDT, ID: 21497174

As it turns out, I have 185 fields to find MAX from with 5485 records to search from. I have already ran a query to find the MAX in each field. Now I will try to run the code you provided earlier to find the MAX of all the fields.

05.04.2008 at 05:51PM PDT, ID: 21497562

Capricorn1,

The function works but is there anyway to include the field name with the output of the function?

05.04.2008 at 06:29PM PDT, ID: 21497657

Harfang,

The code you supplied gave me a list of all Max of each field. I need a MAX within all fields including field name the MAX number was derived.

05.04.2008 at 06:47PM PDT, ID: 21497705

GrayL,

I get a compile error on both of your solutions.

05.04.2008 at 06:48PM PDT, ID: 21497710

GrayL,

However, the compile error, I am looking to get both the value and the field name

05.04.2008 at 06:51PM PDT, ID: 21497713