Recordset comparing duplicates

[Ebes1099]

I have a query to find duplicate values in one of my tables. I open a recordset on this query and I want to compare groups that have multiple records.

The reason a group would have multiple records is that it is shifting to a new product. Each group has a group number, product, and membership values for 12 months. So I could have group 12345 with product AB123 and membership values for the first 6 months, then on the next line in my query I would have group 12345 with Product XY789 and membership in the last 6 months. In this case, I have a field in my original table that says, EndProduct, and in the record where the product is AB123, I put XY789 in the EndProduct so I know that group is moving to that product.

So what I want to try and do in the recordset is go through each group in the query, find all the rows that are for the same group, and look at the last month of membership. If one has data and one doesn't, I want to put the Product for the row that has data into the EndProduct field on the row that doesn't have data in the last month of membership. I'm pretty sure I can figure all that stuff out, what I don't know how to do in the recordset is how to look at data from both groups at the same time to compare them.

Would I need sometime like...(I'm not bothering with declarations)

For a=0 to totalRows
    GroupNumber = rst.Fields("GroupNumber")
    Product1 = rst.fields("Product")
    LastMonthMbrship = rst.Fields("DecMbrs")
         Find matching group by the group number
             LastMonthMbrship2 = new group's DecMbrs
             Product2 = new group's product
    If(LastMonthMbrsip > 0 AND LastMonthMbrship2 = 0) Then
         rst.fields("EndProduct") = Product1
    ElseIf (LastMonthMbrship2 > 0 AND LastMonthMbrship = 0) Then
         rst.Fields("EndProduct") = Product2

I can already see a problem. When I set rst.Fields("EndProduct") it doesn't know which record to put it in. How do I tell it the right one?

Also, I know there would need to be rst.Edit and rst.Update in there but I'll do that when I get the logic figured out.

 

[Ebes1099]

I had an idea, not sure if it's right or if it'll work and even if it does I still have a couple questions.

If I create 2 recordsets on the same query. Then I have that loop that goes through the first recordset and for each group I do a FindLast on the second recordset to get the data from the 2nd group. Then I can determine which EndProduct to put the data in based on which recordset.fields I use. I'll use rst1.Field("EndProduct") if product 1 is moving to product2 or vice versa.

The one problem I see and would need help working out is that in recordset 1 when I'm going through all the groups, once I do a group, I don't want the 1st recordset to go to the 2nd instance of that group in the recordset and try and find a matching group again. How can I skip a record in Recordset 1 if I've already looked at it in Recordset2?

 

[MazeWorX]

Set your first record set to loop through as groups only ie select distinct. that way your only looping through each group once. something like

Do Until rs1.EOF
With rs2
'do something to rs2
End With
rs1.MoveNext
Loop

Thoughts

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]