Recordset and loop

[larochy]

Hi,

Wondering if someone can provide some guidance. I have a database that
tracks customer purchases of consulting hours and the usage of those hours by
the Customer. A customer can have multiple purchases for blocks of time. In
any given month they use their consulting hours and they are expired at
different rates depending on who they utilized (Consultant = $200/hr, Project
Manager = $225/hr, etc.). My problem is I’ve never used a recordset before
and when recognizing the hours, I’m trying to use a FIFO method in which the
hours they use are recognized against the oldest hours that they’ve purchased
which is determined by the Purchase Date. Is there some psuedo code or an
example of what the code would look like so I could go line by line
recognizing the hours and have the balance updated after each line is
recognized? I know this is a little vague but I’m just looking for a basic
outline of what the code would look like.

Thanks

 

[Graham Mandeno]

Hi larochy

I don't think there is any need to store the balance anywhere in your
database. It seems to me that it can be calculated simply from:
Sum( PurchaseAmount) - Sum( HoursUsed * Rate )

 

[larochy]

Thanks for the response Graham but I'm not storing the balance in my
database. The hours have to be recognized against a certain "bucket" of
hours that the customer has purchased with each bucket having a unique
identifier. The consulting hours used each month are imported into a temp
table and I need to go through each line in the temp table one by one to
assign the correct bucket of hours that each line should be recognized
against. I'm trying to find a way to do this using some sort of recordset
and loop function where it assigns the oldest available bucket of hours
against the current hours used. Each line in the temp table is considered a
Transaction that has to be recognized against a bucket of hours that the
customer has purchased.