Gareth Horton
July 12th, 2002, 02:45 PM
Nicholas
Why not just use a subtotal in the summary, i.e. Just configure the sorting so a field that would
logically contain the most current date is either first or second in the list, depending on how you want
to see it. You can suppress duplicate values in the summary to make it a little cleaner.
Bennet Tony xxxxxxxxx nnnnn 8/7/1960 11/3/1997 7/20/1994 1/24/1997 SameUnion 16685.79
1/9/1998 SameUnion 1779.84
Subtotal 18465.63
Jones Tom xxxxxxxxx nnnnn 8/4/1969 4/28/1998 11/30/1993 12/26/1997 NewUnion 3422.60
12/26/1997 11/30/1993 9/29/1995 OldUnion 185.30
Subtotal 3607.90
Summary 22073.53
If you need this as a flat table to export somewhere else, you could feed the output back into Monarch
and just pull the most current info.
Gareth
Nicholas Osdale wrote:
> I'm comparing 2 reports (Employee Earnings Data for 1997 and 1998). Trapping termination & rehire dates that may
> have changed from one year to the next. Trying to do a summary of Fiscal info from June 97 - May 98 and the different
> date traps are creating duplicate records for employees (there is also a "status" field that indicates if they have been terminated="T", Union="U", Active="A", which also breaks out the rows). Need to compare rows and return the most current date information for the individual and totaling their fiscal info.
> Example:
> LAST NAME FIRST NAME SSN EE NO DOB CDOH PDOH DOT Status Union fica2 Gross Taxable 401k def sec 125 Excluded 401k match total hours
> Jones Tom xxxxxxxxx nnnnn 08/04/69 12/26/97 11/30/93 09/29/95 U OldUnion 185.3 185.3 185.3 9
> Jones Tom xxxxxxxxx nnnnn 08/04/69 04/28/98 11/30/93 12/26/97 U NewUnion 3422.6 3422.6 3422.6 174
> Bennet Tony xxxxxxxxx nnnnn 08/07/60 11/03/97 07/20/94 01/24/97 U SameUnion 16685.79 16685.79 16685.79 419
> Bennet Tony xxxxxxxxx nnnnn 08/07/60 11/03/97 07/20/94 01/09/98 T SameUnion 1779.84 1779.84 1779.84 64
>
> Should result in:
> Jones Tom xxxxxxxxx nnnnn 08/04/69 04/28/98 11/30/93 12/26/97 U NewUnion 3607.9 0 3607.9 0 0 3607.9 0 183
> Bennet Tony xxxxxxxxx nnnnn 08/07/60 11/03/97 07/20/94 01/09/98 T SameUnion 18465.63 0 18465.63 0 0 18465.63 0 483
>
>
>
> Hope this makes sense!
Why not just use a subtotal in the summary, i.e. Just configure the sorting so a field that would
logically contain the most current date is either first or second in the list, depending on how you want
to see it. You can suppress duplicate values in the summary to make it a little cleaner.
Bennet Tony xxxxxxxxx nnnnn 8/7/1960 11/3/1997 7/20/1994 1/24/1997 SameUnion 16685.79
1/9/1998 SameUnion 1779.84
Subtotal 18465.63
Jones Tom xxxxxxxxx nnnnn 8/4/1969 4/28/1998 11/30/1993 12/26/1997 NewUnion 3422.60
12/26/1997 11/30/1993 9/29/1995 OldUnion 185.30
Subtotal 3607.90
Summary 22073.53
If you need this as a flat table to export somewhere else, you could feed the output back into Monarch
and just pull the most current info.
Gareth
Nicholas Osdale wrote:
> I'm comparing 2 reports (Employee Earnings Data for 1997 and 1998). Trapping termination & rehire dates that may
> have changed from one year to the next. Trying to do a summary of Fiscal info from June 97 - May 98 and the different
> date traps are creating duplicate records for employees (there is also a "status" field that indicates if they have been terminated="T", Union="U", Active="A", which also breaks out the rows). Need to compare rows and return the most current date information for the individual and totaling their fiscal info.
> Example:
> LAST NAME FIRST NAME SSN EE NO DOB CDOH PDOH DOT Status Union fica2 Gross Taxable 401k def sec 125 Excluded 401k match total hours
> Jones Tom xxxxxxxxx nnnnn 08/04/69 12/26/97 11/30/93 09/29/95 U OldUnion 185.3 185.3 185.3 9
> Jones Tom xxxxxxxxx nnnnn 08/04/69 04/28/98 11/30/93 12/26/97 U NewUnion 3422.6 3422.6 3422.6 174
> Bennet Tony xxxxxxxxx nnnnn 08/07/60 11/03/97 07/20/94 01/24/97 U SameUnion 16685.79 16685.79 16685.79 419
> Bennet Tony xxxxxxxxx nnnnn 08/07/60 11/03/97 07/20/94 01/09/98 T SameUnion 1779.84 1779.84 1779.84 64
>
> Should result in:
> Jones Tom xxxxxxxxx nnnnn 08/04/69 04/28/98 11/30/93 12/26/97 U NewUnion 3607.9 0 3607.9 0 0 3607.9 0 183
> Bennet Tony xxxxxxxxx nnnnn 08/07/60 11/03/97 07/20/94 01/09/98 T SameUnion 18465.63 0 18465.63 0 0 18465.63 0 483
>
>
>
> Hope this makes sense!