I think I remember reading awhile ago that this was not possible, but I'm not so sure that was true. Okay here we go.
A little explanation of what we're doing. Our real estate appraisers are being moved over from 100 percent commission to salary; however, their salary going forward is based on what they are able to produce (the salary will change probably every quarter). So, I need to be able to track and summarize their salary vs. their production.
In Table A: this is just a place where each record holds the appraisers name, their salary, when they were payed (see following paragraph), and the date the record was created.
In Table B: This table is where each appraisal record is created and it holds the appraiser's production.
I should mention that the following is being sorted by "Year", "Month", and Pay Periods 1 and 2"; however, for the purposes of keeping this clear I'm only mentioning the fields I'm having issues with.
Table A has these fields:
Salary (Number Field)
Salary_Total (Summary Field)
Table B has these fields:
Job Fee (Number Field)
Job Fee_Total (Summary Field)
Here's what I have happening: Within Table B, in a sub summary and via a portal, I have "Salary" and "Salary_Total" displayed, and not only are they displayed, but they are also matched up(sorted) along side with the "Job Fee_Total* of that same time period (I thought I was pretty fancy for doing that).
Here's what I need!!!
So for each pay period I need to find the difference of their salary vs. production. I need to be able to do the following calculation in Table B in a subsummary: "Job Fee_Total"-"Salary_Total"
thank you in advance for insight you may be able to offer. Also, whether it's elegant or complicated, a solution is a solution!!!
Here's an image of my report. The Salary figures haven't gone into effect. These numbers are appearing just so that I can test the calculations. I made some mock salary records.