Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    23

    Column Running Total and Subtract

    I have the following table structure:

    P. No. Name HID Type Amount Balance Leave
    2029 ALAC AO-352 Earning 50
    2029 ALAC AO-354 Ded 10
    2029 ALAC AO-254 REC 5

    This table contains so many records i think they are about 5000. I have grouped it on "PNo" and then on "Type", the type group contain three headings Ear, Ded and Rec. I want to create a filed that may contain following calculation:

    Ear - (ded+rec), then i want to show this field in group footer.

    It is possible that I can do this type of calculation on running single column

  2. #2
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by mfdarvesh
    I have the following table structure:

    P. No. Name HID Type Amount Balance Leave
    2029 ALAC AO-352 Earning 50
    2029 ALAC AO-354 Ded 10
    2029 ALAC AO-254 REC 5

    This table contains so many records i think they are about 5000. I have grouped it on "PNo" and then on "Type", the type group contain three headings Ear, Ded and Rec. I want to create a filed that may contain following calculation:

    Ear - (ded+rec), then i want to show this field in group footer.

    It is possible that I can do this type of calculation on running single column
    Did you want the summation equation like Totalsum(Ear)-(totalSum(ded)+Totalsum(rec))? at the footer, or you want to calculate each PNO group record and sum total at the footer or both?

  3. #3
    Join Date
    Nov 2004
    Posts
    23
    I want to calculate for each PNO

  4. #4
    Join Date
    Oct 2005
    Posts
    178
    Create a group for PNO = Group 1
    Create a group for Type = Group 2

    Create a running total and name it "EarningTotal"
    Choose field Balance to SUMMARIZE the SUM and
    chosse FORMULA to EVALUATE
    ..for your formula function enter
    {Type} = "Earning"
    Choose GROUP 1 or PNO for RESET

    Create another running total and call it "DedTotal"
    choose field Balance to SUMMARIZE the SUM and
    choose FORMULA to EVALUATE
    ...for you formula function enter
    {Type} = "Ded"
    Choose GROUP 1 or PNO for RESET

    Create another running total and call it "RECTotal"
    choose field Balance to SUMMARIZE the SUM and
    choose FORMULA to EVALUATE
    ...for you formula function enter
    {Type} = "Rec"
    Choose GROUP 1 or PNO for RESET

    Create a formula for your Group 1 or PNO footer call it PNO
    and enter this function (double ckicking each formula from the listed fields and formulas when shown)

    EarningTotal - ( DedTotal + RecTotal)

    After saving ..
    insert the PNO formula in the Group 1 footer and insert the other formulas in the same footer as you would arrange them..

    PNO & " -( " & DedTotal & " + " & RecTotal & ")"

    To Create Headings for your Type or group 2 to display for each type column, you need to create 3 formulas, one for each type like for example for Earnings heading..
    your formula function would be

    if {Type} = "Earning" then
    "EARNING"

    then drag this heading formula in group2 heading above the EarningTotal column..
    do the same for Ded and Rec . You got the idea..

    That should do it..
    Last edited by fredservillon; 01-14-06 at 21:13.

  5. #5
    Join Date
    Nov 2004
    Posts
    23
    Thankyou very much dear, I am going to try this, Indeed great help and great work you have done, thanks again
    Last edited by mfdarvesh; 01-20-06 at 04:34.

  6. #6
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by mfdarvesh
    Thankyou very much dear, I am going to try this, Indeed great help and great work you have done, thanks again
    No sweat. Took a couple of moments. Glad to help..

  7. #7
    Join Date
    Jan 2009
    Posts
    2

    how to reset group

    its gud idea but would u like to tell how we can reset the group

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •