Build table in Access using VBA Array

[kayleen]

In Access, I am trying to build an Array from a recordset to then write to a table
The table will then be used to export records to Excel to Chart a Frequency Distribution

The recordset (rstHistogram) has one field [PayloadWeight]

The table needs 2 fields:
Fld1 (Category) Fld2 (Sum of all the PayloadWeights in that Category]
eg.
Under 10 0 (records in the recordset)
10 to 20 0 "
20 to 30 5 "
etc. up to 21 categories


Is it possible to build an array with the following syntax?
The code is stopping at the "Sum(" saying variable not defined.
I have tried "(Sum(".

Am I completely on the wrong track?

With rstHistogram
'
If [PayLoadWeight] < 10 Then
aryCategory(1) = Sum(IIf([PayLoadWeight] < 10, 1, 0))
ElseIf [PayLoadWeight] >= 10 And [PayLoadWeight] < 20 Then
aryCategory(2) = Sum(IIf([PayLoadWeight] >= 10 And [PayLoadWeight] < 20, 1, 0))
ElseIf etc.

Appreciate any help.
Thanks
Kayleen

 

[MajP]

If your categories do not change, this would be easy. Build 21 little queries. They are real short, and should take 5 minutes to do. It is just a cut and paste job to change Name and the where critiera. Here are the first three.

qryLessThan10
SELECT Sum(tblPayloadWeight.payloadweight) AS SumOfpayloadweight
FROM tblPayloadWeight
WHERE tblPayloadWeight.payloadweight <10

qry10To20
SELECT Sum(tblPayloadWeight.payloadweight) AS SumOfpayloadweight
FROM tblPayloadWeight
WHERE tblPayloadWeight.payloadweight>=10 And tblPayloadWeight.payloadweight<20

qry21To30
SELECT Sum(tblPayloadWeight.payloadweight) AS SumOfpayloadweight
FROM tblPayloadWeight
WHERE tblPayloadWeight.payloadweight>=20 And tblPayloadWeight.payloadweight<30

Here is one way then to load the array. There is probably some better ways.
aryCategory(1) = nz(dfirst("SumOfpayloadweight","qryLessThan10"))
aryCategory(2) = nz(dfirst("SumOfpayloadweight","qry10To20"))
aryCategory(3) = dfirst("SumOfpayloadweight","qry20To30")

Except for the "dfirst" this will run pretty fast. There is probably a way to do this in one Step in a query. You could post this in the Query forum and ask if there is a way to do this in one step in a Query.

 

[MajP]

Here is a much more efficient way. Build a function like this, and the end result is a query that can be exported to excel. No arrays:

Public Function wghtCategory(payLoadWght As Double) As String
  Select Case payLoadWght
    Case 0 To 9.9999
      wghtCategory = "1_0to10"
    Case 10 To 19.999
      wghtCategory = "2_10to20"
    Case 20 To 30.999
      wghtCategory = "3_20to30"
    'add more cases
  End Select
End Function

use this function in a query

qryPayloadCategory

SELECT tblPayloadWeight.ID, tblPayloadWeight.payloadweight, wghtCategory([payloadweight]) AS Category
FROM tblPayloadWeight;

Now make a crosstab query

qryPayLoadCategory_Crosstab

TRANSFORM Sum(qryPayLoadCategory.payloadweight) AS [The Value]
SELECT qryPayLoadCategory.ID
FROM qryPayLoadCategory
GROUP BY qryPayLoadCategory.ID
PIVOT qryPayLoadCategory.Category;

now make an aggregate query

qryHistogram

SELECT Sum(qryPayLoadCategory_Crosstab.[1_0to10]) AS 0to10, Sum(qryPayLoadCategory_Crosstab.[2_10to20]) AS 10to20, Sum(qryPayLoadCategory_Crosstab.[3_20to30]) AS 20to30
FROM qryPayLoadCategory_Crosstab;

And there is your answer. This will be far more efficient than your code.

 

[MajP]

Duh. I had it in my mind that you wanted to do columns not rows. That is a lot easier. Build the function like I said. Then here is your table with 2 columns: Sum, and Category Label

SELECT Sum(tblPayloadWeight.payloadweight) AS SumOfpayloadweight, wghtCategory([payloadweight]) AS Category
FROM tblPayloadWeight
GROUP BY wghtCategory([payloadweight])

Also you could just export your original table, and use the excel Data analysis add-in to do your histogram. Sorry about the confusion. Now that is efficient and simple.