AnsweredAssumed Answered

Report From Multiple Like Tables

Question asked by donjuan1498 on Dec 8, 2010
Latest reply on Dec 8, 2010 by philmodjunk


Report From Multiple Like Tables


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 
Policy Number
Transaction Date

In my DB I also have the following Tables

Insurance AgentTB
- AgentID
- Name 
- Address

Insurance Agent NumbersTB
-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.