Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pulling values from different tables into Detail record

Status
Not open for further replies.

ETCS

Technical User
Joined
Jul 21, 2008
Messages
38
Location
US
I am creating an employee evaluation report. The report is grouped by the employee's manager. Though all the employees in the detail record have the same manager, they have one of two different positions. Their manager fills out evaluations for each employee based on the responsibilities of their position. These evaluations are stored in two separate tables.

All of the tables used in the report are connected by the employee ID. I am able to display the employee name and ID fields in the detail record. However, when I pull values from one of the evaluation tables into the detail record, the employees who hold the other position disappear and vice versa. Is there a way to pull this off without creating a SQL view on the back end to combine the data?
 
Please identify all tables. Do you have to use any selection criteria on the employee tables?

-LB
 
Tables are as follows:

Employee Table (Allows grouping employees by their manager)

Agent Performance Table (Provides performance information that is common to all of the employees

Email Agent Table (Contains evaluations specific to this employee's job responsibilities)

Email Coordinator Table (Contained evaluations specific to this employee's job responsibilities)

All of the tables are linked by the employee's ID.

Selection criteria in the table indicates the employee's manager as well as the month and year of the evaluation.
 
Which table provides the month and year of evaluation, and is that based on a date field or number fields?

-LB
 
Month and Year fields are contained in all four evaluation tables. Criteria selection in the report determines the values via parameter fields. The data types of the Month and Year columns are varchar and smallint respectively.
 
But are you using any table other than the Employee table for selection purposes?

-LB
 
All four of the other tables are being used in the selection formula as the month and year in each must match the month and year presented via the parameter fields.
 
I was trying to determine whether you needed to use a command to accomplish this or not, but it might be cleanest to create a command to use as your datasource where you use a union all, i.e., like this:

select 'EmailAgt' as Table, Employees.`field1`, EmailAgent.`field2`
from Employees left outer join EmailAgent on
Employees.`EmplID` = EmailAgent.`EmplID`
where <your date criteria here>
union all
select 'EmailCoord' as Table, Employees.`field1`, EmailCoordinator.`field2`
from Employees left outer join EmailCoordinator on
Employees.`EmplID` = EmailCoordinator.`EmplID`
where <your date criteria here>

-LB
 
Thanks lbass. I think it would be easiest to do this as well. In addition, the report will most likely pull faster with the data processing happening server side.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top