Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculation problem - keeping a running total

Discussion in 'Information Technology' started by scatman@jazzy.org, Jul 28, 2006.

  1. scatman@jazzy.org

    scatman@jazzy.org
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    scatman@jazzy.org wrote:
    > 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.


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

    --
    Joseph Meehan

    Dia duit
     
  4. scruffy

    scruffy
    Expand Collapse
    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



    "scatman@jazzy.org" wrote:

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

Share This Page