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
Tble1ID
Name

Table 2 structure
Tble2ID
Name

Table 3 structure
Tble1ID
Tble2ID
Data

Query
Tble1ID
Name
Tble2ID
Name
Data

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.

Steve

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.