4 Replies Latest reply on Apr 16, 2014 2:33 AM by john9210

    Subsummary report problem

    john9210

      Title

      Subsummary report problem

      Post

           FM 12 adv.

           I'm having trouble creating a subsummary report for the following situation. For example:

           Person: John, Mary, Sam, Frank, etc.

           Pet: dog, cat, fish

            

           A person can own one or more types of pet: dog, cat, or dog and cat, fish and cat, etc.

           I want to generate person list that summarizes by pets owned.

        • 1. Re: Subsummary report problem
          philmodjunk
               

                    A person can own one or more types of pet:

               And will a given type of pet record be linked to multiple persons since more than one person can own the same type of pet?

               I am spelling out two options and since I am guessing that your actual database probably doesn't really consist of persons and pets. If so, you'll need to look at how you need this to work in the real database in order to decide which option to use.

               Option 1:

               If John and Mary both own a pet of type "dog", you can have two records in the Pet table for "dog" so that you can link one to John and one to Mary.

               Option 2: Or you can add a third table, a join table so that you can link several persons to the same pet type record as well as linking the same person to several pet type records:

               Persons----<Person_Pet>-----Pets
               Persons::__pkPersonID = Person_Pet::_fkPersonID
               Pets::__pkPetID = Person_Pet::_fkPetID

               In which case, your summary report can be created on a layout based on Person_Pet. If you use the first option where you have duplicate records in Pet, you can set up the same report on a layout based on Pets.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Subsummary report problem
            john9210

                 Sorry, I should have been clearer. Let me clarify. The Person and Pet fields are both in the Persons table. So, on a person record, the pets owned are in the Pet field. Any person can own any pet or combination of pets, selected from a drop down list.

            • 3. Re: Subsummary report problem
              philmodjunk

                   But for the report that you need, these selections should be made in a related table, one pet type to each related record (option 1) as this then makes your summary report possible.

                   The simplest option is to use a portal to a table of pets with a drop down list for selecting the type in a field in the portal row. Fancier variations of that can look like a set of check boxes with a scroll bar.

              • 4. Re: Subsummary report problem
                john9210

                     The separate table worked beautifully. Thanks for your help