Create new records based on the results of calculations in a report
This is for me as much a concept question as a technique question.
I have created a report based on sub summaries and summary formulas to produce a result set. I now need to use this result set in further calculations.
I have two key pieces of data that at this stage I'm interested in:
Number of staff (represented on my sub summary by data pulled from a customer record)
Share of profit (profit per customer / number of staff) where profit is also a summary field
What I need to do is populate a new table with a quantity of records per staff per customer...
So, let's say customer ABC (ID 1010) has 3 staff and they each share profit of £60,000. This would result in a profit share of £20,000 each. Let's also say that customer DEF (ID 1011) has 2 staff and they each share profit of £20,000. On the new table, what I need therefore is a set of three records like this:
ID | CustomerID | NoStaff | ProfitShare
1 | 1010 | 3 | 20000
2 | 1010 | 3 | 20000
3 | 1010 | 3 | 20000
4 | 1011 | 2 | 10000
5 | 1011 | 2 | 10000
I'll need to create 3 records for customer ABC and then populate these with the right info, then run the process again and create 2 records for customer DEF and populate these with the right info, and so on and so on for the numbers of customers and staff per customer I have.
I think what I need is a loop, a variable of total staff (3+2) to sit in the loop and create x new records. Then somehow I need to populate the first record, then the second and third, and then when I populate the fourth, ensure that it's using the correct customer ID....
Nothing too complex.......!
My thoughts on how to do this could be completely wrong, so if there are other methods to do this, please let me know :)