Hello all - Facing a roadblock on generating a desired report. Could pivot table be generated out of following data? If not, what kind of report/chart can be generated to traverse from (Non-Profit) to (By Laws) to (Supplement Laws and Precedent Law). Thank you for your inputs!!!
We have four tables in the database: Non Profit, Non Profit By Law Association, By Laws,By Law Association.
Non Profit table has one to many relation with Non Profit By Law Association
By Laws table has one to many relation with Non Profit By Law Association
This means Non Profit and By Laws are in many to many relation
Moreover, In order to create many to many relation within By Laws table, By Law Association table is created.
One By Law record can be associated to another By Law record either as Supplement Laws(Children) or Precedent Law(Parent).
For e.g. in the Expected Report below, 20 seconds Non Profit record has By Law 4 as By Laws. Then, By Law 4 has a precedent law By Law 1 which is still a record in By Laws table. Additionally, By Law 4 has supplement laws By Law 3 and By Law 9 which are still record in By Laws table.
Expected Report
Non-Profit Name | By Laws | Supplement Laws | Precedent Law |
20 seconds | By Law 4 | By Law 3 | By Law 1 |
By Law 9 | - |
By Law 51 | By Law 5 | - |
By Law 19 | - | By Law 9 |
Trickfinger | By Law 4 | By Law 15 | - |
By Law 90 By Law 90 | - | By Law 15 |
- | By Law 3 |
By Law 89 | By Law 9 | - |
By Law 7 | By Law 3 | - |
Circular Fish | By Law 4 | By Law 1 | - |
Data/Tables
Non Profit
Non Profit Id | Non Profit Name |
NP-001 | 20 seconds |
NP-002 | Trickfinger |
NP-003 | Circular Fish |
Non Profit By Law Association
Non Profit By Law Association Id | Non Profit Id | By Laws Id |
NPBLA-001 | NP-001 | BL-004 |
NPBLA-002 | NP-001 | BL-051 |
NPBLA-003 | NP-001 | BL-019 |
NPBLA-004 | NP-002 | BL-004 |
NPBLA-005 | NP-002 | BL-090 |
NPBLA-006 | NP-002 | BL-089 |
NPBLA-007 | NP-002 | BL-007 |
NPBLA-008 | NP-003 | BL-004 |
By Laws
By Laws Id | By Laws Name |
BL-004 | By Law 4 |
BL-051 | By Law 51 |
BL-019 | By Law 19 |
BL-090 | By Law 90 |
BL-089 | By Law 89 |
BL-007 | By Law 7 |
BL-001 | By Law 1 |
BL-003 | By Law 3 |
BL-005 | By Law 5 |
BL-009 | By Law 9 |
BL-013 | By Law 13 |
By Law Association
By Law Association Id | Supplement Laws Id | Precedent Laws Id |
BLA-001 | BL-003 | BL-004 |
BLA-002 | BL-009 | BL-004 |
BLA-003 | BL-003 | BL-001 |
BLA-004 | BL-005 | BL-051 |
BLA-005 | BL-019 | BL-009 |
BLA-006 | BL-015 | BL-004 |
BLA-007 | BL-090 | BL-015 |
BLA-008 | BL-090 | BL-003 |
BLA-009 | BL-009 | BL-089 |
BLA-010 | BL-003 | BL-007 |
BLA-011 | BL-001 | BL-004 |
