I have a file with 3 tables: Service_Call, Expense, & Inventory. Both the Expense and Inventory tables are related to the Service_Call table via the Call_ID field.

I want to create a report that shows all of the expenses and inventory related to given service call. If I only wanted to show expenses I would normally report the data from the Expense table and show Service_Call data in the header and footer sections.

With two detail sections (Expense & Inventory) how would I put a report together to have all of the data on one single page?

Here's a sample of a report I print that has several detail sections. All tables use the HerdID as a key reference field. Medical and Weight are in the same table (Medical), breeding, shows and fiber are separate tables. These are all portals with a relationship with HerdID. In the case of Medical and Weight, I have a two calculation fields in the Medical table; MedicalKey is HerdID & "Medical"; WeightKey is HerdID & "Weight". In my main Herd table, I have 2 calc fields that are the same and the relationship links to those.

summary.pdf

Ellen,

Thanks for your answer! I must not have phrased my question correctly because it is not exactly what I was looking for. The table relationships are not what I'm questioning its more of a formatting and layout question.

From your example I'm guessing that you have portals on your layout and you print the first 10 (most recent) rows in each portal. If this is true how would you produce essentially the same report but not limit yourself to only 10 rows of related data? I really need so show all related data.

I suppose I could script the routine that prints the report and have it run from a single layout with portals if there are less than X rows. If there were more than X rows then I suppose I could switch layouts during execution and have it produce what would looks like one 3 page report but would actually by 3 separate reports.

Since I haven't done this before I was wondering if there was a common way to solve this problem.

Put the cost fields from your Expense and Inventory tables on the layout for Service Call. If you have more than one Expense record and/or Inventory record per Service Call, then add summary fields to the Expense and Inventory tables and put these fields on the Service Call layout and print that. If you want to show all the details, then you would have to have two portals on the Service Call layout--and hope that you don't run out of space.

Unfortuneately I can't bank on there only being a handful of rows. There must not be a standard way of doing this. I was hoping that I could have multiple body sections or something like that on the list view but I guess not.

Anyway, I should be able to figure out a fairly clean way to accomplish my goal.

Thanks!

Ted:

Take a look at Post #129166--it might help you.