Arrays

[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?

 

[Bas Cost Budde]

I have a group of 12 numbers As below:

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

 

[Michel Walsh]

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

 

[Derek]

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.

 

[Michel Walsh]

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