7 Replies Latest reply on May 17, 2011 1:42 PM by JohnScalla

    Sub Summary Report sorted by related fields.

    JohnScalla

      Title

      Sub Summary Report sorted by related fields.

      Post

      Okay here is the dilemna:

      I have a table called Staffers in which I have the following fields: Staff Full Name, Activity #1, Activity #1B, Activity #2, Activity #2B, Activity #3, Activity #1a Notes, Activity #1b Notes, Activity #2a Notes, Activity #2b Notes, Activity #3 Notes.

      So in short the staffers table has a list of staff as well as the activities they are able to do with notes for each activity.

      What I need is a report similiar to a Sub Summary report that will list all Staffers who can do a particular activity. The issue is that since the activities are listed in order or preference (ie. 1a is more important than 2b) for another report used. So let's say I have the following data

      Staff Full Name     Activity #1     Activity #1a Notes     Activity #1B     Activity #1b Notes
      John Smith           Swimming      Strong                    Archery           New to this
      Jane Cole            Sailing           Captain                   Swimming       Instructor
      Bob French           Archery          Instructor                Sailing            Student

      As you can see some staffers may have the activity listed in any of the activity fields. I need the report to group ALL staff who do a particular activity.

      For example:

      Swimming
      John Smith - Strong
      Jane Cole - Instructor

      Archery
      John Smith - New to this
      Bob French - Instructor

      Sailing
      Jane Cole - Captain
      Bob French - Student

      What can I do?

        • 1. Re: Sub Summary Report sorted by related fields.
          philmodjunk

          The date in your activity fields should be entered into a related table instead of a series of fields like this. You can use portals to orgainize the data into columns on your first example and base a summary report on the portal's table to get the second example.

          Staff::StaffID = Activities::StaffID

          To get columns of data like your first example, you can put two portals side by side with the activity name and notes field inside the portals. Set the first portal to display 1 row starting on row 1 and the second to display 1 row, starting on row 2. You can use a third field in this table to set the preference and then sort your portals on this preference field if you want.

          Your second layout can place the activity name field in a sub summary part when sorted by activity name and you can place the Staff Name and Activity Notes fields in the body to get a report grouped like you show--provided you sort your records by activity name.

          • 2. Re: Sub Summary Report sorted by related fields.
            JohnScalla

            I see...

            So a new table is to be created with the following attributes:

            Table: Activities
            Fields: StaffID, Activity1a, Activity1b, Activity2a, Activity2b, Activity3, Activity1aNotes, Activity1bNotes, Activity2aNotes, Activity2bNotes, Activity3Notes

            OR

            Table: Activities
            Fields: StaffID, ActivityName, ActivityNotes

            Am I understanding correctly? Forgive me as I have been up most of the night....

            • 3. Re: Sub Summary Report sorted by related fields.
              philmodjunk

              Use the second option. (and get some sleep! Wink )

              • 4. Re: Sub Summary Report sorted by related fields.
                JohnScalla

                HA! Yea, I wish. Deadlines=NoFun/Stress

                I will try this new setup...thank you for your input. Fresh mind, new ideas. I may have more questions, but this is a good start.

                • 5. Re: Sub Summary Report sorted by related fields.
                  JohnScalla

                  Okay setup...but now I have one (probably the stupidest) question...

                  How am I able to add more than one activity to each staff member? as well as notes?

                  • 6. Re: Sub Summary Report sorted by related fields.
                    philmodjunk

                    You can add that data directly to the portals or you can use a layout based on the portal table.

                    If you use the portal, enable "Allow creation of records via this relationship" for the portal's table in the relationship you set up for the portals.

                    If you use a layout based on the Activities table, you can format the StaffID field in this table to be a drop down list or pop up menu of StaffID's (column 1) and names ( Column 2) so that you can create an activity record, then select the appropriate staff record to which it should be linked by selecting that person from the drop down listor pop up menu.

                    • 7. Re: Sub Summary Report sorted by related fields.
                      JohnScalla

                      Yes sir!! That was it. Why did I want to make it harder than it needed to be??? Thank you very much PhilModJunk!!