MAX value in query

[ineedahelp]

What might be the formula or code to find the max value of 3-6 fields PER RECORD in a query. I have individual vendors offering prices for a number of products. Each product is a new record. I would like a calculated expression in my query design to be BEST COST. BEST COST would be the max value of the 3-6 vendors. I thought of using iif() but I felt there should be better way. Any help?

[NeoPa]

2 options I know of :
1. V V complicated IIF().
2. Call Public Function in a module designed to find max of parameters entered.

[ineedahelp]

Is there no MAX IN (list of fields) command?

[NeoPa]

I'm pretty sure there's not :(.
We had a thread in here last week about getting the max of three fields and that was handled by IIF() in the end.

[MMcCarthy]

What might be the formula or code to find the max value of 3-6 fields PER RECORD in a query. I have individual vendors offering prices for a number of products. Each product is a new record. I would like a calculated expression in my query design to be BEST COST. BEST COST would be the max value of the 3-6 vendors. I thought of using iif() but I felt there should be better way. Any help?

It's possible that this is suitable for a crosstab query using your query as a base.

Can you post the full sql statement of your query and I'll have a look.

[MMcCarthy]

It's possible that this is suitable for a crosstab query using your query as a base.

Can you post the full sql statement of your query and I'll have a look.

Sorry one other thing can you confirm it is the max of the values you are looking for and not the min (lowest value).

[PEB]

I can offer you 2 functions...

The first one after adaption can calculate max or min of all fields in a query:

Function min_in_columns(SQL, Start_col, End_col) As Double
Dim mydb As Database
Dim myr As Recordset
Dim i
Dim min_value
 
Set mydb = CurrentDb()
Set myr = mydb.OpenRecordset(SQL)
 
min_value = 0
 
myr.MoveFirst
For i = Start_col To End_col
    If min_value > myr(i) Then
        min_value = myr(i)
    End If
Next i
myr.Close
mydb.Close
 
min_in_columns = min_value
End Function
 

If you want to use it in a query based on table Data with PK ID so you have to type as column in your query:

Min:min_in_columns("SELECT * FROM DATA WHERE ID="+str(ID)+";", 5, 10)

This will give you the minimum from the columns between 6 and 11

And this function is a bit more simple for avoiding the multiple IIF() only:

Function MinNumb(A, b)
        If A < b Then
            MinNumb = A
        Else
            MinNumb = b
        End If
End Function
 
 

So for 3 fields it should be:
Min: MinNumb(C, MinNumb(A, b))

[ineedahelp]

To confirm...it is the MAX value I am looking for. My sql is:
SELECT Table1.RequestDate, Table1.Symbol, Table1.Cusip, Table1.MLRate, Table1.TotalQty, Total1.TotalNV, Total1.JefRate, Total1.JefQty, Total1.QuadRate, Total1.QuadQty, IIf([QuadRate]>[JefRate],[QuadRate],[JefRate]) AS [Best Rate], [MLRate]*[TotalNV] AS [Current Cost], [Best Rate]*[TotalNV] AS [Best Cost], [Best Cost]-[Current Cost] AS Difference, IIf([Best Rate]=[JefRate],"Jefferies",IIf([Best Cost]=[QuadRate],"Quadriserv","No Change")) AS [Who to Call]
FROM Table1;

As you can see I have begun an embedded IIF() statement. What I would like to do is in my BEST RATE column of my query, I would put some 'equation' that would return the MAX value from JefRate,ML rate and QuadRate. As for the suggestion of the "simple" function", your example showed complete code for 2 variables (a) and (b). If I have 3,4 or 5 variables, would this not just be the same as my embedded IIF() statement?

Thank you again for any help here!!

[ineedahelp]

I also like your function idea. I have never, but have always wanted to create a function. Would I create it in the modules area?

[NeoPa]

In the VBA window (Alt-F11 from the database), Right-click anywhere in your project and insert a module.
In here type in the Public function you want.

[MMcCarthy]

In the VBA window (Alt-F11 from the database), Right-click anywhere in your project and insert a module.
In here type in the Public function you want.

In the module just create the function, referencing Vladi's brilliant! idea.

Function MaxNumb(A, b)
        If A > b Then
            MaxNumb = A
        Else
            MaxNumb = b
        End If
End Function

Then in query

SELECT MaxNumb(C,MaxNumb(A, B)) As MaxNo ....

Get the idea?

[ineedahelp]

Yes, I did it and it worked! I actually changed the function given to work for more than 2 items! Thanks for all the support!

[MMcCarthy]

Yes, I did it and it worked! I actually changed the function given to work for more than 2 items! Thanks for all the support!

Using your initiative, that's what I like to see. If you want you can post the function you're currently using for the benefit of future searches on this thread.

Mary

[NeoPa]

I was just writing a recursive function to do it for any number of parameters when I realised VBA doesn't support passing on all the extra parameters in the 'ParamArray' to another function call :(.

(Killer - please contradict - I hope there really is a way).