[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

Subreport within a subreport

Table1, Primary Key fieldname = "KEY", Record with KEY fieldvalue = 'ABC"
Table2, Foreign Key fieldname = "KEY", 3 records that have KEY fieldvalue = "ABC", 20 fields

Main report selects from Table1

I want a subreport that prints the 20 fields from Table2 in Table format.  20 fields is too wide, so I created 2 Subreports to place within Subreport1 (1st 10 fields in Subreport1.1, 2nd 10 fields in Subreport 1.2)

Am unclear how to LINK the subreports so that they will select records from Table2 that match the KEY value in Table1: Main Report ---> Subreport1 ---> Subreports 1 & 2

Any thoughts?

With Regards-
Sam
0
SAbboushi
Asked:
SAbboushi
  • 10
  • 10
  • 2
1 Solution
 
milduraitCommented:
Sabboushi.
Build a query that joins the data in the two tables and then use the query as your report's datasource.
Cheers
Mildura IT
0
 
SAbboushiAuthor Commented:
Thanks for your post - I don't understand how to do what you are suggesting.
0
 
milduraitCommented:
I think I might have misunderstood your question.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
milduraitCommented:
In report design view, right click the sub-report container and select properties.
On the data tab there are fields for Link Child Fields and Link Master Fields.
Set them both to KEY.
Cheers
Mildura IT
0
 
SAbboushiAuthor Commented:
I have done that.  That work perfectly fine EXCEPT when I have Subreport1 in the middle:

Main Report ---> Subreport1 ---> Subreports 1 & 2

I want Subreports 1 & 2 to be linked to the Main Report.  Subreports 1 & 2 are subreports of Subreport1
0
 
milduraitCommented:
More on my first method, creating a query, see the document http://office.microsoft.com/en-us/access/HA101085051033.aspx
Once you build a query joining the data required from your report, you then should be able to use the wizard to setup a new report, and it will recognise that you have joined data, and ask you how you want to setup your report/sub-report.
Might be easier.
0
 
milduraitCommented:
There needs to be appropriate master child links in sub reports.
0
 
milduraitCommented:
Sorry, repeat
There needs to be appropriate master child links in ALL sub reports
0
 
SAbboushiAuthor Commented:
I have given a simple example for a very complex main report that has over a dozen subreports.  I need to make some modifications.

What I need is for someone to help me understand the linking mechanism for the specific example I have given.  What Source objects and Links to I need to specify for each of the Data tabs of the property sheets to get my example to wokr?
0
 
milduraitCommented:
The Primary Key field needs to go in the Link Master Fields
The Foreign Key field needs to go in the Link Child Fields.
0
 
SAbboushiAuthor Commented:
Main Report:

Property Sheet, Report, Data, Record Source: SELECT Table1.KEY, ... FROM TABLE1 WHERE Tabl1.KEY="ABC";

Property Sheet, Subreport1, Data:
      Source Object: Subreport1
      Link Master Fields: KEY
      Linke Child FIelds: KEY

I open Subreport1 in New Window:
Property Sheet, Report, Data, Record Source: SELECT [Table2].[KEY], [Table2].[Field1]... FROM Table2;

Property Sheet, Subreport1.1, Data:
      Source Object: Subreport1.1
      Link Master Fields: KEY
      Linke Child FIelds: KEY

Property Sheet, Subreport1.2, Data:
      Source Object: Subreport1.2
      Link Master Fields: KEY
      Linke Child FIelds: KEY

I open Subreport1.1 in New Window
Property Sheet, Report, Data, Record Source: SELECT [Table2].[KEY], [Table2].[Field1]... FROM Table2;

When I run the Subreport1.1, I get a list of all records in Table2

When I run Subreport1,  I get a list of all records in Table2, but for any records in Table2 that have the same foreign Key, there is one list for each record.  e.g. if there are 3 records with KEY='ABC", then I will see a list of those 3 records 3 times.

The main report selects only Key='ABC".  When I run Report1, I see the Main Report and then I see the 3 records from Table3 listed in triplicate.

How do I get each list of records from Table2 to be listed only once?
0
 
milduraitCommented:
SAbboushi

Your original question was "unclear how to LINK the subreports so that they will select records from Table2 that match the KEY value in Table1: Main Report ---> Subreport1 ---> Subreports 1 & 2"

Have I answered this correctly?
0
 
SAbboushiAuthor Commented:
No.  You have not taken into consideration Subreport1.  Please examine my last post above (ID: 23653744) if you wish to help further.

Thanks
0
 
SAbboushiAuthor Commented:
OK - I found it.

I open Subreport1 in New Window:
Property Sheet, Report, Data, Record Source: SELECT [Table2].[KEY], [Table2].[Field1]... FROM Table2;

The Record Source needs to be changed to Table1
0
 
milduraitCommented:
The question was on how to link reports and subreports.
I provided the answer for this.
Post 23653316
0
 
milduraitCommented:
The question was on how to link reports and subreports.
I provided the answer for this.
Post 23653316
0
 
SAbboushiAuthor Commented:
Your generalization is not correct.  The question was SPECIFICALLY about how to link a subreport to a subreport to a main report.  As I have already explained to you, your answer Post 23653316 did not take into account the middle subreport.
0
 
Jeffrey CoachmanCommented:
SAbboushi,

I have attached a sample Report that works as you have specified.
The overall Report is named: rptCustomers


Part of the issue here is the exact structure of your Report.
When you say Main Report, is the recordsource Property of the "Main" Report the main table, or is the Main table actually a sub report of a "Main" report with no record source?

For example, you say:
"I want a subreport that prints the 20 fields from Table2 in Table format."
"20 fields is too wide, so I created 2 Subreports"
This would indicate that you want One Parent Report and Two Child reports, like so:
1 Parent Report = Table 1
  Child Report 1 = field1, field2, ...ect from Table 2
  Child Report 2 = Field7, Field8, ...ect from table 2

Then you post:
"Main Report ---> Subreport1 ---> Subreports 1 & 2"
This indicates 4 Reports (The Main Report, the Subreport, and two SubSubReports.
If the two subreports are just parts of Table 2, then what is in Subreport1 above?

So what is needed here is a list of fields in each Table, *and* in each Report.
Can you supply this info please?
Thanks.

My sample is based on:
1 Parent Report based on Table1 (Customers)
  1 Child Report based Table 2 (Order Info fields)
  2 Child Report based Table 2 (Order Date fields)
(Using this layout, the links were created for me automatically, by Access, so the advantage here is that I don't have figure out, and insert the links manually)

You can insert a WHERE clause in the main report, if you like.

Examine it carefully.
I am sure you can adapt it to work in your database.

;-)

JeffCoachman

Access-EEQ24147807-MainParentRep.mdb
0
 
SAbboushiAuthor Commented:
Hi Jeff-

>> When you say Main Report, is the recordsource Property of the "Main" Report the main table, or is the Main table actually a sub report of a "Main" report with no record source?

>> "I want a subreport that prints the 20 fields from Table2 in Table format."
My bad - I meant 'Tabular' format

>> If the two subreports are just parts of Table 2, then what is in Subreport1 above?
Subreport 1 is just the container for Subreports 1 & 2.  This allows me to use the Border and other properties for Subreport1

>> So what is needed here is a list of fields in each Table, *and* in each Report.
Can you supply this info please?

Hmmm... I was trying to figure out why you were responding to this post that I had flagged to close!  It seems that mildurait's objection re-opened the question which I did not realize... sorry for that.

I resolved the problem - let me copy and paste the solution below and try and close the question again.  Sorry that you spent some effort on an already resolved problem.  I will know next time that an objection reopens a question...
----------------------------------------
From ID: 23654694 above:

OK - I found it.

I open Subreport1 in New Window:
Property Sheet, Report, Data, Record Source: SELECT [Table2].[KEY], [Table2].[Field1]... FROM Table2;

The Record Source needs to be changed to Table1
0
 
SAbboushiAuthor Commented:
That's fine by me - I was just trying to stop others like Jeff from wasting their time trying to resolve an already resolved problem.

Peace-
0
 
Jeffrey CoachmanCommented:
SAbboushi,

For the record, I have no issue with the delete request.
It's just the it was unclear (to me at least) what your ultimate goal was.
It was also unclear whether or not you were locked into this design.
I'm still not even sure how breaking the one table into two subeports saves any more space than any of the other techniques I can think of.

You never gave us any info on the fields or the "purpose" of the report.
It is still unclear why you need a "container" Report just to put a border around the two subreports when, AFAICT, you can just draw a rectangle around them.
Thus ridding the project of one layer of complexity.
Again, in your original question you made no mention that this Sub-report was actually just a "Box".
;-)

Part of what I do when I look at a question is to see if I can see an imporovement that the asker did not.

My point here is that instead of saying "This is my design, how do I make it work?"
It might be worth it *sometimes* to ask: "Here is what I want to do, I am thinking about this design, what do you think?"

;-)

JeffCoachman
0
 
SAbboushiAuthor Commented:
Thanks for you feedback Jeff-

Sam
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 10
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now