Arrays in Access.

[KK]

How can I find out the maximum or minimum values out of many values in
an array. I have an array in Access that loads upto 20 values depending
on a sql query. I would like to find out the min and max values from
this array. Is there like a function thats like Max(array()) that can
do that. Please let me know. Thank you
KK

[pietlinden@hotmail.com]

Re: Arrays in Access.

hmm... I"m a little slow... "based on a SQL query" is the key here.
(Missed that the first 3 times I read it.) If you sort in the query by
the field you want to get the min/max for, the min and max will be at
opposite ends of the recordset.

So if you use (assuming query sorts in ascending order)
rs.MoveFirst
lngMin=rs.Field s("OrderByField ")
rs.MoveLast
lngMax=rs.field s("OrderByField ")

kinda ugly, but if you pass two variables by Reference, then you can
effectively pass two values out of your function/sub.

[KK]

Re: Arrays in Access.

Interesting! let me think about this. Thanks for the response.

[Steve]

Re: Arrays in Access.

pietlin makes a good point. If you don't order the SQL query, you'll
need to a) sort your array (bubble sort would be efficient, since it's
only 20 elements), or b) write your array to a temporary table and run
a query off of the temporary table (which begs the question of "why
didn't you just sort the original query to start with?")

If your situation is different than I am understanding it to be, can
you show how your array gets its values from the SQL query? I.e., does
the SQL query feed into a form, and your array pick out data from the
form?

[Pachydermitis]

Re: Arrays in Access.

I am not aware of functions like that - too bad.
if you are building the arrays from a recordset sort the recordset
first before you build the array, then you can just get the first and
last values. array(0) and array(ubound(ar ray).

if that is not an option, you are stuck with code. You didn't say
whether or not these were multi dimensional arrays or not - it changes
a little. Try something like this.

Function CreateArray()'t his is for testing and to show how the
getminmax fn is used
Dim ary()
ary = Array(8, 6, 7, 5, 3, 0, 9)
MsgBox GetMinMax(ary)
MsgBox GetMinMax(ary, True) 'get max
End Function

Function GetMinMax(ary, Optional bGetMax As Boolean = False)
Dim iCounter
GetMinMax = ary(0) 'set to the first one so it's not empty
For iCounter = 0 To UBound(ary)
If (GetMinMax < ary(iCounter)) Xor bGetMax Then
GetMinMax = ary(iCounter)
End If
Next
End Function

[Pachydermitis]

Re: Arrays in Access.

By the way, if this is something you have to do often, looping through
an array is MUCH faster than looping through a recordset.

[KK]

Re: Arrays in Access.

Good points guys! Thanks.
Sorting before hand actually helped a bit.

[Randy Harris]

Re: Arrays in Access.


"Steve" <theonesteve@gm ail.com> wrote in message
news:1134590993 .741788.232710@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> pietlin makes a good point. If you don't order the SQL query, you'll
> need to a) sort your array (bubble sort would be efficient, since it's
> only 20 elements), or b) write your array to a temporary table and run
> a query off of the temporary table (which begs the question of "why
> didn't you just sort the original query to start with?")
>
> If your situation is different than I am understanding it to be, can
> you show how your array gets its values from the SQL query? I.e., does
> the SQL query feed into a form, and your array pick out data from the
> form?
>[/color]


It looks like pietlin has the OP on track by sorting the data. In case that
doesn't work for him, however, I wanted to take exception to this
alternative. Since he only needs the min and max values from the array, he
really doesn't need to sort it. It would be more efficient and easier to
code to simply make a single pass through the array doing comparisons.

psuedocode:

minval = a(lbound)
maxval = a(lbound)
for i = lbound + 1 to ubound
if minval < a(i) then minval = a(i)
if maxval > a(i) then maxval = a(i)
next i

--
Randy Harris
I'm pretty sure I know everything that I can remember.

[David W. Fenton]

Re: Arrays in Access.

"Pachydermi tis" <praesagus@gmai l.com> wrote in
news:1134591237 .924265.25130@g 49g2000cwa.goog legroups.com:
[color=blue]
> By the way, if this is something you have to do often, looping
> through an array is MUCH faster than looping through a recordset.[/color]

But with a recordset, you wouldn't need to loop it to get the
answer.

Also, my bet is that the speed advantage of an array is going to
decrease the more rows you have.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Pachydermitis]

