[AccessD] Is a cursor the way to go

Not sure, but I have found that particularly good cursors will tell you where to go... quite colorfully.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland
Sent: Friday, May 25, 2012 8:35 AM
To: SQLServerList; Access List
Subject: [AccessD] Is a cursor the way to go

To all,

I will try and explain the problem as best as I possibly can, for this purpose lets say I have two tables, PODetail which holds detail records for a purchase order, and a table containing a value which we have currently allocated to a single purchase order number POAllocation

PODetail
PoNumber    Amount     Allocated    RowID
123456          50.00             0              1
123456          20.00             0              2
123456          76.00             0              3

POAllocation
PONumber    Allocated
123456          141.00

What I need to do is update the Allocated field in PODetail with a value, the way I want to do this is get the PO number and allocated amount from POAllocation and then loop through the PODetail records updating the Allocated field, so I would come to record (rowid) 1 if the allocated amount is less than 50.00 then update the allocated field with that value, if the allocated amount is great than 50.00 then update the allocated field to 50.00, take 50.00 away from 141.00 to get 91.00 then report the process for record (rowid) 2.

I am thinking/in middle of writing a cursor inside a cursor, anyone else any better ideas on how to do this.

Thanks in advance for any help.


--
Paul Hartland
paul.hartland at googlemail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com