9 Replies Latest reply on Oct 10, 2014 10:31 AM by gremlin9297

    Summary Comparison of two years of data

    gremlin9297

      Title

      Summary Comparison of two years of data

      Post

           Hey there,

           Okay here is my dilemma as it were. My goal is create a summary of two years comparing the number of accounts, balance, and price for each investment option. I have to do this for 12 different investment options. 

           Should look something like this

                                                                                                     Year 1                                                                    Year 2

           Investment option 1               # of Accounts               Balance Amount $            Share Price $           (Repeat)

           Investment option 2

           Investment option 3

           Side by Side Comparison Summary. I have a table that includes the fields for name of clients, each investment elections is a field (which shoes the amount $ each clients has in each fund). I believe i'll have to create addition table for the investment elections. I'm still new at filemaker so I'm note sure if I have correctly set up the tables for this purpose. I have an addition table for the price of each fund, the fields are that of the name of the fund and the price of the fund.

           I apologize if I'm not clear in explaining what I'm trying to do or how my current database is set up. Still new to this :/

            

           Thank you for any help!

           -Eric

            

      Screen_Shot_2014-08-26_at_4.55.36_PM.png

        • 1. Re: Summary Comparison of two years of data
          philmodjunk

               This is often called a "cross tab report" in FileMaker. It's not the simplest thing to set up, but it can be done.

               Do you have a table of investment options with one record for each such option?

               What is an "account" in your system? A Field? A single record? A group of records all with the same Account ID? What is the connection between investment options and accounts like? Can an account be linked to more than one investment option?

               Do you have a table of accounts linked to a table of transactions that then must be summarized to compute a balance for each account?

          • 2. Re: Summary Comparison of two years of data
            gremlin9297

                 An account is a single record. Each account can be invested in more than one option. If you saw the screenshot I posted shows the how the fields are set up. I don't have a table set up for the options yet. I have a one table with many layouts. One layout contains all the accounts and their information including how much they're invested in each option.

            • 3. Re: Summary Comparison of two years of data
              philmodjunk

                   The screen shot does not answer all my questions. And your last post does not fully answer them either. I need to know the structure of your database tables in order to describe how to set up a cross tab report.

                   Do you have a table of accounts linked to a table of transactions that then must be summarized to compute a balance for each account?

                   Another way to look at it:

                   I need to know how these values are produced: Balance Amount $ and Share Price $. From my perspective, these could be single values from single records or sub totals computed from a large number of records. Which is it?

              • 4. Re: Summary Comparison of two years of data
                gremlin9297

                Sorry for the delay, was put on another project for a bit. After coming back and taking a fresh look at it I believe I need to start over in a sense. My biggest issue at this point is being able to create a summary report that is categorized by the Investment options. With each investment option listing the number of members who are currently invested within that fund and the total balance of that fund with a grand total summary of all the funds and the number of members invested. 

                Lets say I'm starting from scratch, one table with one layout that contains the info of each member and all the funds. Each fund listing how much each member has invested.

                The names column was left out but not needed for this summary. basically this is the information i have and need to turn it into a summary report with the fields I recently stated.

                • 5. Re: Summary Comparison of two years of data
                  philmodjunk

                  Am I correct that you have 8 different fields to record data on 8 different funds in the same record?

                  Would those be the "Investment options" of your original post?

                  Does each row represent a different member?

                  If so, you are not using an optimum design for your data model and this will complicate your reporting.

                  • 6. Re: Summary Comparison of two years of data
                    gremlin9297

                    Actually there are 18 different funds and yes those are the investment options. Each row is a different member. I'm very new to filemaker so if you can help with optimizing this I would greatly appreciate it. 

                    Thank you

                     

                    • 7. Re: Summary Comparison of two years of data
                      gremlin9297

                      I should also add that this this data is used for several different reports and is imported from external file, which is why I had set it up that way I did. I designed it so that when someone open the database they can easily import records by pushing a button, selecting a file then added a date to those records. 

                      • 8. Re: Summary Comparison of two years of data
                        philmodjunk

                        Yet a different data model where you have one record for each member linked to a set of related records, one for each investment option (fund) is far more flexible--especially for reporting and does not preclude showing the data in columns.

                        It would make the design you'd have to implement for importing the data a bit more complex but it would the design that would be more complex as it can still be just as simple for the user.

                        • 9. Re: Summary Comparison of two years of data
                          gremlin9297

                          As long as I would be able to create a script to import the data, It sounds like what your suggesting would be more manageable. Also making reports would be easier. How would I go about create this solution?