What does each record in the new table represent? From what you describe, all three records for customer ABC would be identical. What would be different in each of the three records?
Why do you need such a table? (It's possible that you might get the desired results without adding a table of denormalized data.)
Yes, strange but true.
Each record represents a person and how much they themselves have 'earned'.
I need to do some statistical analysis on the results set I'm looking to create. A couple of things I need are mean, mode and median profit share through a range of unique individuals, along with top quartiles, so that is why I need what appear to be identical records, just to represent each individual and their wealth! This will give me a total count of all people.
At the end, the total table needs to be printed for management to give a full view of the same earnings per unique person.
I've managed to set up a lovely little looping script which looks up the numbers of staff per record and creates the appropriate numbers of new records in the data table. This means that columns 1, 2 and 3 from the original post are now complete. A little progress :)
The final issue now is that the numerical data I wish to place against these new records (that which has already been calculated on the sub summary report) needs to have years filtered out.
I have a global field GF_YEAR which is used on the summary report to allow the user to use checkboxes to select the year of their choice.
I can get the total of all year's profits to the data table, but need them splitting by GF_YEAR.
I was thinking of a GetSummary(Profit;GF_YEAR) with the global field as the break but as yet this isn't working.
A little madness inducing because the sub-summary report is perfect, but it's just a matter of transposing the summary results per year to the new records.... :)