By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

MAX value in query

P: 98
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?
Nov 17 '06 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,186
2 options I know of :
1. V V complicated IIF().
2. Call Public Function in a module designed to find max of parameters entered.
Nov 17 '06 #2

P: 98
Is there no MAX IN (list of fields) command?
Nov 17 '06 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 17 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 17 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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).
Nov 17 '06 #6

PEB
Expert 100+
P: 1,418
PEB
I can offer you 2 functions...

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

Expand|Select|Wrap|Line Numbers
  1. Function min_in_columns(SQL, Start_col, End_col) As Double
  2. Dim mydb As Database
  3. Dim myr As Recordset
  4. Dim i
  5. Dim min_value
  6.  
  7. Set mydb = CurrentDb()
  8. Set myr = mydb.OpenRecordset(SQL)
  9.  
  10. min_value = 0
  11.  
  12. myr.MoveFirst
  13. For i = Start_col To End_col
  14.     If min_value > myr(i) Then
  15.         min_value = myr(i)
  16.     End If
  17. Next i
  18. myr.Close
  19. mydb.Close
  20.  
  21. min_in_columns = min_value
  22. End Function
  23.  
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:

Expand|Select|Wrap|Line Numbers
  1. Function MinNumb(A, b)
  2.         If A < b Then
  3.             MinNumb = A
  4.         Else
  5.             MinNumb = b
  6.         End If
  7. End Function
  8.  
  9.  
So for 3 fields it should be:
Min: MinNumb(C, MinNumb(A, b))
Nov 18 '06 #7

P: 98
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!!
Nov 20 '06 #8

P: 98
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?
Nov 20 '06 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 20 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Function MaxNumb(A, b)
  2.         If A > b Then
  3.             MaxNumb = A
  4.         Else
  5.             MaxNumb = b
  6.         End If
  7. End Function
Then in query

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

Get the idea?
Nov 20 '06 #11

P: 98
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!
Nov 20 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 20 '06 #13

NeoPa
Expert Mod 15k+
P: 31,186
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).
Nov 20 '06 #14

Post your reply

Sign in to post your reply or Sign up for a free account.