two dimensional array

[adcpqc]

Hi! I am a beginner user of Access VBA. I need help in storing records in a table to a two dimensional array. I'm doing a withholding tax calculation for a payroll system. I have a table for a withholding taxes which I would like to store the table records in an array. Thanks in advance.

 

[Jacob Mathai]

I used a code like this. This may not be the best method. But it
worked for me.

Private Sub Command13_Click()
Dim myarray(), ub As Long, i As Long
Dim rst As Recordset, strsql As String
Dim dbs As Database

Set dbs = CurrentDb

strsql = "SELECT name,salary from testtabl order by name"
Set rst = dbs.OpenRecordset(strsql)
rst.MoveLast
'MsgBox "Recordset contains " & rst.RecordCount & " records"
ub = rst.RecordCount ' upper bound

ReDim myarray(ub, 2) ' 2 columns
rst.MoveFirst ' go to top

For i = 1 To ub
'MsgBox "Recordset contains " & rst(0) & " " & rst(1)
myarray(i, 1) = rst(0) ' column 1
myarray(i, 2) = rst(1) ' column 2
rst.MoveNext
Next i

rst.Close
Set dbs = Nothing
End Sub

 

[mte17]

Although I'm a proficient programmer in Access, I'm short on experience with arrays and frankly not sure of when or where they should be used. What's your rationale for using an array for your calculations?

 

[adcpqc]

Here is the scenario of my calculation. I need to compute an employee withholding tax based on his filing status-either single or married and his weekly taxable wages.
I have a tblSingleTax table which compose of the following fields and record values .
This tax table need to store in a table to become updatable anytime when there is new tax rate.

fldNotOver fldBase fldPercentage fldExcessOver
51 0 0 0
517 0 15% 51
1,105 69.90 28% 517
2,493 234.54 31% 1,105
5385 664.82 36% 2,493
0 1,705.94 39.6% 5,385


Now assume that an employee has a taxable wages for a week of $600 and he is a single.
We scan the column fldNotOver , stopping the search when we encounter the first value that is greater than or equal to the taxable wages.
The computation of his withholding tax as follows:

Taxable wages $600.00
Less:Excess Over (517.00)
------------
Subtotal1 83.00
Multiply: Percentage * .28
------------
Subtotal2 23.24
Add: Base + 69.90
----------
Withholding Tax 93.14

How can I store the tax table into an multidimensional array? If you have a better solution other than using an array, much better. Please tell me how to do it with a code example.
Thanks.