Re: Arrays in Access.

I may be mistaken, but the Microsoft code behind VB accessing a
recordset still has to loop through the database file's values to find
it's least and greatest. This code is done in some low level language
like C or Assembly so it is very fast. Not to mention you have all the
recodset management overhead that must be done before and after. To
make matters worse, the slowest recordset cursor tyeps are the ones
that support more than forwardonly movement.
An array resides in memory, so there is no file manipulation (unless
windows has wirtten it to a swap file) which is why it is so much
faster. Although you are right about the speed decreasing based on the
size of the array, the speed of accessing a recordset will also
decrease as the size of the recordset increases. I agree with you that
I would never recommend this for huge value lists, but for something
around 20, that he is using very often, it would be quite a bit faster.
Of course even if it's 100 times faster, the user probably won't ever
notice the difference between 1/1000 of second and 1/10 of a second. :)

[Rich P]

Re: Arrays in Access.

I thought I would share my humble 2cents worth. Here is a generic query
that will populate an array.

Query1 --- Select * from Table1

populate the array with values from Query1

Sub PopulateArray()
Dim RS As DAO.Recordset, i As Integer
i=0
Set RS = CurrentDB.OpenR ecordset("Query 1")
'--or Set RS = CurrentDB.OpenR ecordset("Selec t * From Table1")
Do while not RS.EOF
Redim Preserve array1(i)
array(i) = RS(0)
RS.MoveNext
i = i + 1
Loop
RS.Close
End Sub

To find the min and max values you can do this
Set RS = CurrentDB.OpenR ecordset("Selec t Max(fldx) from Table1")

to find the Min value

Set RS = CurrentDB.Opend Recordset("Sele ct Min(fldx) From Table1")

HTH
Rich


*** Sent via Developersdex http://www.developersdex.com ***

[Lyle Fairfield]

Re: Arrays in Access.

If one creates the array from an sql statement one can use joins and
subqueries to have maximum and minimum values appended to each record
and thus to each "row" in our array.
Also we can use GetRows to create the array; we must remember however
that in this two dimensional array the second element refers to the row
or record while the first refers to the field and that the array is
zero based (seemingly regardless of the Option Base setting, that is
a(7,41) refers to the value of the 8th field in the 42 record.

The code below gets all the field values from all the records from
tblCheques, appends a maximum fldAmount field, a minimum fldAmount
field, sorts not by fldAmount but by fldDate and slam dunks the results
into an array named a.

a(7,n) always returns the min amount and a(6,n) returns the max amount.
tblCheques has six fields.

Two subs: one for JET 35 and one for JET 40. 35 should work in 40 as
well, but 40 will not work in 35.

TTBOMK GetRows was a much used method in the X-Base days but is largely
ignored in both DAO and ADO. Too bad, it can be fast and powerful.

Ain't Access grand?

Sub temp40()
Dim a As Variant
Dim r As DAO.Recordset
Set r = DBEngine(0)(0). OpenRecordset( _
"SELECT * FROM (tblCheques c" _
& vbNewLine & "Left Join" _
& vbNewLine & "(SELECT MAX(fldAmount) AS maxAmount FROM tblCheques)
x" _
& vbNewLine & "ON c.fldAmount <= x.maxAmount)" _
& vbNewLine & "Left Join" _
& vbNewLine & "(SELECT MIN(fldAmount) AS minAmount FROM tblCheques
WHERE Nz(fldAmount,0) <> 0) n" _
& vbNewLine & "ON c.fldAmount <= n.minAmount" _
& vbNewLine & "ORDER BY c.fldDate", dbOpenDynaset)
With r
If Not .BOF Then
a = r.GetRows(.Reco rdCount)
Debug.Print a(.Fields.Count - 1, .RecordCount - 1)
Else
'whatever
End If
End With
Set r = Nothing
End Sub

