Comparing Query Data in Reports

[awl]

I want to use 2 Subreports in 1 Main Report to display “differences” between 2 tables for easy recognition where data errors have been made. In the sample below, I have 2 select queries based on 2 non-related tables. Col 1 and 2 are displayed; GroupBy and Sum in descending order by $ in Col 2. I want to use the IIf Statement function, to display Columns 3 through 7. Then in the Report Footer section of each subreport I would show Subtotals; in Report 3 to show the difference. When I have the report set-up, then all I need to do is randomly run this report, and if Report 3 has all $0.00, then I know the data is accurate. If someone could provide an example of an IIf Statement for each of the Columns for one of the rows. Thanks for anyone offering any suggestions. Note: in Report 1, Sets 2, 7 and 4 as listed in Col 3 should have the same value as Set 3 in Report 2. (Due to the editing of this thread, the 220 at the end of the first row in Reports 1 and 2 should be under column C7$.)
Report 1; qrySummary of Deposit Description from tblDeposits
Cl DS#, C2$, C3$, C4$, C5$, C6$, C7$
Dep Set 1 220 220
Dep Set 2 110 110
Dep Set 5 80 80
Dep Set 7 35 35
Dep Set 3 30 30
Dep Set 4 25 25
Dep Set 6 9 9

Subtotal 1 509 170 80 30 9 220


Report 2; qryAnnual Inc Chbk Trans from tblIncome
C1 IS# C2$ C3$ C4$ C5$ C6$ C7$
Inc Set 1 220 220
Inc Set 3 140 140
Inc Set 4 80 80
Inc Set 2 70 70
Inc Set 5 8 8

Subtotal 2 518 140 80 70 8 220

Report 3
Difference: (9) 30 0 (40) 1 0

 

[awl]

I am responding to my own thread. Since I wrote the initial thread above, I found one solution. This may not be the shortest way; however the results is what I want, and more.

I altered the 2 queries to Crosstab queries for subreports 1 and 2.
I created 3 additional queries to obtain the totals of each column and to combine the totals of the individual Set 2, 7 and 4 for the values in Report 1. These queries are the ControlSource for Report 3 values. In Report 3, I used control text boxes with the DlookUp function from the queries, to place the subtotals on the Report, and then I used another set of control text boxes to calculate the differences between the DlookUp values. I used the following formula to obtain the "difference" between the "2 sets of data" in the subreports:

=DLookUp("[Sum Of Total Of TRACTAmount]","qryCHBK Transactions_Crosstab Totals&quot-DLookUp("[Sum Of Total Of Amount]","qryDeposits Made_Crosstab Totals B&quot

I hope what I wrote may help someone.