Sum based on Yes/No Field

[ryguy7272]

I posted this question to the Queries DG a few days ago, and didn’t get any
response. I’m trying this ‘General Questions’ section, as it may be more
appropriate for my type of question...

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 Goals for:
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---

 

[Clifford Bass]

Hi Ryan,

There seems to be something missing here. How do you know that Ben is
on those five teams? How do you know who those teams' leaders are? How do
you know if Ben is a team leader or just a member? Can any one team have
multiple leaders? What are Name and VP in Mapping and what is Employee in
Goals? Do you have an employee table? How about a teams table?

By the by, the use of Name for a column name is generally not a good
idea. It can cause troubles because it is a reserved word and as such is
used in other contexts. Better to use something like Mapping_Name. Do a
search for "reserved words" in the online help for other reserved words.

Clifford Bass