Sub temp35()
Dim a As Variant
Dim r As DAO.Recordset
Set r = DBEngine(0)(0). OpenRecordset( _
"SELECT * FROM (tblCheques c" _
& vbNewLine & "Left Join" _
& vbNewLine & "[SELECT MAX(fldAmount) AS maxAmount FROM
tblCheques]. x" _
& vbNewLine & "ON c.fldAmount <= x.maxAmount)" _
& vbNewLine & "Left Join" _
& vbNewLine & "[SELECT MIN(fldAmount) AS minAmount FROM tblCheques
WHERE Nz(fldAmount,0) <> 0]. n" _
& vbNewLine & "ON c.fldAmount <= n.minAmount" _
& vbNewLine & "ORDER BY c.fldDate", dbOpenDynaset)
With r
If Not .BOF Then
a = r.GetRows(.Reco rdCount)
Debug.Print a(.Fields.Count - 1, .RecordCount - 1)
Else
'whatever
End If
End With
Set r = Nothing
End Sub

[Terry Kreft]

Re: Arrays in Access.

Or if you were using ADO you could do the following

SELECT
MAX(fldVal),
MIN(fldVal)
FROM
MyTable
WHERE
MyField = Condition

SELECT
fldVal
FROM
MyTable
WHERE
MyField = Condition


Pick up the maximum and minimum values from the recordset, the use
nextrecordset to get to the recordset used to fil lthe array.



--
Terry Kreft



"Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
news:1134618697 .800603.168070@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> If one creates the array from an sql statement one can use joins and
> subqueries to have maximum and minimum values appended to each record
> and thus to each "row" in our array.
> Also we can use GetRows to create the array; we must remember however
> that in this two dimensional array the second element refers to the row
> or record while the first refers to the field and that the array is
> zero based (seemingly regardless of the Option Base setting, that is
> a(7,41) refers to the value of the 8th field in the 42 record.
>
> The code below gets all the field values from all the records from
> tblCheques, appends a maximum fldAmount field, a minimum fldAmount
> field, sorts not by fldAmount but by fldDate and slam dunks the results
> into an array named a.
>
> a(7,n) always returns the min amount and a(6,n) returns the max amount.
> tblCheques has six fields.
>
> Two subs: one for JET 35 and one for JET 40. 35 should work in 40 as
> well, but 40 will not work in 35.
>
> TTBOMK GetRows was a much used method in the X-Base days but is largely
> ignored in both DAO and ADO. Too bad, it can be fast and powerful.
>
> Ain't Access grand?
>
> Sub temp40()
> Dim a As Variant
> Dim r As DAO.Recordset
> Set r = DBEngine(0)(0). OpenRecordset( _
> "SELECT * FROM (tblCheques c" _
> & vbNewLine & "Left Join" _
> & vbNewLine & "(SELECT MAX(fldAmount) AS maxAmount FROM tblCheques)
> x" _
> & vbNewLine & "ON c.fldAmount <= x.maxAmount)" _
> & vbNewLine & "Left Join" _
> & vbNewLine & "(SELECT MIN(fldAmount) AS minAmount FROM tblCheques
> WHERE Nz(fldAmount,0) <> 0) n" _
> & vbNewLine & "ON c.fldAmount <= n.minAmount" _
> & vbNewLine & "ORDER BY c.fldDate", dbOpenDynaset)
> With r
> If Not .BOF Then
> a = r.GetRows(.Reco rdCount)
> Debug.Print a(.Fields.Count - 1, .RecordCount - 1)
> Else
> 'whatever
> End If
> End With
> Set r = Nothing
> End Sub
>
> Sub temp35()
> Dim a As Variant
> Dim r As DAO.Recordset
> Set r = DBEngine(0)(0). OpenRecordset( _
> "SELECT * FROM (tblCheques c" _
> & vbNewLine & "Left Join" _
> & vbNewLine & "[SELECT MAX(fldAmount) AS maxAmount FROM
> tblCheques]. x" _
> & vbNewLine & "ON c.fldAmount <= x.maxAmount)" _
> & vbNewLine & "Left Join" _
> & vbNewLine & "[SELECT MIN(fldAmount) AS minAmount FROM tblCheques
> WHERE Nz(fldAmount,0) <> 0]. n" _
> & vbNewLine & "ON c.fldAmount <= n.minAmount" _
> & vbNewLine & "ORDER BY c.fldDate", dbOpenDynaset)
> With r
> If Not .BOF Then
> a = r.GetRows(.Reco rdCount)
> Debug.Print a(.Fields.Count - 1, .RecordCount - 1)
> Else
> 'whatever
> End If
> End With
> Set r = Nothing
> End Sub
>[/color]

