Finding the MAX across several fields(columns)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to ceate a query with a field(column) that will give me the MAX
number in the row where the numbers are in several fields.

Example:

customer units1 units2 units3 MAX-units
ABC inc 100 50 25 100

The only thing I'm finding is for the MAX for a single field(column).
 
Well, the reason you are having a problem is that your table isn't
normalized. You shouldn't have repeating fields (units1, units2, units3).
You should simply have a field called Units and another field called
UnitNumber (or something like that). Then you can use the Max function and
group by Customer.

To get you by temporarily, you can use the following IIF statement:

IIf(([units1]>[units2]) And
([units1]>[units3]),[units1],IIf([units2]>[units3],[units2],[units3]))

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
 
The fact that you need to do this typically indicates that your table isn't
properly normalized. Since it's very unusual to require such a capability,
there's nothing built into Access (nor any other DBMS, I don't think)

For your simple 3-field situation, you should be able to use something like:

Max-units: IIf([units1] > [units2], IIf([units1] > [units3], [units1],
[units3]), IIf([units2] > [units3], [units2], [units3]))

In general, though, units1, units2 and units3 should be in separate rows in
a second table, so that you can use the Max function and the appropriate
Group By clause.

To simulate what that second table would look like, you could create a
query:

SELECT customer, 1 As UnitIndicator, units1 As Units
UNION
SELECT customer, 2 As UnitIndicator, units2 As Units
UNION
SELECT customer, 3 As UnitIndicator, units3 As Units

If you saved that as qryNormalized, you'd have a second query:

SELECT customer, Max(Units) As Max-units
FROM qryNormalized
GROUP BY customer
 
Thank you both very much for the replies.
This is my first time here and I did not explain my table properly but you
did give me the answer that I needed. I'll do the union query.
Thanks again,
Iceman


Douglas J Steele said:
The fact that you need to do this typically indicates that your table isn't
properly normalized. Since it's very unusual to require such a capability,
there's nothing built into Access (nor any other DBMS, I don't think)

For your simple 3-field situation, you should be able to use something like:

Max-units: IIf([units1] > [units2], IIf([units1] > [units3], [units1],
[units3]), IIf([units2] > [units3], [units2], [units3]))

In general, though, units1, units2 and units3 should be in separate rows in
a second table, so that you can use the Max function and the appropriate
Group By clause.

To simulate what that second table would look like, you could create a
query:

SELECT customer, 1 As UnitIndicator, units1 As Units
UNION
SELECT customer, 2 As UnitIndicator, units2 As Units
UNION
SELECT customer, 3 As UnitIndicator, units3 As Units

If you saved that as qryNormalized, you'd have a second query:

SELECT customer, Max(Units) As Max-units
FROM qryNormalized
GROUP BY customer


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Iceman said:
I'm trying to ceate a query with a field(column) that will give me the MAX
number in the row where the numbers are in several fields.

Example:

customer units1 units2 units3 MAX-units
ABC inc 100 50 25 100

The only thing I'm finding is for the MAX for a single field(column).
Click to expand...
Click to expand...
 
Back
Top