Calculation done on fields from 2 transaction tables

[nadms]

I'm kind of new to Access. I need to create a report that require information from 2 transaction tables. Here are my tables.
a)Employee

Fields: -Employee ID (primary key)
-Employee Name

c)Transaction1

Fields: -Date
-Employee ID
-Revenue1
-Revenue2

c)Transaction2

Fields: -Date
-EmployeeID
-Expenses1
-Expenses2

The Employee lD is linked to both the Transaction tables.
The first report is "Revenue - Employee within a date range" from Transaction1 table and the report is base on a query. To get the Total Revenue, I have =Sum([Revenue1]+[Revenue2]) in Control Source.

The second report is "Expenses - Employee within a date range" from Transaction2 table and the report is base on a query. The Total Expense is = Sum([Expenses1]+[Expenses2]) in Control Source.

I need another report that is similar to the first report. My form require me to enter the Date From and Date To and Employee Name. There should be a row in the report that allows me to show the Total Expenses figure also within the same date range from Transaction2 table. What I want to do is to minus the Total Expenses from my Total Revenue from Transaction1 to get the Actual Revenue.

I am using my second report as a subreport. My report seems to be able to capture records within the date range from the Transaction2.

I've tried to place a text box to show the Actual Revenue and have the following in the Control Source.
=(Sum([tblTransaction1]![Revenue1]+[tblTransaction1]![Revenue2])) -
(Sum([tblTransaction2]![Expenses1]+[tblTransaction2]![Expenses2]))
It doesn't work. Something seems to be wrong here.

Any ideas would be appreciated.

 

[JerryDennison]

You'll need to create two totals queries. One for expenses and one for revenue. Set the total properties for each field in the following manner:

Date = Where (include the criteria for limiting to the date range you want in the criteria property.

EmployeeID = Group By

TotalRevenue: [Revenue1] + [Revenue2] = Sum
(note this is the same as using: TotalRevenue: Sum([Revenue1] + [Revenue2]), also note that this is not very good database design. You should avoid having repeating groups. Just as you should avoid fields that are not atomic. i.e. have multiple values in a single field seperated by comma or other delimiter)

 

[nadms]

JerryDennison,
Thanks for the suggestion.
Sorry for the delay in response as I was struggling with the codes. I was hoping to give a try to get the problem solve. My form actually require me to enter the "Date From and Date To" and "Employee ID from" to "Employee ID to". I have a combo box for both. These combo box work fine.

I created two queries for each report.

Main report Query1 (Revenue)
Criteria in Date field from Transaction1 table
>=[forms]![ReportForm]![StartDate] And <=[forms]![ReportForm]![EndDate]

Criteria in Employee ID field from Employee table
>=[forms]![ReportForm]![cboEmployeeID1] And <=[forms]![ReportForm]![cboEmployeeID2]

Name : TtlRevenue
Control Source =Sum([Revenue1]+[Revenue2])
&quot;Revenue - Employee within a date range&quot; report from Transaction1 table is base on a query1.

Sub report Query2 (Expense)

Criteria in Date field from Transaction2 table
>=[forms]![ReportForm]![StartDate] And <=[forms]![ReportForm]![EndDate]

Criteria in Employee ID field from Employee table
>=[forms]![ReportForm]![cboEmployeeID1] And <=[forms]![ReportForm]![cboEmployeeID2]

Name : TtlExpense
Control = Sum([Expenses1]+[Expenses2])
&quot;Expenses - Employee within a date range&quot; report from Transaction2 table is base on a query2.

Finally, the ActualRevenue is place below the subreport; in the main report.

Name : ActualRevenue.
Control Source: =sum([ttlRevenue]-[subRptTotalExpense].[Report].[ttlExpense])

It does not seems to work. I tried the following too and it's not given any result either.
Control Source: =sum([RptRevenue].[Report].[ttlRevenue]-[subRptTotalExpense].[Report].[ttlExpense])

 

[nadms]

I've finally got the result by entering the following.

=[ttlRevenue]-[subRptTotalExpense].Report![ttlExpense]

Thanks anyway.