[AccessD] Is a cursor the way to go

I see what you're trying to do, but why are you trying to do it?  Does
"Allocated" mean paid or to be paid?  Or are you trying to figure out what
should be paid and what left out?  What logic would be applied?  Would the
largest  Amount receive it's allocation first?  Would they go in date
order?  It feels like there is a piece missing in this logic.

Charlotte Foust

On Fri, May 25, 2012 at 5:35 AM, Paul Hartland <paul.hartland at googlemail.com
> wrote:

> 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
>