I am in-charge of creating a DB for an insurance company. We have commission statements that come in for several different carriers. Each statement needs to be imported into the database each month but into separate tables, (so each company gets it's own commission table) due to the fact that each companies statement is different. There are however 6 records from each statement that are similar (but often have different names).

Example of similar records from multiple commission statements -

Commissions Amount

Premium Amount

Agent Number

Client

Policy Number

Transaction Date

In my DB I also have the following Tables

Insurance AgentTB

- AgentID

- Name

- Address

Insurance Agent NumbersTB

-agent_number_recordID

-Agent Number

- Company

- AgentID

I am trying to generate a report that will take all the policies written by an agent from each commission statement table underneath them. Then I need to summarize and total them. So for example

Agent John Smith

Wrote several policies this month from 3 different companies;

policy1 CompanyA

policy2 CompanyA

policy3 CompanyB

policy4 CompanyB

policy5 CompanyC

How do I get filemaker to run me a report based off the separate commission statements and summarize them into one report. I hope I am being clear enough. Please let me know if more explanation is needed.

Have you considered using one table instead of many? This would be a much simpler option. Or, if you really need multiple tables, consider importing records into a central table from there to do your reporting. This could simplify things for you tremendously.