I have a fairly large database that I am building for an insurance company. I am trying to summarize a group commission statements that we get from different insurance carriers. The problem I am running into is I have to keep all the statements in separate tables in the DB. As you can imagine I need to total up each statement and total them out based off insurance agents. I basically am looking to summarize all carrier commissions into one table, but I am lost on how to do this. Here is an example of my DB setup.

Agent Table

- Agent ID Number

- Agent Name

Insurance Company 1

-Agent ID number

- Commission

- Premium

- date

Insurance Company 2

-Agent ID number

- Commission

- Premium

- date

Insurance Company 2

-Agent ID number

- Commission

- Premium

- date

As you can see the insurance company tables have the same data structure (I simplified it for this example) however I have to keep them in separate tables for tracking purposes.

I want to be able to summarize an agents policies by date and total them out for all the companies on one layout. How can I accomplish this without portal records?

I have to keep all the statements in separate tables

Why is that?

I have a fairly large database that I am building for an insurance company. I am trying to summarize a group commission statements that we get from different insurance carriers. The problem I am running into is I have to keep all the statements in separate tables in the DB. As you can imagine I need to total up each statement and total them out based off insurance agents. I basically am looking to summarize all carrier commissions into one table, but I am lost on how to do this.

As you can see the insurance company tables have the same data structure (I simplified it for this example) however I have to keep them in separate tables for tracking purposes.

I want to be able to summarize an agents policies by date and total them out for all the companies on one layout. How can I accomplish this without portal records?

When Creating a new layout you add a Part (Layouts -> Parts Setup) add a leading Sub Summary sorted by the date (add another subsummary above it for agency if needed). For the totals you need to create a summary field that adds up whatever need totaled. Then create a trailing Sub Summary sorted by the date.

Also, you can have the insurance company tables as one table and track it just as well as if they were in three separate tables. The insurance companies can have their own ID, like the agency has its own ID. Having it in one table makes it easier for reports.