Get 2nd Name with same ID

[a75537]

I have an Employee table and a PhoneMonitor table which are linked by the EmpID field in the Employee table. I can then pull the FirstName and LastName fields from the Employee table based on the EmpId field in the PhoneMonitor table. This gives me the name of the person who's phone call was monitored.

There is another field in the PhoneMonitor table called Monitor. This field contains the EmpId of the person who monitored the phone call. How can I pull the FirstName and LastName fields from the Employee table for the monitor. When I link the EmpId from the Employee table to both the EmpID and Monitor fields in the PhoneMonitor table I don't get desired results.

Any ideas??

 

[synapsevampire]

Add in the Employee table a second time and join it by the monitor field.

The original join should just be by the Emp_ID

-k

 

[Naith]

This smacks of Big Brother - and frankly, after all my left wing lectures at university, I can't believe I'm now selling out...

Basically, you need to use a subreport. Link the subreport to your EmpID and source the monitoring guy in the subreport, which you can then pass back to the main report via shared variables.

Naith

 

[synapsevampire]

I don't think that they need a subreport, Naith.

Just add in the table again.

a7: subreports are slow, so try to avoid it if possible.

-k

 

[Naith]

I think we posted at the same time. Your pig's ear solution didn't occur to me at the time, but it is the better option.

 

[a75537]

Thanks for your quick replys!

My goal is to have a crosstab report with the Monitor's names across the top (columns) and the employee's names down the side (rows). They intersect at the average score each monitor gave the employee.

Everything works fine except for getting the monitor's names. Currently it just shows the Monitor field - which is the EmpId.

I've created a formula called @Name which simply concatenates the FirstName and LastName fields from the Employee table. This works fine for the employee's names down the side (rows).

synapsevampire - I've added the Employee table a second time and linked it to the PhoneMonitor table based on the Monitor field. When I add the @Name formula to the top (column) it now shows the employees name's across the top, as well as down the side. How can I create another @Name formula for the Monitors??

 

[synapsevampire]

You'll need another formula that points to the first and last from the second employee table.

-k

 

[a75537]

Thank you. This works perfectly. I didn't realize you could add the same table twice.