Linking Tables - Undesirable Results

[a75537]

I have 3 tables:
Employees
Inbound
Outbound

The Inbound and Outbound tables both have the EmpId value as a field to link to the Employees table. The Inbound table contains info on all Inbound calls the employee received, and the Outbound table contains info on all Outbound calls the employee made.

I would like to build a report that that counts total inbound calls and total outbound calls for each employee and adds them together.

The Inbound and Outbound tables each have a field called Score, and my next step is to add all the scores together for each employee. I can then divide by the total number of inbound and outbound calls to have an average score for each employee.

My problem is I can't figure out my links. When I add the three tables and join by the by the EmpId field (Inbound to Employees, and Outbound to Employees) I don't get desired results.

I've also tried adding the Employees table twice - joined one table to Inbound, and the other to Outbound. This results in an ODBC error and the report crashes.

I think my next step is going to have to be a subreport - the main report will be Employees & Inbound, and the subreport will be Employees & Outbound. I really don't want to have to go this route though.

Does anyone know another way to accomplish the above??

Thanks.

 

[pgtek]

hi

your first table should be employee
ex:you link employee table to the other not the site way

employee inbound outbound
empId ---> empId ----> empId

thats an equal joint

cheers

 

[lbass]

I would use your original approach:

Employee----->Inbound
\
v
Outbound

You will get duplicates either way, which you can deal with using conditional formulas or running totals. You can suppress the "messy details" once you have the correct summaries.

-LB

 

[synapsevampire]

If you share your expected output fields, I'll try to work out the SQL for you.

It may be simplest to use a Union Query to build the data out, but it's hard to tell from text descriptions.

-k

 

[a75537]

synapsevampire:

EMPLOYEES - EmpId, Department, Name
INBOUND - EmpId, Date, Score1, Score2, Score3
OUTBOUND - EmpId, Date, Score1, Score2, Score3

I want my output to be:

EMPLOYEES.Deparement
EMPLOYEES.Name @AvgScore

@AvgScore would be the total sum of (INBOUND.Score1 + INBOUND.Score2 + INBOUND.Score3) + (OUTBOUND.Score1 + OUTBOUND.Score2 + OUTBOUND.Score3) divided by the count of (INBOUND.EmpID + OUTBOUND.EmpId) for each employee.

This report would also have a date parameter - only pull from INBOUND and OUTBOUND where their Date fields have a value equal to the input date parameter.

Is it possible to build a union query? My data is stored in a SQL Server database.

Thanks.

 

[synapsevampire]

If you can create a View in SQL Server, build out the Union and filtering of the Inbound and Outbound in a View.

If you don't have View privileges, I think that subreports may be in order.

Either way let me know.

-k