1 Reply Latest reply on Jan 20, 2009 1:04 PM by TSGal

    Summaries and value lists?



      Summaries and value lists?


      I seem to be having a bit of a problem figuring out how to pull together a summation for a set of values linked to a value list.  Here are the details.


      I have one database with two relational tables (one holds project information and the other holds project accomplishment data).  For each project that exists there are a number of different accomplishments (benches built, trails cleared, etc.) that are linked to the project ID.  And for different projects, the same accomplishment might appear (two projects both have benches).  I'd like to create a summary that totals the number of benches built without showing two, Benches Built line.  In essence, for the found set of project, I'd like each value in the value list listed just once with the numerical data tallied across all projects.


      Any suggestions?

        • 1. Re: Summaries and value lists?



          Thank you for your post.


          It appears that you have a many-to-many relationship.  Therefore, you would need a third table to reference a Project to a specific accomplishment.  From there, you can find for the specific projects and then use the Accomplishment data to run a sub-summary report.


          In your third table, you would include the fields Project ID, Accomplishment ID, Start Date, and Date Completed (among other fields).  The Project ID would link to a Project ID in the Projects table, and the Accomplishment ID would link to the Accomplishments table.  When a Bench is being built, you assign it a Project ID, and Accomplishment ID, and the Start Date and if finished, Date Completed.


          You can then find specific records, and then put in the Sub-summary section the Accomplishment task and a summary field to add up the values (sum or count) for that Accomplishment.


          This should get you pointed in the right direction.


          If you need clarification for any of the above steps, please let me know, and I'll try to explain it further.



          FileMaker, Inc.