asked on

Multiple Record Source Report

Listed below is a sample of an Excel based report I am trying to reproduce in Access:

Line No      Group      SubGroup            Jan      Feb      Mar      Year Total

1      Group1
2            SubGroup1            11      22      3      36
3            SubGroup2            33      7      19      59
4            TotGrp1            44      29      22      95
5      Group2
6            SubGroup3            14      5      4      23
7            SubGroup4            28      77      8      113
8            TotGrp2            42      82      12      136

9            TotGrp1-2            86      111      34      231
10
14      GroupA
15            SubGroup5            100      85      68      253
16            SubGroup6            120      55      44      219
17            TotGrpA            220      140      112      472
18      GroupB
19            SubGroup7            220      140      112      472
20            SubGroup8            172      222      68      462
21            TotGrpB            392      362      180      934
22            TotGrpA-B            612      502      292      1406
23
24            TotalAll            698      613      326      1637

While the data is in the same format, in our report filing, each section is displayed separately. I know how to reproduce the SubTotals and Totals, and I've created separate queries for each "SubGrouping, but I'm struggling with how to combine the data on one report. Is this possible with Access?

axla

jeremyspringer

I think I understand your issue. And believe it can be solved by using the "Sorting and Grouping" option on the report.

Design the report with all of the data as your record source formatted how you'd like it to appear. Then right click in the report and select sorting and grouping.

Enter the field you'd like to group on. In this case I think you want to use the "Group" field. On "Group Header" enter any header you want above the subtotal or enter "No" if you don't want a header. On "Group Footer" enter yes. Under group interval, enter 99. Then enter "Whole Group" under Keep Together so your groups all appear on the same page in the report.

Finally, close the window. You should see your "Group" footer. Create a calculated field under each of your numeric fields which are equal to "=SUM(Jan)", "Sum(Feb)", etc. This should give you the result your looking for.

If your data is in several different tables, you'll want to perform a "UNION" query to combine the tables. That's a whole other deal. You can search in EE for an example, or I'll give you one.

Hopefully this gives you what you need.

Jeffrey Coachman

axla,

Can you post the raw data and the "Summary" as it is now in Excel?

(This way Experts here do not have to "Guess" at anything)

JeffCoachman

axla

ASKER

Jeff,

I've posted an Excel Workbook that contains the table I'm trying to duplicate in Access. The report is in the "Summary" spreadsheet tab, I've alson included a data snapshot and the filter I'm trying to use to duplicate the Excel table.

axla
Position-LookupRev1.xls

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

axla

ASKER

Jeff,

Sorry for taking so long to respond. Thanks a bunch for all of your help.

axla

Jeffrey Coachman

;-)