12 Replies Latest reply on Mar 17, 2010 10:29 AM by philmodjunk

    Looking to Replicate some Access Query Functionality

    tglatt

      Title

      Looking to Replicate some Access Query Functionality

      Post

      I have an Access database containing three tables - a contacts table along with two tables containing current period and prior period financial performance for each contact. The tables are related via the primary key. I also have a query that calculates financial ratios using the current period and prior period financial data, and a query that uses the ratios query as a basis for calculating a proprietary performance score.

       

      I am on a MacBook with OS 10.6.2, and have been using Access by running Parallels. I am a bit tired of the whole Parallels thing, and since I use Bento I thought I would look into Filemaker.

       

      I've been able to set up comparable tables in Filemaker, set the relationships on the client id, and have even learned how to construct some calculations, however I cannot seem to figure out how to set up a table (or view or list, etc.) that automatically calculates the ratios I am looking for in a separate table. 

       

      I know that it can be done, all I need to be pointed in the right direction.

       

      In summary, here is what I am looking to accomplish:

       

      I want to be able to open a table (similar to my query in Access) containing the contact name from the contacts table and the results of calculations based on prior period financial data and current period financial data for each contact.   

       

      As an aside, I have figured out how to do these calculations if I add the calculation fields to the Contacts table - but I would rather have the contacts table only contain contact information. I want a stand-alone, dynamic ratio calculation table.

       

      I am not looking for someone to do the work for me - just help me understand how to think about the process to use in Filemaker. I am a competent Access user but completely new to Filemaker terms and functions.

       

      Thanks!

        • 1. Re: Looking to Replicate some Access Query Functionality
          philmodjunk

          In filemaker, you can add fields from multiple tables onto the same layout--as long as there's a valid relationship linking the tables.

           

          You should be able to define a calculation field in your related tables and simply add it to your contacts layout.

           

          On a side note: in both Access and Filemaker, I'd look at combining your current and prior period tables into a single table and use relationships that include dates to access either current or past records for a given contact.

          • 2. Re: Looking to Replicate some Access Query Functionality
            tglatt

            Thanks for the reply - and I can see how combining the performance data tables into one can help with a trend tracking project I am working on related to the same set of data.

             

            Your response did inspired another question. The way the query works in Access is by looking at the contacts table and related performance data and then calculating the ratios. I can open the query and see the most current ratios. 

             

            What I tried in Filemaker was creating a table for the ratios - which worked so long as I put the related data (in this case the contact id) in the ratios database. For example, The table was empty until I added an id number and then the ratios for that particular contact were calculated using performance data. Of course, that was accomplished only for those contacts for which I entered the ID numbers, not for all of them "on the fly." 

             

            My question is, is what I need to create a layout - per your suggestion - that pulls from the calculation fields from the ratios table I just described and also key data from the contacts table, or am I completely going off in the wrong direction?

             

            I think my issue is that I don't yet know how to use the Filemaker tools properly! 

            • 3. Re: Looking to Replicate some Access Query Functionality
              philmodjunk

              I can't answer that without knowing more about the calculation you are attempting to define. I assumed your calculation drew data from your financial data table and so suggested that you define a calculation in this table not in a new table.

               

              Does this calculation draw it's data from one record of financial data or multiple records? If multiple records, what's the relationship that identifies which records to include in the calculation. (There are sumary fields and aggregate functions that can pull data from multiple records and compute sums, averages, etc.)

              • 4. Re: Looking to Replicate some Access Query Functionality
                tglatt

                Here is the table set-up:

                 

                Contacts (containing a unique ID field and contact data such as organization name, phone, address, etc.)

                Current Period Financial Data (containing the related contacts ID field and the contacts latest quarter's financial report such as assets, capital, income/loss, etc.)

                Prior Period Financial Data (same structure as current period, but for the year before)

                 

                The ratios I am calculating are for each client. An example is the capital ratio, which is capital divided by assets. Another example, one that uses prior year data, is Return on Average Assets, which is income/((current year assets+prior year assets)/2).

                 

                Since my last post, I went ahead and created the ratio calculation fields right in the contact table. They do update automatically. I was hoping to keep that table clean - without calculation data - but that seems like the best way to go. I get the calculations that I want.

                 

                With regard to your earlier suggestion of combining current and prior year data into one table, I am working on how to do that. I can think of the query to write if I were to do it in Access, selecting the fields based on the ID and a date stamp field, but I have not yet figured out how to do that in Filemaker.

                 

                Thanks again for your comments and direction.

                • 5. Re: Looking to Replicate some Access Query Functionality
                  philmodjunk

                  I was hoping to keep that table clean - without calculation data

                  That's generally not an approach you can take with filemaker. In Access, the calculations are always part of a Query or other datasource object (such as a text box placed on a form or report) where you have to define the calculation in a field in Filemaker.

                  In any case, I don't really see any advantage to you in trying to set up your tables in that way. (You could place all the calculation fields in a related table with a one to one relationship based on client ID, but why go to all that trouble?)

                   

                  On the combinded table, you can define an unstored calculation field in Clients that computes a current date or time stamp which can then be included in a relationship to your financial data.

                  • 6. Re: Looking to Replicate some Access Query Functionality
                    tglatt

                    Phil

                     

                    Thanks again. Agreed on the "why go to all the trouble" statement. I am becoming more familiar with the layouts functionality, with which I can show the data however I want. Kind of slick when you get used to it.

                     

                    As for the data combination, I seem to be hitting a wall. Don't want to abuse your willingness to help, but I don't understand how the related date works to define the values to use for a calculation in Filemaker. I may not have mentioned this, but the financial records are in rows by client id. If I combine all financial data, then I will have a multiple rows of data - a row of financials for each client for each year stored in the database. How do I "tell" Filemaker to calculate using the most recent and prior financial data (for example, assets from 2009 and assets from 2008)? 

                     

                     

                    • 7. Re: Looking to Replicate some Access Query Functionality
                      philmodjunk

                      In filemaker, you typically use a combination of calculation fields and table occurrence entries in your relationships graph to take the place of your SQL Where clause.

                       

                      If you are grouping your data by year, you can set up a simple calculation field in your financials table that returns just the year: Year (datefield)

                       

                      Put a number field in your clients table for the year. A Global or local number fields work, but have different pros and cons depending on what you need and whether the file is hosted amongst multiple users or not.

                       

                      Now you can relate records by year and Client ID like this:

                      Clients::ClientID = Financials::ClientID AND

                      Clients::Year = Financials::Year

                       

                      You can manually change the year field in Clients to see results for different years or you can use a script to change it.

                       

                      You can also Add a calculation field in Clients that subtracts one from the year field to give you the previous year and you can make yet another relationship in the graph for it.

                       

                      Here's an article on the relationship graph, tables and table occurrencs that may help you fill in some of the blanks:


                      Table vs. Table Occurrence (Tutorial)

                       

                       

                      • 8. Re: Looking to Replicate some Access Query Functionality
                        tglatt

                        Awesome. Thank you. I think I get it now. 

                        • 9. Re: Looking to Replicate some Access Query Functionality
                          tglatt

                          I've run into an issue I cannot figure out related to this post. I tried to work through it but cannot seem to come to find the solution.

                           

                          So far I have all of the calculations previously referenced in the posts working perfectly. The set-up looks like this:

                           

                          Financial data table containing current year and prior year performance information

                          Directory table containing contact information and financial performance data that calculates using current and past performance data from the financial data table

                           

                          The relationships are built on the client id, and the current year date to retrieve current financial performance data, and the prior year date to retrieve prior financial performance data.

                           

                          What I have been pulling my hair out over is this: I want to look at the calculations if I were to combine the financial data of one contact with that of every other record in the database (for example, in a merger setting).

                           

                          So, what I have tried so far is creating relationships similar to what I described above, but with the added relationship category of "client" - meaning that I only want the financial records to show for the one entry in the directory table that I designate a client. This didn't work because Filemaker assumed that any record where the client field was empty (i.e. not a client) was a match - meaning that it turned up everything.

                           

                          I then created a third table called Clients that contained the ID I wanted to designate and linked it to table views of the financials and also the main directory table. I was able to set up the relationships, and create a layout that showed only that client's data. So far, so good.

                           

                          My problem, then, is this: The calculations do not perform. It ignores the client data in the calculations. 

                           

                          How do I create this relationship so that the financial data for a client is added to the financial data for each other record in the directory table.

                           

                          The relationships are shown in the linked image here: http://gallery.me.com/tglatt#100055/Relationships&bgcolor=black

                           

                          Any guidance on how to do this  would be much appreciated. 

                          • 10. Re: Looking to Replicate some Access Query Functionality
                            philmodjunk

                            I don't see anything in Client that will serve as a Primary Key to uniquely identify each client record.

                             

                            What exactly is the "Charter" field?

                            • 11. Re: Looking to Replicate some Access Query Functionality
                              tglatt

                              Every financial institution has a unique charter id, which is the number used to identify the unique institutions in the database. I used the same ID for the client. PErhaps that is the source of my problems. Maybe I should use a different unique ID number for the client table and then add the same field to the directory table (or financial records table)? 

                              • 12. Re: Looking to Replicate some Access Query Functionality
                                philmodjunk

                                If you want to see only data applicable to a given client, you need a way to identify which records apply to a given client. A client ID field to serve as Primary Key is the first step.

                                 

                                Since multiple clients may be linked to multiple institutions, you'll likely need a join table to link them. (This would also have been necessary in Access.)