Related Baseball Database Calculation Problems
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...