MAX Across Multiple Columns 2

[bajo71]

Hello,
I need to find the maximum amount within a single record that spans multiple columns. Is this possible without using a subquery? Something akin to MAX AMT: Field1 + Field2 etc...

Thank you..........Mick

 

[JBinQLD]

G'day fella,

i'm sure my brain is in backwards and there must be any easier way than this but a quick solution could be to write a quick VBA function. Something like:

function GetMaxOfFieldValues(strFieldValues as string) as long

Dim lngMaxSoFar As Long
Dim lngThisValue As Long
Dim intCounter As Integer

lngMaxSoFar = 0
intCounter = 0
arrlngValues = Split(strFieldValues, ";")

Do Until intCounter > UBound(arrlngValues)
   lngThisValue = arrlngValues(intCounter)
   If lngMaxSoFar < lngThisValue Then lngMaxSoFar = lngThisValue
   intCounter = intCounter + 1
Loop

GetMaxOfFieldValues= lngMaxSoFar
end function

and then call it with some numbers seperated by semi-colons

Eg. GetMaxOfFieldValues( cstr(field1) & ";" & cstr(field2) )

as the function splits on the ; you can pass it as many fields as you want.


Hope this helps,

JB

 

[fneily]

You didn't post your table structure, but based on experience, I'd say your table isn't normalized. Since you are looking for a max value from a collection of fields, it would seem that they could form, in essence, a table or category. This violates the first normal form. Example,
TableID Cat Dog Oranges Cars
TID1 4 5 10 2

You want the max value of this person's things. This is constructed incorrectly. It should look like:
TableID Thing Amount
TID1 Cat 4
TID1 Dog 5
TID1 Oranges 10
etc.
with multi-field primary key TableID/Things.
Now the max value is easy to get.

Here's a place to start to read about normalization:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[MajP]

This is from PHV

'A generic function to get the max value of an arbirtrary numbers of same type values:
Public Function myMax(ParamArray Args())
Dim i As Long, rv
For i = 0 To UBound(Args)
  If IsNull(rv) Or rv < Args(i) Then rv = Args(i)
Next
myMax = rv
End Function

You can pass in any number of field values, but take fneily's advice.

 

[MajP]

You can do this in SQL as well, and for performance and flexibility you should always strive to do it in SQL.

Here is one reason why. Assume you hae 10,000 records and 10 fields. Every time you open that query you will iterate 10 loops by 10k times (100,000). If you put a criteria on this calculated field and you just want to see the records with a max value of say 20, it still has to perform 100k loops even if you only return a couple of records.

There is probably a couple of ways to do this in SQL the easiest I can think of is build a union query of all the fields: primary key, fieldOne; primaryKey, fieldTwo; .... primaryKey, fieldN

Then groupBy primary key and return the max of the union column.
This is far more efficient and portable to other dbs. If you are only dealing with a small amount of data then the calculated function is fine.

I am a far better coder than query builder, but over the years I have spent a lot of time in the query forum trying to fine SQL solutions even though I could code a function quickly.

 

[JBinQLD]

The union query was the only way I could think of for this too but of course that would be labour intensive if other fields were added.

Certainly the preferable solution would be to organise the data in a more suitable manner but it there not another SQL mehod rather than the union option?

Would be interested to know. Have a great weekend all,

JB

 

[fneily]

the easiest I can think of is build a union query of all the fields:". This has become known as a "normalizing" query. It'll put non-normalized data into a structure the data should have been put in when you design a table and then go through the normalizing process. You'll see this suggestion throughout the Access forums. Just build your tables correctly by following the protocols of Access.
And JBinQLD makes one of the strong arguments for learning normalization:"The union query was the only way I could think of for this too but of course that would be labour intensive if other fields were added." A well designed table should allow for any addition of future data, not addition of fields. If some record doesn't have data for a particular field, you'll be displaying blank (null) cells. In theory, you have, and will be, creating variable length records which are not allowed in Access tables.

 

[MajP]

Wait a minute, I just want to go on the record in that my quote

"the easiest I can think of is build a union query of all the fields:".
Refers to the easiest SQL solution, not the best solution. The best solution would be to restructure you data base and I stated that in my prior post.

You can pass in any number of field values, but take fneily's advice

 

[bajo71]

Wow,
Thanks for the generous responses!

 

[fneily]

I agree with you, MajP. With what's given, your way is probably the best. My beef is with the database upfront. I'm just trying to show him that alot of time designing and learning the protocols of Access will avoid problems down the line.