Calculation problem - keeping a running total

S

scatman

I have 2 tables I'm using for a simple item shipment report.
Our custmers order X number of pieces on one purchase order, then get
shipments throughout the year against that PO.

tblOrder has:
Customer
OrderNo
PONo
PartNo
QtyOrdered
Unit
UnitPrice

tblShipments:
OrderNo
InvoiceNo
DateShipped
QtyShipped

The report I'm trying to create is used as a Shipment Log that is
attached to the cover of the each order file. It has (in the group
header) -
------------------------------------------------------------------------------------
OrderNo PartNo PONo QtyOrdered Unit
ex:
13804 2562-16 152480 450,000 Pcs
------------------------------------------------------------------------------------
Each detail line is/should be-

InvoiceNo DateShipped QtyShipped RemBalance
ex:
73150 6/10/06 120,000 330,000
74025 6/12/06 50,000 280,000


My question is..... how can I get the RemBal computed for each line in
the report?
I need a running total as opposed to a "grand total"
The first line could use [QtyOrdered] - [QtyShipped], but that wouldn't
work for subsequent lines.
Could I have a "work field" (that would not appear on the report) that
would keep a running total of shipments and subtract that from
QtyOrdered, or is there another way?

Thanks.
 
J

Joseph Meehan

I have 2 tables I'm using for a simple item shipment report.
Our custmers order X number of pieces on one purchase order, then get
shipments throughout the year against that PO.

tblOrder has:
Customer
OrderNo
PONo
PartNo
QtyOrdered
Unit
UnitPrice

tblShipments:
OrderNo
InvoiceNo
DateShipped
QtyShipped

The report I'm trying to create is used as a Shipment Log that is
attached to the cover of the each order file. It has (in the group
header) -
------------------------------------------------------------------------------------
OrderNo PartNo PONo QtyOrdered Unit
ex:
13804 2562-16 152480 450,000 Pcs
------------------------------------------------------------------------------------
Each detail line is/should be-

InvoiceNo DateShipped QtyShipped RemBalance
ex:
73150 6/10/06 120,000 330,000
74025 6/12/06 50,000 280,000


My question is..... how can I get the RemBal computed for each line in
the report?
I need a running total as opposed to a "grand total"
The first line could use [QtyOrdered] - [QtyShipped], but that
wouldn't work for subsequent lines.
Could I have a "work field" (that would not appear on the report) that
would keep a running total of shipments and subtract that from
QtyOrdered, or is there another way?

Thanks.
Click to expand...

I have not played with it, but I believe your idea of using a "work"
field (hidden filed) is the answer.
 
G

Guest

Create a text box on the report and name it txtRemBalace for the RemBalance
amount.
In the OnOpen event of the report enter coding similar to the following:

Dim RemBalance1 as Long
Dim RemBalance2 as Long
Dim RemBalance as Long
Dim QtyOrdered as Long
Dim QtyShipped as Long

For Me.QtyShipped = Me.QtyOrdered to 0
RemBalance1 = Me.QtyOrdered - Me.QtyShipped
RemBalance2 = RemBalance1
If RemBalance2 = 0 then
Me.txtRemBalance = RemBalance2
Else
RemBalance = RemBalance2 - Me.QtyShipped
End If
Me.txtRemBalance = RemBalance
Next

You may want to place several text boxes in a particular section of the
report and check the values as it loops through the records to make sure it
is calculating correctly. When it is you can take the ones you don't want out
or hide them with .visible= False property.

HTH
scruffy
 
Top