Page 1 of 1

Reporting data in intersecting matrix

PostPosted: Mon Oct 06, 2008 9:05 pm
by steveorg
I'm new to Base and have advanced beginner MS Access skills. I have two tables that are related through a third table. I'd like to create a report where table 1 is the row header, table 2 the column header, and data from table 3 is placed where the appropriate row and column intersect. Here's an example:

Table 1 structure

Table 2 structure

Table 3 structure


Data in table 1
0 Bob
1 Ted
2 Alice

Data in table 2
0 Like
1 Big
2 Things

Data in table 3
0 1 Banana
0 2 Car
1 1 Halloween
2 2 Babe

Report that I'd like to produce:
Code: Select all   Expand viewCollapse view
      |  Like    | Big       | Things
Bob   |          | Banana    | Car
Ted   |          | Halloween |
Alice | Vacation |           | Babe

Is this possible through the report wizard or simple programming? Please point me towards appropriate instructions or sample code.

I may have one related issue. I I set up a relationship based on IDs between table 3 & table 1 and table 3 & table 2. The relationships are 1 to n or n to 1, but this structure seems to require n to n and I do not see how to create an n to n relationship. I assume n=many.

Thanks for any help or input.


Re: Reporting data in intersecting matrix

PostPosted: Mon Oct 06, 2008 10:04 pm
by Villeroy
Currently this is not supported at all. However Calc can create data pilots (aka pivot tables, aka cross tables) from registered databases. [Tutorial] Using a DataPilot with a Base or MySQL Database. Oh, sorry. I just see that your cross-table has text values in the middle range. A pilot would show the numeric aggregations of the intersecting groups (sum, count, average and such)
Then there is the Sun Report Builder (SRB) which can create pretty reports with advanced options for grouping, however it can not create cross-tables.