Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Add column for a Running total for PO Amount Remaining or Outstanding Balance yet to be Invoiced

Status
Not open for further replies.

awaria

IS-IT--Management
Sep 16, 2010
100
US
The result set has detailed and header data elements related to PO and Invoices.
Requested to have column added to results set for PO_Amt_Remaining.

Not sure how to accomplish that data request. The result set has over 40 columns so I didn't know how to, or want to group on all those columns, but, will try any recommendations.

Query run after invoice 3:

PO_Nbr PO_Amt Inv_Nbr Inv_Dt Inv_Amt PO_Amt_Remaining

1010 $1,000 1 1/1/18 $100 $500
1010 $1,000 2 2/1/18 $100 $500
1010 $1,000 3 3/1/18 $300 $500

as another invoice assoc with PO is paid , the PO_Amt_Remaining would decrement for all invoices in the record set when the query is executed after invoice 4

PO_Nbr PO_Amt Inv_Nbr Inv_Dt Inv_Amt PO_Amt_Remaining

1010 $1,000 1 1/1/18 $100 $300
1010 $1,000 2 2/1/18 $100 $300
1010 $1,000 3 3/1/18 $300 $300
1010 $1,000 4 4/1/18 $200 $300

Thank you, awaria
 
Join a Sub Query that sums the INv_Amt and groups by PO_Nbr.
You can then join this subquery to your Dataset on PO_Nbr and then simply subtract subquery total from PO_amt for each record.

Ian
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top