Generating a Matrix of Results

[DBDoktor]

Much like a Pivot Table, I would like to develop an MS Access 2003 Report that prints detail rows from one table, detail rows from another table across the top vertically (I am assuming a subreport in the Header Section), and check boxes in the associated cells at the intersections. No, it is not feasible to export this to an Excel spreadsheet to accomplish same.

I cannot get the subreport in the header to generate more than the first row from my second table (since it is not in a detail section), so I am stuck.
Further, I am assuming if I get the second table to print rows across the top vertically, then I would create a subreport to list the association table intersections where they match/associate.

Is there a way to do this?

 

[dhookom]

It would help if you described your data tables and how you want them rendered in the report. Have you considered a crosstab query?

Duane
Hook'D on Access
MS Access MVP

 

[DBDoktor]

Yes an example is warranted.

Table 1 is a table of activities performed by different business units within the organization. Table 2 is a list of Business Scenarios that represent varying "sets of activities". Table 3 is the Association of Activities that participate in one or more Business Scenarios (it is the many-to-many association of tables 1 and 3.

I would like to list the ActivityName as the row headers down the side of the report. I would like to run the ScenarioTitle across the top of the report (vertically) as the Column header. I would like a check box control to represent the participation of an ActivityName in one or more Scenarios at the intersection point of the ScenarioTitle with a given ActivityName.

 

[dhookom]

This sounds exactly like a crosstab query. Did you try it?

Duane
Hook'D on Access
MS Access MVP

 

[DBDoktor]

It is a cross-tab, but one that needs dynamic column names and counts extracted from the second table, and formatted to be vertical -- as a dynamic report...

 

[dhookom]

You can create crosstab reports with dynamic column headings using the solution at Dynamic Crosstab Report.

I suppose I should update the sample to Access 2003 and 2007 ;-)

Duane
Hook'D on Access
MS Access MVP