Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012

    Unanswered: Subtracting fields in a report

    Hi all,

    I'm trying to create an expense report based off two tables. I have a table for Bouts, and a table for Bout transactions. In the Bout transaction table there is a field that specifies whether the transaction is an expense or a revenue.

    I'm trying to create a report that will split the transactions out by expenses and revenues, then in the summary show the total income, (income - expenses.) I have the report formatted, but I'm not sure how to get the grand total, any help is appreciated.

    I currently have the report laid out as such:

    Bout Name


    Sample Revenue 1 $100
    Sample Revenue 2 $50

    Revenue Total: $150


    Sample Expense 1 $100

    Expense Total: $100

    Net Total: (I would like to subtract the 150 from 100 here)
    Last edited by ProjectHelp; 03-21-12 at 21:22.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so presumably you have a group footer for employee
    what you could do is sum the revenue and add sum the expenseses * -1

    whether you can do that in the report designer or resort to code is somethign I can't remember. its cleaner to do it in the report designer (place a control in the relevant footer, and defines its value as myrevenuecolumn - myexpensecolumn
    or possibly as myrevenuesum - myexpensesum taken form the earlier footers

    if not you'll have to do it via code
    declare to variables at the top of the code block
    zero your sum variables on change of 'bout name' group
    add the relevant value to the relevant variable
    in the boutname footer
    add a control, give it a reasnable name
    in the boutname footer on format assign a value to the control
    set its value in code to be = revenue - expenses
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2012
    Thanks for the tip, I actually posted this question to another forum and I was helped over there. If any one is ever interested, here is the solution I used.

    Subtracting aggregate fields in a report

Posting Permissions

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