Function to loop thru multiple records with same id to calculate on

[ciskris]

Am having trouble building a function to loop through a recordset that contains records that can have one or more billing codes under the same patient number. I need to calculate 100% of the highest paying amount, 50% of the second highest and 25% on all remaining amounts. I have a Do While Not rs. EOF but am having trouble building a counter to count through the patient number records and then start over the calculations for the next patient number. Any guidance would be appreciated.

 

[MichaelRed]

A classic (Relational) db soloution might be to run the process using a parameter for the PatientID, which is obtained as a (unique) recordset from the table. Otherwise I would want more info on the process before proceeding. Like where are the calcs being saved/used, what constitutes a billing code, what is a paying ammount ...



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ciskris]

I am pulling the patient id, proc code and pay amount with a query and then adding a new field in the query with the module that contains the loop. My new query field would look something like this: payoutamt: pay([patID],[payamt]).
The calculations on the pay amount would possibly be saved to a table as a make-table query. The billing code is just a 5 number identifier. The pay amount is any dollar amount for the procedure like $689.77. It is a currency field in the table. Do I need to put an intCounter in the module code to count billing codes under the patient id and while it is under a certain patient id, if intCounter = to 1 then payamt * 1.0 else intCounter = 2 then payamt * .50 and so on. This is what I am struggling with in the coding process.

 

[MichaelRed]

Hmmmmmmmmm,

I think you should have an aggregate query, where you group on PatientId and BillingCode, Summing the PayAmt and ordering by the summed ammount. The result should have the more or less raw info without any looping.

If this is a make table query, then you need to process the new table as a recordset with your counter. The counter needs to be re-set for each PatientId, and the calc is pretty much as you stated, although I would use a Select Case intCounter for the processing.

Assuming the process is proceeding along these lines, you would need to also have a field in the table to hold the payamt. This can/should be created in the make table query by havng the alias name with the constant value of 0 (zero). Have the calc just update the field from the calc.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over