I can see the Fortran mind set here. But this is not Fortran, it's a relational database. You need to think in terms of tables and relationships first, then design a report layout that shows the results that you want.
Your script could be fixed--From the outline that you posted, it looks like you aren't creating new records and thus your data is overwriting the same record over and over again, but there's a much simpler way and there is really no need to copy data from one table to another when you can set up relationships to link to that data instead.
From what you have described, a single person can have more than one role, but can the same role be listed for more than one person?
Hello again Phil
First thanks for the quick response. I really appreciate.
Second, to answer your question the relationship between Person and role is PersonID -|----< PersonID, RoleID-Sdate-Edate. The use of the temp file is because of the counts the report needs
PersonName RoleName SDate EDate
AB Floor Clerk 2008
CD Floor Clerk 2008
EF Floor Clerk 2008 2010
GH Floor Clerk 2009
IJ Floor Clerk 2009
KL Floor Clerk 2010
The challenge is that there is a running total in the Summary and Detail report is
Year Role Current Number = Last years total + add people - resigned people
2008 FC 3 0 3 0
2009 FC 5 3 2 0
2010 FC 5 5 1 1
Year Person Role Resigned in Current Number
2008 AB FC
2008 CD FC
2008 EF FC 2010
2009 GH FC
2009 IJ FC
2010 KL FC
I use the TempFile to write the Total Current Numbers into the ReportFile
Does that make any sense to anyone but my client and me??
Oh and by the way the reports MUST work on WebDirect so totals in the footers is not really an option, they will not show correctly. :(
Technically, this is a many to many relationship, But if the only "Role" specific data is the name, we can keep your data as structured, but I'm not convinced that you need a looping script to do this.
It occurs to me that your roles table could be setup to record one new record for each person's role per year. If a person starts as an FC in 2008 and resigns in 2010, You could log two records for that person in Roles, one for 2008, and one for 2009. That would greatly simplify the process of computing total counts as you can use sorting, relationships or ExecuteSQL to produce the parts of this report much more simply with that structure to Roles.