How to join duplicate fields and summarize data into one query.

[kdestro]

Hi,


I have a table1 with the following fields: Recipient, Sender, & Comment.

Recipient	Sender	Comment
EmployeeA	EmployeeB	Nice job EmployeeA!
EmployeeB	EmployeeC	Way to go!

Recipient and Sender are duplicate employee name fields. I want to create a query that summarizes how many comments the "Sender" has sent and how many comments the "Recipient" has recieved.
So my query would be something like this:

Recipient & Sender combined into one field (NAME).
Count of Total Comments sent from "Sender" (SENT)
Count of Total Comments recieved by "Recipient" (RCVD)

NAME	RCVD	SENT
EmployeeA	1
EmployeeB	1	1
EmplyeeC		1

Thanks for your help! K-

[June7]

Try:

1. Create two aggregate queries, one that counts Recieved and one that counts Sent, group by respective employee ID field

2. Another query that joins the first two on the employee ID fields

[kdestro]

Query - left join on Sender = Recipient. Employee A is missing from this list when joined this way? How do i get EmployeeA to appear on this list?


Sender Rcvd Sent
EmployeeB 1 1
EmployeeC 1

[kdestro]

Thank you June7,
I did step 1 but not sure how to do step two. I will need to do this in sql right?
The sql I have right now is a left join sender=recipient. But this only lists the sender's names and not the recipient name that may have received a comment but didn't send one. Is there another way to join them?

[June7]

I was thinking INNER join but if there is possibility of names in sender and not in recipient (and vice versa) and you want all names, then need a datasource for all names and join the two aggregate queries to the AllNames source. So do you have an EmployeesInfo table?

What do you mean "I will need to do this in sql right?"?
Use the query designer. Drag tables/queries into the window, create link on the ID fields. Drag fields to the grid.

[kdestro]

I created an Employee Table and made the employee name the Primary Key. I then went into query design and added the employee table, ace sent query and ace rcvd query. But now I only get one record. EmployeeB is the only one that shows up because they have sent and recived one. How do I get all employees to show up?
Below is the SQL view:
SELECT Employees.Employee, Sum(ACE_SENT.[Total Sent]) AS [SumOfTotal Sent], Sum(ACE_RCVD.[Total Rcvd]) AS [SumOfTotal Rcvd]
FROM (Employees INNER JOIN ACE_SENT ON Employees.Employee = ACE_SENT.Sender) INNER JOIN ACE_RCVD ON Employees.Employee = ACE_RCVD.To
GROUP BY Employees.Employee;

[June7]

Don't use INNER join. You want 'Include all records from Employees and only those ...' (is that LEFT?).

All three datasets should have one record for each employee and the query should have one record for each employee.

[kdestro]

Oh sweet relief finally! I figured it out. Thank you June7 for your guidance!!!!!

SELECT Employees.Employee, Sum(ACE_SENT.[Total Sent]) AS [SumOfTotal Sent], Sum(ACE_RCVD.[Total Rcvd]) AS [SumOfTotal Rcvd]
FROM (Employees LEFT JOIN ACE_SENT ON Employees.Employee = ACE_SENT.Sender) LEFT JOIN ACE_RCVD ON Employees.Employee = ACE_RCVD.To
GROUP BY Employees.Employee;

[June7]

Should not need the GROUP BY clause for this query. The two aggregate queries use GROUP BY.