Need to link Benefit tbl and Deduction tbl together but codes are diff

[scotton]

Okay, I need to be able to show an employee with their benefit plan code Ex: PPO and beside it show what that employees deduction code is Ex: MD08. As you can see, these codes are not the same but they represent the same medical coverage; one code is for the HR benefits group and the other is the payroll group deduction code.

Another fly in the ointment is there are multiple deduction codes per benefit plan. Ex: PPO benefit plan code can have deduction code of MD08 for pretax deduction, MD68 employer paid deduction code, and MDX8 is posttax deduction code.

Is there a way to do a union query or create a formula field where I would define the benefit plan codes based off what the deduction codes are and then be able to link by that formula field?

I'm sure there's a way to do this, I'm just not sure the best way to do it.
Thank you,

Sarah

 

[lbass]

Do you need to be able to do calculations with fields from each table or is this just for display? You could create a crosswalk formula and then use it to link a subreport containing one of the tables. I would probably keep the multiple instance table in the main report and use a formula like this:

select {table.deductioncode}
case "MDX8","MD08","MD68" : "PPO"
case "ABC1", "ABD2" : "HMO"
//etc.

-LB