One of our consultants is having the below issue and hoping someone might have a suggestion or resolution. Thank you in advance.A client has several custom Payroll reports. Three of them contain Payroll history header data and Payroll history tax data. In the old Payroll system, these fields were in one table; in the new, they're in two tables:
PR_PayrollHistoryHeader PR_PayrollHistoryTaxReporting
There is no way to link the two so that duplicates are eliminated. It boils down to the fact that Table #2 (tax table) doesn't have the check number. It does have the check date, but the Clients employees regularly receive multiple checks on the same date.
There are other "identical" fields in both tables, but when linked, they either still produce duplicates or they produce no results.
I thought I was close when I limited table #2's "SeqNo" field to be less than "000002" and this eliminated most, but not all, of the duplicates.
I have scoured the database to find another table I can use, but there is none. And, the PR_PayrollHistoryTaxReporting is not available for customization; and it is also not available to add fields to the other table. I also tried using subreports, suppressing duplicated figures, running totals, etc. but all three of these solutions prohibit me from getting a grand total, which is critical to this report.
I'm at a standstill. I see clear business reasons why fields in both of these tables would need to be combined into one report. Both have information that is critical to the taxes deducted per paycheck. Just one of these tables would not have sufficient information. For example, Table #1 has no tax data, but table #2 has no check number, total check amount, total regular earnings, etc.
------------------------------
Shannon Sadowsky
RKL eSolutions, LLC
------------------------------