Solved: Multidim RecordSets?

[afh110]

Hi guys,

Im looking to query a table and expecting to get back multiple rows! I need all these rows in a recordset of some kind (or a Dataset) so i can manipulate the data with some aggregations. I looked up the ADOMD library, but I don't know how to go about querying a table and loading a bunch of rows in one ADOMD object?

[OBP]

Why not do the aggregations in the Query, or a second query?

[afh110]

Tell me how :)!

I have a complex query that results with the following datasheet:

[Key] [Weight] [Value]
1 .20 100
2 .30 80

I would like to get the sum of 1st (Weight x Value) + 2ndRow(Weight x Value)

:)

[OBP]

Are there just 2 records in the query?

[afh110]

No, the number of rows changes according to an input variable.

Isnt there a dataset object in VBA Access?

[CreganTur]

Just use an ADO recordset. There are a ton of great articles online on how to create and use recordsets- just google it.

[afh110]

Ok so my lack of understanding of recordsets was the reason for this problem. I figured out that when you query a table as a whole, you can run through the different rows by using the "rs.movenext" object! so i re wrote my code to the one below! BUT i have one problem :)..when I try to get a record count, Im always getting a count of 1 although it query should be returning 3 rows! any ideas people :)


Function Aggregate_KPI(Assoc As String, DataTbl As String, Month As String) As Integer
Dim SqlStr As String
Dim dB As DAO.Database
Dim rs As DAO.recordset
Dim Sum As Integer
Dim Key As String
Dim i As Integer
Dim MonthValue, Weight As Integer
Dim NumOfKPIs As Integer

Sum = 0
i = 0
Key = ""

SqlStr = "SELECT KPI_Def.Master_Key, KPI_Def.Weight ," & DataTbl & "." & Month & " FROM " & DataTbl & " INNER JOIN KPI_Def ON " & DataTbl & ".Master_Key = KPI_Def.Master_Key WHERE KPI_Def.Association='" & Assoc & "';"
Set dB = CurrentDb
Set rs = dB.OpenRecordset(SqlStr, dbOpenDynaset)
NumOfKPIs = rs.RecordCount '<-----------Here im always getting the number 1 ! I should be getting 3

For i = 0 To NumOfKPIs - 1
Key = rs(0)
Weight = rs(1)
MonthValue = rs(2)
Sum = Sum + MonthValue * Weight
rs.MoveNext
Next i
rs.Close
Aggregate_KPI = Sum
End Function\0

[OBP]

Use rs.movelast and then rs.movefirst to get the correct recordcount.

[afh110]

Ok so this is my final code.. I used a While loop instead of a for loop and it worked !! :) . Frankly i dont understand the difference but it works to say the lease. Thank you all :)



Function Aggregate_KPI(Assoc As String, DataTbl As String, Month As String) As Integer
Dim SqlStr As String
Dim dB As DAO.Database
Dim rs As DAO.recordset
Dim Sum As Integer
Dim Key As String
Dim i As Integer
Dim MonthValue, Weight As Integer
Dim NumOfKPIs As Integer

Sum = 0
i = 0
Key = ""

SqlStr = "SELECT KPI_Def.Master_Key, KPI_Def.Weight ," & DataTbl & "." & Month & " FROM " & DataTbl & " INNER JOIN KPI_Def ON " & DataTbl & ".Master_Key = KPI_Def.Master_Key WHERE KPI_Def.Association='" & Assoc & "';"
Set dB = CurrentDb
Set rs = dB.OpenRecordset(SqlStr, dbOpenDynaset)

While Not rs.EOF
Key = rs(0)
Weight = rs(1)
MonthValue = rs(2)
Sum = Sum + MonthValue * Weight
rs.MoveNext
Wend
rs.Close
Aggregate_KPI = Sum
End Function\0
\0\0
\0\0\0
\0\0\0