Multiple columns

  • Thread starter Thread starter Rob Kings
  • Start date Start date
R

Rob Kings

I have a table with multiple columns Lets call them A B C. They may (or may
not) contain values Hence the data in a single row might look like

ColA, ColB, ColC
1, , 3

What I want to do is to be able to select a single value being the max of
those
columns.

all of the functions I can think of (MAX, DMAX) work on multiple rows in a
single column. I can't think of a way to work across columns in a single
row.

Also, how about an average? In this example the answer should be 2 and not
1:33 (i.e. summed and divided by 2 not 3)

Any ideas?

Cheers

Rob
 
Rob said:
I have a table with multiple columns Lets call them A B C. They may
(or may not) contain values Hence the data in a single row might look
like

ColA, ColB, ColC
1, , 3

What I want to do is to be able to select a single value being the
max of those
columns.
Click to expand...

You can use nested immediate-ifs.

MaxVal:
IIf([ColA]>[ColB],IIf([ColA]>[ColC],[ColA],[ColC]),IIf([ColB]>[ColC],[ColB],
[ColC]))

As you can see nested IIf() functions can quickly get ugly and hard to
decipher. Any more than a few columns and I would create a custom function
instead.
 
Rick

Thanks for the reply.

I did wonder about that, but like you say it can get ugly, and sadly I had
simplified the situation. There are actually 7 columns (Monday-Sunday) and
so the iif would be pretty big.

As ever, what I wanted to do didn't sound so outrageous that no one has ever
needed to do it before, but I can't find anything on Google (though thinking
of appropriate search phrases isn't easy)

Cheers

Rob
Rick Brandt said:
Rob said:
I have a table with multiple columns Lets call them A B C. They may
(or may not) contain values Hence the data in a single row might look
like

ColA, ColB, ColC
1, , 3

What I want to do is to be able to select a single value being the
max of those
columns.
Click to expand...

You can use nested immediate-ifs.

MaxVal:
IIf([ColA]>[ColB],IIf([ColA]>[ColC],[ColA],[ColC]),IIf([ColB]>[ColC],[ColB],
[ColC]))

As you can see nested IIf() functions can quickly get ugly and hard to
decipher. Any more than a few columns and I would create a custom
function
instead.
Click to expand...
 
You could use a Union query to "normalize" your data and then use a
standard Summary Totals query.

There are multiple examples of this in this group.

Regards,
Andreas


Rob said:
Rick

Thanks for the reply.

I did wonder about that, but like you say it can get ugly, and sadly I had
simplified the situation. There are actually 7 columns (Monday-Sunday) and
so the iif would be pretty big.

As ever, what I wanted to do didn't sound so outrageous that no one has ever
needed to do it before, but I can't find anything on Google (though thinking
of appropriate search phrases isn't easy)

Cheers

Rob
Rob said:
I have a table with multiple columns Lets call them A B C. They may
(or may not) contain values Hence the data in a single row might look
like

ColA, ColB, ColC
1, , 3

What I want to do is to be able to select a single value being the
max of those
columns.
Click to expand...

You can use nested immediate-ifs.

MaxVal:
IIf([ColA]>[ColB],IIf([ColA]>[ColC],[ColA],[ColC]),IIf([ColB]>[ColC],[ColB],
[ColC]))

As you can see nested IIf() functions can quickly get ugly and hard to
decipher. Any more than a few columns and I would create a custom
function
instead.
Click to expand...
Click to expand...
 
Rob said:
Rick

Thanks for the reply.

I did wonder about that, but like you say it can get ugly, and sadly
I had simplified the situation. There are actually 7 columns
(Monday-Sunday) and so the iif would be pretty big.

As ever, what I wanted to do didn't sound so outrageous that no one
has ever needed to do it before, but I can't find anything on Google
(though thinking of appropriate search phrases isn't easy)

Cheers
Click to expand...

You might want to examine your table design. It _is_ actually fairly
unusual in a database application to compare or aggregate across columns
instead of across rows and it is a warning flag that the tables are not set
up properly when you find it necessary to do so.

Having a column per-day, per-week, per-year, etc., is (in most cases) an
incorrect setup. You would ordinarily have a setup more like...

NumberField, TextField, DateField...

....then you could create a crosstab query against this table that would
present the data in a per-day, per-week, per-year layout. Since all of the
similar data is now in the rows of a single column the normal aggregate
functions are easy to use.
 
Rick

<<You might want to examine your table design...>>

Ha. I should be so lucky. I should have perhaps said at the outset. I did
NOT design the database. I'm trying to get data out of somebody else's
system and I have no control over the design (or lack thereof)

Rob
 
Andreas

Hmmm. Not a bad idea. I might try that.

Rob
You could use a Union query to "normalize" your data and then use a
standard Summary Totals query.

There are multiple examples of this in this group.

Regards,
Andreas


Rob said:
Rick

Thanks for the reply.

I did wonder about that, but like you say it can get ugly, and sadly
I had simplified the situation. There are actually 7 columns
(Monday-Sunday) and so the iif would be pretty big.

As ever, what I wanted to do didn't sound so outrageous that no one
has ever needed to do it before, but I can't find anything on Google
(though thinking of appropriate search phrases isn't easy)

Cheers

Rob
Rob Kings wrote:

I have a table with multiple columns Lets call them A B C. They may
(or may not) contain values Hence the data in a single row might
look like

ColA, ColB, ColC
1, , 3

What I want to do is to be able to select a single value being the
max of those
columns.

You can use nested immediate-ifs.

MaxVal:
IIf([ColA]>[ColB],IIf([ColA]>[ColC],[ColA],[ColC]),IIf([ColB]>[ColC],[ColB],
[ColC]))

As you can see nested IIf() functions can quickly get ugly and hard
to decipher. Any more than a few columns and I would create a
custom function
instead.
Click to expand...
Click to expand...
Click to expand...
 
maybe,

Public Function ColOp(Op As String, ParamArray cols()) As Variant
'Operation on columns
Dim l As Long, n As Long, S As Double
For l = 0 To UBound(cols)
If Not IsNull(cols(l)) Then
Select Case Op
Case "X": 'max
If ColOp < cols(l) Or IsEmpty(ColOp) Then ColOp = cols(l)
Case "N": 'min
If ColOp > cols(l) Or IsEmpty(ColOp) Then ColOp = cols(l)
Case "A": 'avg
S = S + CDbl(cols(l))
n = n + 1
Case Else:
Err.Raise vbObjectError + 1, "ColOp()", "Oops - bad op"
Exit Function
End Select
End If
Next l

'Exit select
Select Case Op
Case "A": 'avg
ColOp = S / n
End Select

End Function

Rgds, Peter
 
Peter

Sorry, I should have been more specific. Because of the nature, origin, and
ownership of the database I really need an SQL solution.

Thanks all the same.

Rob
 
Back
Top