Summary Layouts with Related Data

[Johnny5]

Im confused on layouts with summary sections. Lets say I have a database with company and contacts tables. They are related through a companyid match key. So when I print a summary report where the layout is for the customer table, summary subsection shows company::CompanyName, the field in the subsummary is company::CompanyName as well, and in the body I have the related fields for contact::First contact::Last.

I tried reversing it as well where the layout was showing records from contacts and that did not work too. Does anyone have an example? I have lots of reports I need to do and the data is alsmost all related like this.

I dont know much about how subsummary reports work with related data. If they were in the same table it would work so much easier.

[-Queue-]

The rule of thumb is to print reports from the 'line items' table, in this case the contacts table. Summarize by the related CompanyName or local CompanyID and include the summarized by field in the sort order. Put local contact fields in the body, then enter Preview Mode to view the report.

[Johnny5]

Thanks Queue!

I wasn't sorting it properly.

[Johnny5]

I got it to work but what about multiple subsummary fields? If there are three tables, company, contacts, and categories. Each company can have lots of contacts and belong to multiple catgories.

So now I need to have a report categorize by category, list all the companies that belong to that company and then have all the contacts of each company under that. I have seen something like this done before. Do I need to create a dummy output file to achieve this? I have attached a mock file to show you what I am trying to achieve.

test_sort.zip

[-Queue-]

Attached is a modified version of your file. I did not solve your report problem, but I did make some changes (such as adding a join table) which helps to simplify things.

I would suggest creating a new table into which you will import all contacts for each company, as many times as there are categories for each company, giving each group a different category id, and then summarizing the records by each category and company. Once the report is printed, delete all records in the new table so it can be refreshed the next time the report is run.

test_sortQ.zip

[Johnny5]

I think i kinda understand what you are talking about. I made a nesting loop, but I cant seem to figure out how to import only those related contacts rather than the whole contact list.

Is this the right approach to this print this report?

test_sort_NEW.zip

Edited October 6, 2005 by Guest

[-Queue-]

Close. Try this one.

test_sort_NEW_Q.zip

[Johnny5]

Thanks Queue. I didnt realize that when you run an import it imports only the records in the found set.