7 Replies Latest reply on Oct 21, 2012 11:56 AM by MacUT

    Need Help with Subsummary

    MacUT

      I have a HR data base that tracks salaries. In a give year there may be several changes to a person's salary. The person may be promoted, change departments, etc. Each time this happens I record the old salary and the new salary for the given date in a salaries change table. What I need is a report that shows each person's salary change records, the percent change each time, the percent change for the whole year, and the person's beginning and ending salary for the year. In my sample file I can do this but what I can't seem to do is to get a subtotal of the the person's salary for a 3 or 4 year period. I would also need the subtotal broken out by each of the departments.

       

      The file is named SampleBrian.fp7 and is just a brief mock up of what I need.

       

      Can anyone help me with this?

       

      Thanks

       

      Brian Garman

        • 1. Re: Need Help with Subsummary
          comment

          Does a person's salary ever decrease?

          • 2. Re: Need Help with Subsummary
            MacUT

            Yes I guess it could.

             

            Sent from my iPhone

            • 3. Re: Need Help with Subsummary
              BruceHerbach

              Brian,

               

              This appears to work correctly for me.  I added data so that there were 3 years of data for P1 and sorted the data Department, Person,year, date.   All ascending.

               

              Attached is a pdf of the report and the database.

               

              Good luck with your project.

               

              Bruce

              • 4. Re: Need Help with Subsummary
                comment

                MacUT wrote:

                 

                Yes I guess it could.

                 

                Well, then you cannot use Min and Max as the initial and final salaries of a person in a period.

                 

                This is a very simple issue up to the point where you want summaries by department - then it gets pretty complicated. The attached file shows how this could be solved - hopefully you'll be able to finish it to your satisfaction.

                 

                 

                On a more general note: there's a redundancy here, created by recording the starting and ending salary for each change. Ideally, only the change amount should be recorded, and the "before" and "after" amounts should be derived. But then you'd also need to know what salary each person has reached at the beginning of the report period.

                • 5. Re: Need Help with Subsummary
                  comment

                  In your example, the starting salaries are:

                   

                  P1: 40,000

                  P2: 10,000

                  P3: 30,000

                  P4: 20,000

                  P5: 25,000

                   

                  That's a total of 125,000 - not 506,500 as you would lead us to believe.

                  • 6. Re: Need Help with Subsummary
                    BruceHerbach

                    Brian,

                     

                    In looking at your original post and Mr Horak's it became clear that you really nead an Earned field that calculates amount earned at each rate.  To do this I added three fields to your DB.  Endate = Date at which salart ended,  Earned = Amount earned at the current salary rate.  And searned = Sumary field of earned. 

                     

                    To make things look right,  I added records that would go back to the beginning of the year with a 0 dollar increase.  Attached is the updated DB and pdf of the report. 

                     

                    HTH
                    Bruce

                    • 7. Re: Need Help with Subsummary
                      MacUT

                      Thank you Michael and Bruce for your help.  Both of you gave me new insight to my problem.  Michael, I adapted your ideas for my solution and it is working how I wanted it too.  I had never realized how the subsummary with running count worked or how it could be used.

                       

                      Thanks again

                       

                      Brian