History Table - Lets Try Again

[bhansen]

I've tried posting this before, but didn't have much luck. Now that I understand Crystal a little better, lets try again.

I have three tables I'm dealing with;
1) Employee Information (contains their current payroll overhead info).
2) Transaction Details (contains the date that the payroll transaction was processed and details of such).
3) Overhead History (If employees overhead has changed this table contains dates of such).

Sample Data

Table 1
Employee # | Current Overhead %
KI06 0.43

Table 2
Employee # | Trans Date | Amount
KI06 04/10/02 31.02
KI06 05/11/02 41.02
KI06 05/12/02 51.02

Table 3 – Not all employees are in this table.
Employee # | Start Date | End Date | Previous Overhead %
KI06 01/01/01 12/31/01 0.00
KI06 01/01/02 04/10/02 0.21

Table 2 is joined to Table 3 with a Left Outer Join by employee number.

I need to take the employee transaction date and compare it against my history table, if the transaction date is within the range of one of the records of table 3, I need to return that %. If my transaction date is outside of the highest date of table 3, I need to return the current value from Table 1.

All of my previous attempts have resulted in 3 lines per each transaction date from table 2.

Your help is greatly appreciated.

 

[chelseatech]

It is an invalid set of table joins and your problem is your table structure.

the solution is to use the Employee table and the Transaction details as the main report and get the overhead information in a subreport. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[bhansen]

Okay so I've created a subreport that shows all employee number and any details of history changes that to their overhead.

The subreport shows
Employee Num | Start Date | End Date | Overhead percent|

How do I create a link from my main report to the subreport?Please note that my subreport shows all Employee Numbers, but may not have details for some while others may have 3 or 4 date ranges.

Again, the help is greatly apprciated.