4 Replies Latest reply on Sep 11, 2012 8:00 PM by ArnieG

    Ad hoc queries on two or more tables (Filemaker Pro 7)



      Ad hoc queries on two or more tables (Filemaker Pro 7)


           I can't seem to find a way to do what ought to be simple queries and table-matches.

           I have 6 tables in one database. These are pretty big tables, ranging from 10 to 50 million records in each, for a total of 180 million records. The tables represent active accounts at year end 2006 through year end 2011, and most records appear in more than one table. The tables are easily linked together by the unique ID code for each account.

           What I'd like to do is count the accounts that continue to exist from each year to each following year. It would be great to have one master table like this:

           ID      Descriptor1    Descriptor2   Descriptor3    EOY2006     EOY2007     EOY2008     EOY2008

           where the EOYnnnn fields are just 0 or 1, True or False, etc. to indicate if the ID is found in the original EOYnnnn table.

           Or, I could live with just having a file of all IDs in 2006 but not 2007, another of IDs in 2007 but not 2006, another of IDs in both 2006 and 2007, and likewise for other pairs of years.

           With either of those results I could continue my analysis. The analysis is a one-shot result, and doesn't need to be anything live that auto-updates or anything. Just a few specific ad-hoc numbers need to be pulled out and then I can forget the whole wretched mess.

           Either type of solution would have been simple in old neanderthal Paradox via its "query by example" functionality, but Paradox won't handle these big tables, and I can't seem to find a way to do the same in FMP. The only time the Help function mentions table joins and such is in reference to importing, which I can't use.

           Thanks for any suggestions!

        • 1. Re: Ad hoc queries on two or more tables (Filemaker Pro 7)

               This appears to be the main issue:

               The tables represent active accounts at year end 2006 through year end 2011

               This sounds like data that should be in a single table instead of separate tables for each year. Such a table structure is very inefficient in FileMaker.

               "Table Joins" are defined in Manage | Database | Relationships.

               I suggest the following basic approach:

               Define a new table and set a validation rule on ID that it be "unique", "validate always".

               Import all your records from all your tables one at a time into this field. You need only import data into ID and any fields that can store data you need that is common to all records of the same ID. The validation rules I've specified will filter out records with Duplicate ID's so this produces a unified table where each ID appears exactly one time. Given that you have millions of records, the importing may take awhile and you may want to dedicate a machine to crunch through the importing while you use another machine for other tasks or you may want to kick off imports just before quitting work for the night so that it can import data all night.

               Now open Manage | Database | Relationships and "join" the new table to each of your original tables by ID field. This is done by dragging from ID field to ID field.

               WIth this relationship in place, you can now set up a layout based on your new table, but you can include any fields of interest from each of your related tables to get your data in columns organized by the table where they are defined.

          • 2. Re: Ad hoc queries on two or more tables (Filemaker Pro 7)

                 Thanks very much! Your directions did the job. It took me all day, but I've now imported two of the tables and got the right layout from them, so I can now see for the first time that I will succeed at this. This was the key idea from you that I was missing:

                 Define a new table and set a validation rule on ID that it be "unique", "validate always".

                 Lacking that, I didn't know how to combine the tables.

                 By the way, one reason it's going slowly is that my new superspeed hex core windows machine only allots 8% of cpu resources to FM. There's nothing much else running and competing for the cpus, so I assume the problem is that FM Pro 7 doesn't know how to use multiple cores at the same time. Are the newer versions any better at that? Are there any other real reasons for me to upgrade, given that this is a one-shot analytical effort?

                 Thanks again,


            • 3. Re: Ad hoc queries on two or more tables (Filemaker Pro 7)

                   This is about FMP Server, but holds true for any application

              <SNIP>   Use a fast disk subsystem: Choosing the proper disk subsystem for your server can provide the biggest performance impact of any hardware decision you can make.

                   Until recently the choice for what disk subsystem to use was an easy one – go with either SATA or SCSI hard drive(s). When it comes to hard drives for your server environment, you want to consider the fastest hard drive you can. Speeds of hard drives are typically measures in “revolutions per minute” or RPMs. As a general rule, the faster (higher RPMs) a drive is the faster the drive can get to data on the drive which translates into faster response time and better performance. SCSI drives typically have a higher RPM rating that SATA drives.

                   Solid State Drives, or SSD for short, are gaining in popularity and might be a viable alternative to using a hard drive in your FileMaker Server environment. SSD drives have advantages over traditional hard drives due to low read latency and consistent read performance. However, SSDs tend to be more expensive and may require more power than a hard drive with the same capacity.  <SNIP>

                   You can see videos of  the speed difference between SATA (spinning hd) and SSD (solid state hd) here  (Macs - but HDs are HDs)

              • 4. Re: Ad hoc queries on two or more tables (Filemaker Pro 7)

                     Thanks. I'm already running all of Windows on an SSD, with another SSD used to buffer the hard drive, but I guess I should try copying over all the data tables onto the Windows drive for those extra few percentage points of speed. Why not?

                     It still seems as though FM Pro isn't equipped to utilize more than one core for a given task, and from the description of Pro Server, it looks as though each core services one or more clients, but a single client still never gets the power of all the available cores. Not that I'm saying that this sort of multithreading is easy!