5 Replies Latest reply on Nov 16, 2011 4:17 PM by philmodjunk

    Related Baseball Database Calculation Problems

    RykionPrangh

      Title

      Related Baseball Database Calculation Problems

      Post

       I'm new to FileMaker and wanted to take a MS Access database and design it from scratch in FileMaker Pro 11.  This database consists of 8 related tables kept in separate files due to file size restriction in Access (I understand that there is no limit in FileMaker but still want to keep it relatively the same) 

      Each record in each table represents a single play in a baseball game; outs recorded, result of hit, who the pitcher was, who the hitter was, etc.  So I created the 8 tables with their respective fields.  The only field in common amongst the 8 tables is the ID field. I created relationships from table1 to each of the other 7 tables using the ID field to join them.  I shouldn't have to join table2 to table3 correct so far? 

      Here's the problem I'm having: I want to find all records for the pitcher field in table2 that match a certain pitcher and total his "total outs on play" field from table5 and put the result in either a field (tried this in table1 with a sum (table5::total outs) but it only sums the current record) or in a layout (I tried using summary fields but they only are recognized in the current table and didn't seem to work either they would only show the result of the current record).  I've done other total calcualtions in other databases and they work, so I'm thinking something must be wrong with my relationships or in the way the calculated field is set up?  This should be very easy and I have it working in Access, but I think once I got the hang of FileMaker it would be easier to work with in the long run, but now I'm having doubts...

      bball1.JPG

        • 1. Re: Related Baseball Database Calculation Problems
          philmodjunk

          This database consists of 8 related tables kept in separate files due to file size restriction in Access (I understand that there is no limit in FileMaker but still want to keep it relatively the same)

          I don't recommend this. It works, but complicates things, especially for new users. It's especially a bad idea to try to replicate the way you would do things in Access. Each program has its strengths and weeknesses. It's best to design your database to maximize the strengths and minimize the weaknesses of FileMaker if you intend to get the most out of your database.

          I'm afraid that I don't follow the logic behind how you have set this up. Naming each table the same except for a number obscures the purpose of each table and I'm not knowledgeable enough to deduce the function of each table from the field names.

          You also repeat the same field in a table except for a number ( Error 1, Error 2, error 3, for example)--which suggests that you need to add a related table so the records in this table would take the place of the individual fields of same name. (I'd make the same two observations if this were an MS Access system.)

          • 2. Re: Related Baseball Database Calculation Problems
            RykionPrangh

             Ah thank you.  A little more clarification.  Think of each table in my database as containing different fields to the same record.  So baseball has fields 1-10, baseabll2 has fields 11-17, etc.  Each table contains the same exact records, just different fields from that record.  If there's a better way to set this up, let me know but keep in mind this database contains over 190,000 records and that's just one season's worth.

            Each field such as error1, error2 represents if there was one or two errors on the same play. I hope that clears it up.

            • 3. Re: Related Baseball Database Calculation Problems
              philmodjunk

              Then these should all be in one table. There's no reason to have separate tables let alone separate files.

              Each field such as error1, error2 represents if there was one or two errors on the same play.

              I understood that to be the case. In Access or in FileMaker it would be better to put those errors in a related table. This makes it possible (using the error fields as an example), to record any number of errors committed on the play without being limited to the number of error fields you've defined in the table. It can also make compiling error statistics much simpler as well.

              • 4. Re: Related Baseball Database Calculation Problems
                RykionPrangh

                 If I combined all tables into one file, that file would be over 16GB in size and it grows by about 200MB every year.  I will try to combine it into one as that should not pose a problem these days.  I'm getting my totals to work out mainly by using summary fields, much nicer than Access.

                I've run into one calculation that I'm not able to figure out so far though and that is Runs Allowed by pitcher.

                Here is what I need to do (I'll use the graphic above to refer to the fields in question)

                I need to count all the instances of where:

                Pitcher = (search field)
                and then Count if any of the follwing is true
                BatterDest = 4, 5 or 6

                ResponsiblePitcherForRunner1 = (same search field)
                and then Count if any of the follwing is true
                RunnerDest1 = 4, 5 or 6

                ResponsiblePitcherForRunner2 = (same search field)
                and then Count if any of the follwing is true
                RunnerDest2 = 4, 5 or 6

                ResponsiblePitcherForRunner3 = (same search field)
                and then Count if any of the follwing is true
                RunnerDest3 = 4, 5 or 6

                All my attempts have failed, this is my best guess at what needs to be doen but it seems to ignore the ResponsiblePitcher fields. Yes I probably need to space out the IF statements to make it easier to read, lol.

                If(PITCHER = Baseball7::ResponsiblePitcherForRunner1; If( Baseball6::RunnerDest1 = 4 or Baseball6::RunnerDest1 = 5 or Baseball6::RunnerDest1 = 6; Count(Baseball6::RunnerDest1); 0);0) + If(PITCHER = Baseball7::ResponsiblePitcherForRunner2; If( Baseball6::RunnerDest2 = 4 or Baseball6::RunnerDest2 = 5 or Baseball6::RunnerDest2 = 6; Count(Baseball6::RunnerDest1); 0);0) + If(PITCHER = Baseball7::ResponsiblePitcherForRunner3; If( Baseball6::RunnerDest3 = 4 or Baseball6::RunnerDest3 = 5 or Baseball6::RunnerDest3 = 6; Count(Baseball6::RunnerDest1); 0);0) + If(Baseball5::Hit value = 4; Count(Baseball5::Hit value); 0)

                This was accomplished in Access using 4 Select Queries, one for each grouping above.  I think I'll try to separate the IF statements and calculate each base runner seperately tomorrow, but any suggestions greatly appreciated!  Hmm looking at this now, I think the problem might be using a field named PITCHER from table1 that has the same name pitcher from table 2.  More reason to consolidate, which I will do.

                Thank you PhilModJunk for your current help!!

                • 5. Re: Related Baseball Database Calculation Problems
                  philmodjunk

                  Please keep in mind that I have only a very limited picture of how you have structured your database and the purpose it is intended to serve.

                  If I combined all tables into one file, that file would be over 16GB in size

                  I would guess tath for any one record, most of the fields are empty. I suspect you could greatly reduce the size of your database by restructuring your database so that related tables are used where records for that subset of the data are created only when needed. The case of the error fields is one example. If there are now errors on a given play, no records in the errors table would be created.

                  Your different "responsible for runner" fields look like another case where such a table of related records would work better.

                  Careful analysis of all parts of your data and how you work with the data would be needed to optimize the structure of your database--and to properly move the data from your current structure into the new.

                  To suggest a solution to this latest question would require explaining a great deal more about your database and how you need to use the data stored in it.