Reporting data in intersecting matrix

Getting your data onto paper - or the web - Discussing the reports features of Base

Reporting data in intersecting matrix

Postby steveorg » Mon Oct 06, 2008 9:05 pm

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
steveorg
 
Posts: 6
Joined: Wed May 21, 2008 7:11 pm

Re: Reporting data in intersecting matrix

Postby Villeroy » Mon Oct 06, 2008 10:04 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28440
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest