Arrays

  • Thread starter Thread starter Derek
  • Start date Start date
D

Derek

I have a group of 12 numbers As bellow:
1
2
3
4
1
2
5
7
2
3
2
2
I want to be able to count how many numbers are the same
in the format

1=2
2=5
3=2
4=1
5=1
7=1
How?
 
Derek said:
I have a group of 12 numbers As below:
Click to expand...

Is that in a table? Then create a Totals query, grouping on the number,
and counting the number (in two columns)
 
Hi,



Something like (VBA6) -- untested -- :

Public Function Counting( X() As Long ) As Long( )
Dim Result() As Long
Dim i As long
Dim ub As long
Dim ux As Long

On Error Resume Next
Redim Result(0 To 0) As Long
ub = 0 ' upper limit for Result( i )
ux=UBound(X)

For i = LBound(X) TO ux
If X(i) > ub then
Redim Preserve Result(0 to X(i)) As Long
ub=X(i)

End If
Result(X(i)) = Result(X(i)) + 1
Next i

Counting = Result
Debug.Assert 0=Err.Number
End Function




I stored UBound(x) in a variable, ub, to avoid using the function
UBound inside the loop, because it is a function which is little bit slow. I
didn't provide code to check that X() is an array with (just) one dimension.


Hoping it may help,
Vanderghast, Access MVP
 
Thanks guys

Michel I'm sure yours is the answer but looks a little
advanced for me. a bit more explanation would be
greatfully appreciated.
I have to put results into fields on the form as in:
result 1 = txtTotal but I'm sure i'm realy pushing the
limmit now.

Thanks again.
 
Hi,


The algorithm is like this: read a value, X(i), that is a "bucket"
number. We just have to increase the actual "count" in that bucket by one:

Result( X(i) ) = Result( X(i) ) + 1

and if the bucket does not exist in your collection ( X(i) > larger bucket
number we got), we "add" it with the Redim Preserve (Preserve is required
to keep the "count" in the existing buckets).

In the end, Result( j ) got the number of time we have read a value
in X() that was equal to j.

VB6/VBA6 allow array assignment if the left array is redim-able:

Dim z(1 to 6 ) As Long
Dim a( ) As Long

z(1)=4
z(2)=5
...
a=z ' the copy occur here


If you have to put back each values in a control ( a control belong to a
form, a field belong to a table), it may be preferable to start with a table
and to use the method suggested by Bas Cost Budde, and to display the result
in a continuous form (or sub-form).




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top