[Larry Linson]

Re: Arrays in Access.

The real question, Randy, would be "What is the compelling reason for moving
the results of a query into an array?" That is something that a procedural
programmer would do as a matter of course, but not necessarily a good
approach in a database application. It took many of us some time to change
our view from the procedural programming approach to "The Access Way", but
once we get accustomed to "The Access Way", database applications become
much easier.

I can think of several approaches to retrieving the Max and Min... a totals
query, the DMAX and DMIN functions, for example... in addition to the ways
already mentioned. And all a LOT easier than filling and sorting an array
(and likely as fast, or faster, to execute).

Larry Linson
Microsoft Access MVP


"Randy Harris" <randy@SpamFree .com> wrote in message
news:3R%nf.4154 1$6e1.13625@new ssvr14.news.pro digy.com...[color=blue]
>
> "Steve" <theonesteve@gm ail.com> wrote in message
> news:1134590993 .741788.232710@ o13g2000cwo.goo glegroups.com.. .[color=green]
>> pietlin makes a good point. If you don't order the SQL query, you'll
>> need to a) sort your array (bubble sort would be efficient, since it's
>> only 20 elements), or b) write your array to a temporary table and run
>> a query off of the temporary table (which begs the question of "why
>> didn't you just sort the original query to start with?")
>>
>> If your situation is different than I am understanding it to be, can
>> you show how your array gets its values from the SQL query? I.e., does
>> the SQL query feed into a form, and your array pick out data from the
>> form?
>>[/color]
>
>
> It looks like pietlin has the OP on track by sorting the data. In case
> that
> doesn't work for him, however, I wanted to take exception to this
> alternative. Since he only needs the min and max values from the array,
> he
> really doesn't need to sort it. It would be more efficient and easier to
> code to simply make a single pass through the array doing comparisons.
>
> psuedocode:
>
> minval = a(lbound)
> maxval = a(lbound)
> for i = lbound + 1 to ubound
> if minval < a(i) then minval = a(i)
> if maxval > a(i) then maxval = a(i)
> next i
>
> --
> Randy Harris
> I'm pretty sure I know everything that I can remember.
>[/color]

[David W. Fenton]

Re: Arrays in Access.

"Pachydermi tis" <praesagus@gmai l.com> wrote in
news:1134607867 .065420.220110@ z14g2000cwz.goo glegroups.com:
[color=blue]
> I may be mistaken, but the Microsoft code behind VB accessing a
> recordset still has to loop through the database file's values to
> find it's least and greatest. . . .[/color]

Eh? If you're using a recordset, you'd use GROUP BY and Max() and
Min(), so there wouldn't be an looping needed at all.
[color=blue]
> . . . This code is done in some low level
> language like C or Assembly so it is very fast. Not to mention
> you have all the recodset management overhead that must be done
> before and after. To make matters worse, the slowest recordset
> cursor tyeps are the ones that support more than forwardonly
> movement. An array resides in memory, so there is no file
> manipulation (unless windows has wirtten it to a swap file) which
> is why it is so much faster. Although you are right about the
> speed decreasing based on the size of the array, the speed of
> accessing a recordset will also decrease as the size of the
> recordset increases. I agree with you that I would never
> recommend this for huge value lists, but for something around 20,
> that he is using very often, it would be quite a bit faster.[/color]

SQL aggregate functions will always be faster than looping either a
recordset or an array.
[color=blue]
> Of course even if it's 100 times faster, the user probably won't
> ever notice the difference between 1/1000 of second and 1/10 of a
> second. :)[/color]

That's not the point. For any process where it's theoretically
possible for the number of items to change, You sould always use
the
most scalable approach, even if right now there is no noticeable
speed increase.

[this is not at all a contradiction of my perennial point that not
all performance optimizations have a real-world effect]

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/