Maximize Values in a Row

[lewis33]

Hi,
I have a query that has something like

County, Male Percent, Female Percent, Robot Percent(kidding)

grouping on the county.

Can I run a query in MS Access that can MAX on the row and update a column OR give me a new calculated field?

Thank you, easy to do in a spreadsheet but I'd rather keep avoid that.

 

[HitechUser]

lewis33,


What do you expect to update, in what field, in what row? When you create a grouping you get a "summary" of all the records you grouped. Which record you want Access to update?????

You could change your query to a "make table" query and have the new calculated field created. Or use your query as input for your form or report then use the max function there.

I hope this helps...

 

[WorkrAnt]

From what you described, it sounds like you are trying to make the query table behave like an Excel spreadsheet. This is not possible through a query alone. When someone wants calculated fields you could possibly run another query with the max of the fiels as "new fields" and use these values for a form or report.

 

[lewis33]

Hi HiTech and WorkrAnt, I was (am) trying to calculate the maximum of some fields in a row (rather than in a column) and thought something might be better than:

IIF([malepercent]>[femalepercent],[malepercent],[femalepercent])

and then do the same thing for the other field in the row.

Doesn't matter to me if I am doing and update query or a make table.

 

[PHV]

Create your own Max function, like this in a standard code module:
Public Function myMax(ParamArray Args())
Dim i As Long, rv
rv = Args(LBound(Args))
For i = 1 + LBound(Args) To UBound(Args)
If IsNull(rv) Or rv < Args(i) Then rv = Args(i)
Next
myMax = rv
End Function
Then in the query grid:
[Percent max]: myMax([Male Percent],[Female Percent],[Robot Percent])


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244