Retuning multiple values from Variable

[Gsurfdude]

Hello again,

After fighting with a crummy DB design to find the max of 24 hours in poor
designed table, I need to bring back the hour that WAS the max. I have a
table with 24 hour data in it for counties (I work for GDOT). So, I run a
query like SELECT CNTYID,Max(Hr1) AS MaxOfHr1,Max(hr2) AS MaxOfHr2 and so on
for all 24 hours. The query DOES bring back the max in rows. I then have
another query to bring back the max value in each row via a function called
with in expression builder in VBA. Good, this works. It brings back the max
value for the row giving me one column. My problem is getting the column
value, this is the "time" that I need, hence hr1 is 1 am hr 2 2am etc... I
created a wrapper function like this:

Public Function GetGlobal()
GetGlobal = IndexOf
End Function

This only brings back one value for my hour.
my query now looks something like this.

CntyID MaxHR HR
001 53 18
002 101 18
003 345 18
004 500 18

In each of the counties hours the physical column in the one table the max
hour is different. Like 001 the max hour is column HR17 so I need to see 17
in the example above as well as HR19 for 002. How do I bring back multiple
values from a variable

Here's my functions: MaxofList brings back the max value in a given row
(Thanks Allen Browne!) The second for loop goes the elements of the array + 1
to get the ordinal position of the hour so I can find where it is. So in my
query I use a expression that looks like this to pass to the function below.

MaxHR:MaxOfList(Hr1,HR2,HR3,HR4,HR5,HR6,...HR24)

Function MaxOfList(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.
Dim J As Integer

varMax = Null 'Initialize to null
IndexOf = -1

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

' 'Default to an error code
'
' 'Return the column position which represents the hour
' ' e.g HR19, HR20 etc...
If Not IsArray(varValues) Then Exit Function
'Check for matching index
For J = 0 To UBound(varValues)
If StrComp(Val(varMax), Val(varValues(J))) = 0 Then
IndexOf = J + 1
Debug.Print IndexOf
Exit For
End If
Next
MaxOfList = varMax
End Function


This is become interesting and if any one has any thoughts I'd be stoked !

Thanks in advance.

G

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When I come across a spreadsheet type table that is hard to work w/ I
usually find I can "Normalize" it by using a UNION query & then run my
queries on the Unionized query (View). E.g.:

SELECT County, 1 As [Hour], HR1 As Hours
FROM table_name

UNION

SELECT County, 2 As [Hour], HR2 As Hours
FROM table_name

.... etc. ...

UNION

SELECT County, 24 As [Hour], HR24 As Hours
FROM table_name

The only CON about this scheme is the queries run slower 'cuz the UNION
query has to run first & it may take a while (depending on table size).
The PRO is all your queries will be easier. E.g. Find the max hour:

SELECT TOP 1 [Hour], Hours
FROM <union query name>
ORDER BY Hours DESC

This might get more than one row if there are equal Hours in different
rows.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlWJvIechKqOuFEgEQJ42ACbBpB8IS7+br1VIdH2W+dhOYqW1QsAnirC
rlsT4IVp/wjLemVZfR5iwqc8
=0RCn
-----END PGP SIGNATURE-----

 

[John Spencer (MVP)]

If you want the GetGlobal to run for each line, you wil need to force it to run
by using a parameter. Change GetGlobal to

Public Function GetGlobal(anyvalue)
....

Then call it from the query like

GetGlobal(Hr1)

You can use any field in the GetGlobal call. The fact that there is a variable
of some kind forces the function to get called for every row. I don't know of a
way to ensure that GetGlobal is called after the MaxOfList function. It may
work on the order of the columns in the Select clause.

A better solution is described by MGFoster using the Union Query scheme,
although with 24 hours (and therefore 24 queries in the Union) it may break
down before you get all 24 hours into one big union query. If it works, I would
suggest using UNION ALL vice UNION to increase the speed of the query a bit.