How to segregate multiple recordsets from a main table/recordset? 1

[Jawad77]

I have a main table/recordset. The recordset has composite primary key that consists of two fields "Product Group" and "Patch Area". There are 368 different combinations of these fields. I like to create 368 different recordsets so that I could run an independent cumulative sum function on each of the recordsets. Could you please help me with how to code this in VBA Access. I have the generic idea but really don't know the syntax of carrying this out. Thanks!

 

[DoubleD]

I'm probably misunderstanding your requirement, but why not just write an aggregate query?
If I understood your need a little more clearly, I can help you write the code.

I am what I am based on the decisions I have made.

DoubleD

 

[Jawad77]

I am sorry, I guess I was not very clear on my requirements. I have a table that has 5 different fields:

Product ID, Area ID, Item ID, Sales%

I have 368 different combinations of Product ID & Area ID in my table.

Prodcut ID Area ID Item ID Sales %

1 1 1 30

1 1 2 30

1 1 3 20

1 1 4 20


For Each, Product ID/Area ID combination, I will like to do the following analysis:

I like to have a separate calculated column (called cum%sales) for each Product/Area Combination. I like to filter only those items for that are above the 80% cumulative sales% mark. In the above example, I would like to see only items 1,2 and 3 because they make top 80% of the sales for that Product/Area Combination.

I can do this analysis in VBA for one Product/Area combination but I don't how to do it for more than one becasue my main table has 368 Product Area Combinations. Each time we move on to a different Product/Area combination, the sumulative sum should reset to zero and we start calculating it again for the given Product/Area combination.

What I was thinking and I might be completely off on this that If we could create 368 different recordsets from the main table in VBA and then use each recordset to calculate the cum Sales%. But I have no clue how to do it is VBA. I just started programming in VBA Access. Thanks a lot for your reply. I hope you could help me on this.

 

[DoubleD]

Do you need to look at several Product/Area Combinations at the same time? If so, then I agree that you may need VBA recordsets. If you will only be analyzing one at a time, which seems more likely to me, then it's just a matter of writing the appropriate query to show the cumulative totals.

This isn't the exact code, but it should get you a pretty good start.

Sub Top80Pct()
Dim rsSource As New ADODB.Recordset
Dim rsResult As New ADODB.Recordset
Dim strKey As String
Dim intTotal As Integer

strKey = ""
intTotal = 0
rsSource.Open "OriginalTableName", CurrentProject.Connection
Do While Not rsSource.EOF
  If intTotal < 80 Then
    If strKey = rsSource!ProductId & "-" & rsSource!AreaId Then
      intTotal = intTotal + rsSource!SalesPct
    Else
      intTotal = rsSource!SalesPct
    End If
    rsResult.AddNew
    rsResult.Fields("ProductId") = rsSource!ProductId
    rsResult.Fields("AreaId") = rsSource!AreaId
    rsResult.Fields("ItemId") = rsSource!ItemId
    rsResult.Fields("SalesPct") = rsSource!SalesPct
    rsResult.Update
  End If
  strKey = rsSource!ProductId & "-" & rsSource!AreaId
  rsSource.MoveNext
Loop

End Sub

I am what I am based on the decisions I have made.

DoubleD

 

[Jawad77]

Thanks a lot Buddy!