One to Many Report
Posted: Thu Apr 30, 2015 11:01 pm
I have 3 tables, Main, Sub1 and Sub2. The main table has a one-to-many relationship with each of the other two. It is set up this way because the number of Sub1 and Sub2 entries will vary for each Main entry. IE: I may have 1 Sub1 entry and 5 Sub2 entries for a main entry. I can combine my tables via view or query and the data gets laid out as below.
Main | Sub1 | Sub2
MRecord 1 | Record 1 | Record 1
MRecord 1 | Record 1 | Record 2
MRecord 1 | Record 1 | Record 3
MRecord 1 | Record 1 | Record 4
MRecord 1 | Record 1 | Record 5
MRecord 2 | Record 2 | Record 3
MRecord 2 | Record 3 | Record 3
MRecord 2 | Record 4 | Record 6
I am trying to create a report to display this data. I am not sure if there is a way to better filter the data before it goes into the report or if the filter should in fact take place in the report. I can group the main records, but I can’t find a way to prevent the “detail” records from repeating. My report looks like so:
MRecord 1
Sub1 = Record 1 Sub2 = Record 1
Sub1 = Record 1 Sub2 = Record 2
Sub1 = Record 1 Sub2 = Record 3
Sub1 = Record 1 Sub2 = Record 4
Sub1 = Record 1 Sub2 = Record 5
MRecord 2
Sub1 = Record 2 Sub2 = Record 3
Sub1 = Record 3 Sub2 = Record 3
Sub1 = Record 4 Sub2 = Record 6
I want it to look like this:
MRecord 1
Sub1 = Record 1 Sub2 = Record 1
Record 2
Record 3
Record 4
Record 5
MRecord 2
Sub1 = Record 2 Sub2 = Record 3
Record 3 Record 6
Record 4
Please note Sub2 = Record 3 does repeat, but under a different MRecord.
I hve the same information in the attached document with better formatting.
May the person that helps me find great rewards!
Thank You
Ron
Main | Sub1 | Sub2
MRecord 1 | Record 1 | Record 1
MRecord 1 | Record 1 | Record 2
MRecord 1 | Record 1 | Record 3
MRecord 1 | Record 1 | Record 4
MRecord 1 | Record 1 | Record 5
MRecord 2 | Record 2 | Record 3
MRecord 2 | Record 3 | Record 3
MRecord 2 | Record 4 | Record 6
I am trying to create a report to display this data. I am not sure if there is a way to better filter the data before it goes into the report or if the filter should in fact take place in the report. I can group the main records, but I can’t find a way to prevent the “detail” records from repeating. My report looks like so:
MRecord 1
Sub1 = Record 1 Sub2 = Record 1
Sub1 = Record 1 Sub2 = Record 2
Sub1 = Record 1 Sub2 = Record 3
Sub1 = Record 1 Sub2 = Record 4
Sub1 = Record 1 Sub2 = Record 5
MRecord 2
Sub1 = Record 2 Sub2 = Record 3
Sub1 = Record 3 Sub2 = Record 3
Sub1 = Record 4 Sub2 = Record 6
I want it to look like this:
MRecord 1
Sub1 = Record 1 Sub2 = Record 1
Record 2
Record 3
Record 4
Record 5
MRecord 2
Sub1 = Record 2 Sub2 = Record 3
Record 3 Record 6
Record 4
Please note Sub2 = Record 3 does repeat, but under a different MRecord.
I hve the same information in the attached document with better formatting.
May the person that helps me find great rewards!
Thank You
Ron