Linking master and child reports

[rjmccafferty]

I need to have information in a report that covers varying date ranges. This has to do with our Managers calling potential investors (large institutional investors). The report needs to have essentially the fields listed below (all for a date range specified by the user, which is done in a calling form which has unbound text fields for the start and end date of the range). I use two tables, one for information about the individual call being reported, a second that allows the user to list any number of follow up duties/things to be mailed. Kind of like invoice and lineItem tables in a one to many relationship, if that analogy helps at all. The best way to get the combination of totals fields and lists of details seem to be with a master report and 4 subreports as noted below.

On the master report, my only entries are a beginning date and ending date that are obtained form unbound fields in a form, the report title, date, page numbers, etc. The date information is obtained via a query.

So, I have
1. an initial query to access the tables and provide date range for the master report (all queries listed below access this query for their data
2. a query based subreport to count phone calls
3. a query based subreport to count meetings set up and develop a list of companies they were set up with
4. a query based subreport to pull up a list of materials sent
5. a query based subreport to list all companies moved to the declined/dropped category

· I link the master and child reports with the CustomerID field (which I think may be my problem).
· I have no grouping on the master report
· I have all subreports set to CanGrow and Can Shrink
· Visually, in design view, I do have the subreports drawn very narrowly vertically, looking much like a long pencil going from side to side. (I do this in other reports with no adverse effects).

I have a couple problems I do not understand:
1. The subreports mostly do not show headers or footers, just detail, although there is one exception to this.
2. The subreports that are meant to show details, such as all materials sent out, do not list them all in a row, looking like they are a table of their own. The subreport begins over again with each new Customer's information. (But within each customer's subset of information, it does list them line by line as you would expect.) It is this problem in particular that makes me thing linking the subreports by the customerID field was an error.

Potential solutions:
· If linking the subreports by CustomerID is a problem, how do I link them? Do I need to somehow assign ID numbers to each record in the master and child tables so I can link the reports?
· Or is this not what is causing the problem with the listings (subreports) of Follow up Materials, Meetings and Group changes beginning anew with each new customerID?
· And I have no clue why I am not see the report and page headers in the subreports when they come to the master report. When run individually, as a report on their own, these areas print just like you would expect them to do.

 

[dzshanno]

I have the same problem with header fields and labels not appearing in the page header of a subreport

Which header section are you using?

I found moving stuff to the report header from the page header caused it to appear.

However this is only a partial solution as I would like it to appear on the top of each page.

I hope that helps a little

 

[rjmccafferty]

As I look back, I am getting exactly what you are. The one subreport where I use count functions, and therefore put it in the report header, the info does show up. But the others all have the info in a page header, which does not show up.

And, like you, I would like it to appear on every page of the master report. Anyone else have an idea?

 

[rjmccafferty]

Plus, this also still leaves open the larger question of why the subreports run more than once, that is they run once for each record. Am I using the wrong field to link them to the master report? And if so, how can I link them?

 

[dzshanno]

What are you trying to show?

Report Header
customer1 cout of calls
customer1 count of meeting set up
customer 1 .....
customer 1 .....

customer 2
custtmer 2
customer 2
customer 2

...


or

Report header
customer1
customer 2
customer 3

customer 1
customer 2
customer 3

.....


sorry but it wasnt quite clear

I think we can come up with a solution for both

 

[rjmccafferty]

I am trying to show:

Total Calls Placed for entire date range (for all customers)
Total Calls with substantive conversation for entire date range
[I moved this to the master report header and then this part seems to work fine. there are just two fields in the sub report header]

A listing of all meetings that are scheduled within the date range for all customers. (I would also like a count of the meetings, but that should be a no-brainer with =count(*) in the subreport header or footer)

A listing of all follow up materials arranged to be sent out for all customers.

I list of all customers who status has been changed to Dropped/declined. ( I have a field to search for this one).

What I get now is:
each subreport for customer 1
each sureport for customer 2
each subreport for customer 3

Again, I believe the problem is the linking of master and subreports by CustomerID field, but I am drawing a blank on an alternative way to link the master and sub reports.

 

[tempclerk]

Sounds like you don't need to link the main and subreports. Do the subreports pull up the right information when opened on their own?

 

[dzshanno]

OK I agree that its linking the customerID that is the problem.

The Answer as I look at is that you dont have to link on any field. Just let the subreport return all its rows.

There should only be one row for the count subreports and thats fine ( you could even move them to the main report if you wanted to )

the other sub reports will run through each customer. Just set the sort fields and order to get them on the order you want.

You also dont need the main report to have a source query as there are no bound fields on it.

I hope that helps. I dont have access openb on my PC here to be able to check that out but it should work.

Let me know

David

 

[rjmccafferty]

Let me make some changes and test. I did immediately eliminate all links between master and subs. It is got a lot better in that I now get things listed as I expect. That is, all meetings together, all follow ups togethrer, etc.

But, incredibly, I get three complete and identical iterations of the info. But let me play with them a bit and see what is happening. This feels like an underlying query somewhere where I need to check unique records or unique values.

For what it is worth, i do need an underlying query for the main report because it gives me the fields for the master report header that indicate the date range.

My entire structure is set up with one master query that is not attached to a report and picks up all information needed for all subreports. All the subreports use this query as their data source. This first query picks up the date range from user input in a form that calls many report that they use. This will be the first one I look at for difficulties. Once I see what I can do I will post results, good or bad, here.

 

[rjmccafferty]

It worked. First I took away the links between master and child reports. There was still a problem, but it was one of those query based ones in which querying related tablescan often cause what seem to be duplicate records to appear in the results table. Once I found the offending fields, all was fine. Thank you very much for the help.

Robert