Summing Sales Goals, With a Twist

[ryguy7272]

I originally posted this question a couple weeks back, but got preoccupied
with other things. Finally, I have a chance to revisit this issue. I am
hoping to find a way to sum employee’s sales goals, but there is a twist,
because some employees are tied to different teams. For instance, Ben can be
on the following five teams: Beth, Dave, Paul, Warren, and Chris. These five
team leaders (RVPs) have goals. If any of their goals change, it is just
that RVP’s specific goal. However, if a salesperson’s goal changes, it could
impact all of his/her team’s goals. For instance, if Ben’s goal changes, I’d
like to sum the goals for these five RVPs: Beth, Dave, Paul, Warren, and
Chris. I have unique IDs for all these people, so the names was just an
example. The tables are structured as follows:

Table1 = Mapping
Field1 = ID
Field2 = Name
Field3 = VP
Field4 = Changed (this is yes/no DataType)

Table2 = Goals
Field1 = IDKey
Field2 = Employee
Field3 = Goals

In the Mapping table, I’d like to check the Yes/No boxes and then run a
query and see the results. So, if I checked Ben, I’d see a sum of Beth,
Dave, Paul, Warren, and Chris.

I have this query:
SELECT *
FROM Mapping
WHERE Changed = True;

That just shows my the items that were checked yes.


I have this query:
SELECT Goals.IDKey, Goals.Employee, Sum(Goals.Goals) AS SumOfGoals,
Mapping.RVP
FROM Goals LEFT JOIN Mapping ON Goals.IDKey = Mapping.ID
GROUP BY Goals.IDKey, Goals.Employee, Mapping.RVP;

But it only shows me Ben’s goals, and the teams he is tied to.


What am I doing wrong?


Thanks,
Ryan---