11 Replies Latest reply on May 19, 2011 2:47 PM by aammondd

    Summary HELP!

    donjuan1498

      Title

      Summary HELP!

      Post

      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?

        • 1. Re: Summary HELP!
          philmodjunk

          however I have to keep them in separate tables for tracking purposes.

          Hmmm, don't think that's truly necessary and the separate tables are what are keeping this from working for you. If you insist on separate tables for the same type of data, the only option that makes sense to me is to create a Report table and import the data from each insurance company table in turn into this one table so that you can generate your report from this combined set of records.

          Can you explain more about why you "have to keep them in separate tables"? I think we can probably suggest ways that you can keep all this data in one table and still do what you need to do here.

          • 2. Re: Summary HELP!

            One idea and not the absolute idea:

            You might need separate tables to import the data from the different carriers and a final table to contain all of the data. Especially if there are laws requiring that or the person signing the check requires it. The import tables I am describing could be inside File 2 and you use File 1 or they could be apart of File 1. File 2 might be a superior idea since you could clone it each month and thus maintian a monthly backup such as File 201101, File 201102...

            First decide the minimum data you want in your final table.

            The create an import table for carrier A and compare that to your final table. Use calcs or scripts to clean up the data or parse it as needed. Then import that into your final table when you are happy with it. The import script can be set to remember the import order, etc. The trick is to not reimport the data each month so you might want to delete it when done.

            Do this for each carrier. This will give you the required separate tables but let you use one final table for the report.

            If you are lucky and each carrier uses the same exact export format you could use one import table with a new field for "Carrier" to identify where the data came from.

            The two table idea with cloning has the extra benefit that if a mistake should be made it can be discarded and redone.

            • 3. Re: Summary HELP!
              donjuan1498

              Absolutely correct they need to be separate for statement organization pruposes as well as accounting proposes. Each insurance company uses ther own terminology, and some give more or less data to track. But for the most part each company has several basic field I need to Capture. 

              Example

              Agent Name

              Agent Number

              Insured

              Policy

              Trans Date

              premium Amount

              commission paid

              I agree that creating one table to capture this all during import/or data entry would be the easiest way to go about it, however my hand are tied from that angle. I like your Idea jack the only problem I see is having to import for each company (there are 8 minumum) and the reports I am trying to run need to be run daily weekly...

              It's almost like I need a temporary join table that will hold the info and release it. I am not sure how to accomplish any of it. Thank for your help. 

              • 4. Re: Summary HELP!
                philmodjunk

                "Absolutely correct they need to be separate for statement organization pruposes as well as accounting proposes. Each insurance company uses ther own terminology, and some give more or less data to track. But for the most part each company has several basic field I need to Capture."

                Yes, but all of that can be done with a single table. There are many ways you can manage this data from a single table and still provide each insurance company the statements and other data that they need and security settings can limit user access to only the records for a specific company, should that be a concern.

                • 5. Re: Summary HELP!

                  Of course one table can do the work

                  BUT

                  This could be a nightmare of cases, ifs, elses and etc. I've tried it... And I now lack the willingness to suffer...  Cry And a distaste for tables with over 1000 fields in them...

                  I would vote, if a vote were taken, for individual FILES for each company and a target single table with the proper fields. The object would be to keep the summary table small, low field count, no calculated fields or lookups, etc. No complexities that can't be debugged by someone else.

                  Just my vote, not much more...

                  • 6. Re: Summary HELP!
                    philmodjunk

                    Jack Rodgers, We're not all that far apart.

                    From what is posted here, I don't see any such "nightmare of ifs and elses" nor anything close to a 1000 field table.

                    If the data from each company is similar in format, use a single table for all companies. If there is just a few fields in common, then a table of just those common fields can be defined with links to specialized "detail" tables can be used to handle the differences when you aren't working with this report. That's basically what you are describing here, but I don't see the value in separate files here for the individual tables.

                    The more you have to create individual tables for each company, the less flexibility you have with your system. With a common table, adding a new company is a data-entery task. With individual tables, it becomes a significant design change to the structure of your system that can require new layouts, script changes and more.

                    That's not to say that sometimes you have no choice. And dedicated "detail" tables (or files) returns us to that situation, but at least makes the report possible. I'm just not seeing anything from our Original Poster here that convinces me that separate tables are required.

                    • 7. Re: Summary HELP!
                      aammondd

                      I have a similar situation here

                      and jacks suggestion of having an import table for each company is the one we are employing.  (currently the solution is in Access but its not so dissimilar that you couldnt do it in FM which would actually be better suited to it)

                      If you create a reference table to map the input field to a consolidation table field you can process your imports into a consolidated table through a single script Using the Set Field by Name function. By looping through a processing table of import table names you can select a single or multiple number of inports to process.

                      You can also create Field labels (as fields) this way

                      You could have 2 copies of each company table one for the single monthly import and one to store history.

                      You could do the same with the homogonized data.

                      • 8. Re: Summary HELP!

                        Let me toss in this bit of info:

                        Access files as I tested them are dbf files and should be importable into Filameker using the dbf option. Someone might try it with a new file to verify this. One problem I found with regulard dbt files is that they need to be packed to eliminate dupllicates else all of those marked as deleted and treated as such by the application will be imported and there is a field with a flag for ok or deleted, forget the name. This caused a lot of problem with 4D when it added import dbf and I was the only one who found the above answer. Sometimes I get lucky.

                        I don't know if you should pack Acess files before importing them or not.

                        There are windows applications that will open the file direcly and display it as a list. The scripts are often compiled and not accessible.

                        Access and xBase do NOT protect their data files, just the scripts. The files just lie around waiting for a hacker...

                        • 9. Re: Summary HELP!
                          aammondd

                          We are only using it as an ETL tool to transform a bunch of text files (39 different) everything is transitory to the Access Database.

                          The application of taking disparate data to a single consolidated table was why I brought it up. FM would make the task so much easier Access is a pain.

                           

                          • 10. Re: Summary HELP!

                            Gotta Love Filemaker (Most of the time anyway)....

                            I have used since 1984 or 85, 12 spreadsheets, 13 or so databases, I don't know how many word processors and page layout programs, etc.

                            Filemaker is by far the easiest to use with really nice tools but also the most frustrating when it comes to doing realy serious programing stuff, disregarding those time when one application offered so many tools that one could get lost it trying to complicate things.

                            How many appls can create many (unlimited?) files that access one central database, each file designed for a special purpose. Wanna knockoff a file just for the data entry guysngals or one for the warehouse or the sales staff or a private file for the owner? No problem...

                            • 11. Re: Summary HELP!
                              aammondd

                              In many ways its very similar to working with PeopleSoft. Especially now with all the script triggers.