Recordsets

[Ebes1099]

I have a question about something I'm about to start doing. I'm not exactly sure about one thing. I have a table with rows of groups in it. Each group has a Group Number and a package and a package2 field. I'm going to want to go through each row and find all the groups that have matching Group Number and Package 2's. That mean's I need to combine those rows. There could be 2 rows or 3 rows that need to be combined.

I feel like if there's 2 rows it won't be a problem because I can just use 2 recordsets going through from top to bottom on the first recordset, and using the 2nd recordset to do a FindLast and then performing all the math necessary to combine the 2 records into 1 row in a new table. The problem is when there's 3 records, I don't know how I'll know if there's 3 and if there are how to find all 3 and perform the math to combine them?

What would the best way be to do this?

 

[lameid]

I'm not real clear on what you mean by combine. If you mean sum, you could group by the two fields and sum. If you mean to combine text (concatenate) or some other non query action, you just need to detect when the values change.

Open a recordset using a query that sorts (order by) the two field.

Then set a variable for each field.

Compare the values as you loop through the records, if they are the same keep combining in a third variable, if different write (add) the combined record to the target table and update your test variables.

That's it in a nutshell.

 

[Ebes1099]

It's not quite that simple. The data in each record is Claim Dollars and Members. The members are simple to just add together. But the claim dollars need to be adjusted depending on the Package for each group. Some packages have higher benefits so the claims for that need to be adjusted to match the package that group is moving to. I'm pretty sure I can figure out that part though.

I'm having a problem with finding every record in the table. There can be 6 instances of a group in the table. Say I have Group 12345. They'll have a line with Package AB12 with claims and membership data, then they'll have a line with package XY89 with claims and membership data. Then in the Package2 field, both have the value AB12 because Package XY89 is moving to AB12. So I need to take the claims dollars from XY89, multiply them by a factor to get them in the terms of AB12 value and then add them together. I can do this with 2 recordsets and I don't seem to have a problem.

My problem is if there's 3 instances of group 12345. It has packages AB12, XY89 and ZZ23, and they are all moving to AB12. I need to then factor XY89 and ZZ23 to get them in terms of AB12 value, then add them all up into 1 row. (And there could be 4 or 5 instances of a group moving to a package, but if I figure out 3, the rest should follow suit I'm assuming).

I hope that clears it up a little bit. If not, let me know what you're still confused about and I'll try and explain more. Thanks for the help.

 

[lameid]

I think I had the right starting place with detecting the change.

I am not sure the specifics of how you want to apply factors... Is each a line item with its own factor or are you doing some sort of weighted distribution?

Start with writing the loop that will sum your charges for each group and then go back and think about how to do what it is you need to do.

I can't tell if you need one or two passes through the data... I mean on change of the key or two fields, you could move to the first matching record, and update them based on the first pass... Or open a recordset to tell you what you need to know as you work with each set.

For me to follow what you mean, you are going to have to show some example data, some expressions or logic and maybe the expected resultant data.

 

[Ebes1099]

I attached the code I'm using. I created a couple recordsets to try and go through. There's 5000+ groups in the ClaimsInputs2 table. I should get about 3200 groups in the output table.

Right now that code runs through and puts about 660 groups in the output table and I get a "No Current Record" error in that first While loop. I must be moving through the records in rst1 incorrectly. I see that it's missing copying over some groups.

To answer your other questions, I'm applying a weighted factor from a lookup of the relative values of the packages. If you wouldn't mind taking a look at that code and possibly helping me out that would be great. Thanks.

 

[Ebes1099]

I notice a couple errors and changed them quick. It now goes through and gives me 3208 records which is good. But I'm still getting a No Current Record error in that first while loop which is weird because when I click debug and hover over the Do Until rst1.EOF it says that returns True. And I look at what Group Number it's returning and it's the last record in the file.

Anyway, I updated the code, that old link should still work.

 

[lameid]

This loop below does not test for the EOF... that is why it is hitting an error.

        While (rst1.Fields("GroupNumber") = tmpGrp And rst1.Fields("Package2") = tmpPkg)
            rst1.MoveNext
        Wend

 

[Ebes1099]

So if I add this in...

            While (rst1.Fields("GroupNumber") = tmpGrp And rst1.Fields("Package2") = tmpPkg)
                If (Not rst1.EOF) Then
                    rst1.MoveNext
                End If
            Wend

 

[lameid]

... You will have an infinite loop.

You only want one loop to move through the recordset... Use an if statement that matches your while comparison in this snippet to conditionally do something.

 

[Ebes1099]

That code didn't work either. I get the error on the While statement.

I'm guessing since my outer loop is the Do Until rst1.EOF that it's the inside part of the While loop where rst1.MoveNext is that's moving the recordset past the end of the file. But then why wouldn't that if statement inside the code work?

What can I do to prevent this error? I'm confused.

 

[Ebes1099]

The thing is that most of the Groups in the recordset have multiple instances. And I don't want to look at the same combo of GroupNumber-Package2 twice. I have the table sorted first by GroupNumber then by Package2.

So I have my outer loop going through the table 1 record at a time, when it moves down 1 record it checks to see if the GroupNumber and Package2 are the same as the group before (Those values are stored in tmpGrp and tmpPkg). If they are the same, it moves rst1 to the next record and checks again. If they are different now, it goes through the rest of the code. If not (meaning there were 3 instances of that groupnumber-package combo) it moves again, and so on until it gets to a new groupnumber-package combo.

It seems to work fine through the whole recordset because I get the correct number of rows in my output table, my problem is just when it gets to the last record. The last 2 records in the recordset have the same groupnumber-package combo. The recordset hits the first one, then goes through the code and combines them for the output table. Then the recordset moves to the last record, sees that it has the same GroupNumber-Package combo as the record it was just on and moves to the next record (which is past the end of the file). Now it goes back to the While loop and tries to compare the record and realizes the recordset isn't on a current record and I get the error.

You probably understood all this before I typed it out. I still don't get what you're saying. If I only have 1 loop going through the recordset, how do I accommodate for this situation? (When there are 3 instances of the same GroupNumber-Package in a row and I want to skip over the second 2 with rst1)

 

[Ebes1099]

Ok I figured it out, I think.

I just did this...

            While (rst1.Fields("GroupNumber") = tmpGrp And rst1.Fields("Package2") = tmpPkg)
                rst1.MoveNext
                If (rst1.EOF) Then
                    Exit Do
                End If
            Wend

That seemed to work.