Calculations Between Groups

askajan

Registered User.
Local time
Today, 12:29
Joined
Nov 15, 2012
Messages
18
Is it possible to create a calculation between groups?

For example....

Group Revenue
Group Operating Expenses
Calculation1 = Revenue - Operating Expenses
Group Other Expenses
Calculation2 = Calculation1 - Other Expenses

?
 
You could create an expression in a new field:

Calculation1: SumOfRevenue - SumOfOperatingExpense

Identify it as an expression in your aggregate query.

Repeat set up for Calculation2

Here is an example that I used. Change your field and table names to match yours.

Code:
SELECT ctab.ID, Sum(ctab.tval) AS SumOftval, Sum(ctab.tbal) AS SumOftbal, [SumofTval]-[Sumoftbal] AS Net
FROM ctab
GROUP BY ctab.ID;

Alan
 
How would I implement that code into a report?
 
Use the query as your record source for your report. Design your report based upon the query.
 
Alan,

Okay, so I have my query setup as such:

Category Amount
Revenue $100
Revenue $500
Revenue $400
Expense $200
Expense $200

When I create an expression within the field of a query, it appears that since the revenue and expenses are in the same column, I can't subtract them? Can I not create an If statement or use any other feature in Access to calculate the items I need (as discussed above)?
 
Can I not use some formula that says:

Calculation1 = Column A When Revenue - Column B When Expense
 
The file is attached so you can see the problem I am running into.
 

Attachments

Can't look at your db this am as I don't have AC2007 or AC 2010 on this machine. But you can separate Revenue and Expense into two fields in a query.

Revenue: IIF(Category = "Revenue", Amount, 0)
Expense: IIF(Category = "Expense", Amount, 0)

These are expressions in two new fields.

You could then subtract one from the other in a third field.
 

Users who are viewing this thread

Back
Top Bottom