Query Help

[Secret Squirrel]

I need to figure out a way to update a record based on the totals of another
record.

Here's my dilema:

Order Date OrderNo LineNo RelNo Qty MultiRel

1/1/09 330055 1 0 10000 True
1/1/09 330055 1 1 5000 True
1/1/09 330055 1 2 2500 True

What I need to do is first find all orders that have a "MultiRel" value of
'True'.
Then total the "Qty" for that "LineNo" within that "OrderNo" except for the
"LineNo" that has a "RelNo" of '0'. Then I need to find the difference
between that total and the "RelNo" 0 quantity for that "LineNo" and if it's
less than the RelNo 0 total then update that RelNo 0 record in my table with
the difference. And then change the 0 to '99'.

So my result should look like this:

Order Date OrderNo LineNo RelNo Qty MultiRel

1/1/09 330055 1 99 2500 True
1/1/09 330055 1 1 5000 True
1/1/09 330055 1 2 2500 True

And ideas where I should begin?

SS

 

[pietlinden]

I need to figure out a way to update a record based on the totals of another
record.

Here's my dilema:

Order Date   OrderNo   LineNo   RelNo  Qty       MultiRel

1/1/09         330055     1           0         10000   True
1/1/09         330055     1           1         5000     True
1/1/09         330055     1           2         2500     True

What I need to do is first find all orders that have a "MultiRel" value of
'True'.
Then total the "Qty" for that "LineNo" within that "OrderNo" except for the
"LineNo" that has a "RelNo" of '0'. Then I need to find the difference
between that total and the "RelNo" 0 quantity for that "LineNo" and if it's
less than the RelNo 0 total then update that RelNo 0 record in my table with
the difference. And then change the 0 to '99'.

So my result should look like this:

Order Date   OrderNo   LineNo   RelNo  Qty       MultiRel

1/1/09         330055     1           99        2500     True
1/1/09         330055     1           1         5000     True
1/1/09         330055     1           2         2500     True

And ideas where I should begin?

SS

can you cheat and use DSUM to do the totals?

 

[Secret Squirrel]

Not sure. How would I use it and have it total each line item for every order
without using the 0 RelNo?