R
ryguy7272
I received some great help here the other day. I am now trying to take my
project one step further. The scenario is like this…I have two tables:
Table1 is ‘Goals’ and it consists of IDKey, Employee, and Goals.
Table2 is ‘Mapping’ and it consists of these fields: ID, Name, RVP, and
Changed. The ‘Field’ named ‘Changed ‘ consists of a Yes/No Data Type. I
would like to check a box in the ‘Changed’ Field, and have Access give me the
sum of changes of all Goals for each employee. For instance, I have a query
named ‘qryMappingchanges’; SQL is as follows:
SELECT *
FROM Mapping
WHERE Changed = True;
This gives me the ID, Name, and RVP for each of the persons who experienced
goals changes; the checked items in the Changed Field are displayed as well.
So what I’d like to do is show all people in the ‘Name’ Filed, along with the
RVP they report to (it could be more than one; one employee can report to
multiple RVPs). Anyway, each employee has a Goal. For instance, Opie reports
to both Lee and Jay. On the ‘Goals’ sheet, Lee has a goal of $74,000,000.00
and Jay has a goal of $26,251,632.99. I’d like to check the ‘Changed’ box
next to Opie’s name and get the results transferred to my ‘qryMappingChanges’
which is $100,251,632.99. So, Opie’s cumulative responsibilities are
$74,000,000.00 + $26,251,632.99 = $100,251,632.99. Does that make sense?
Andrew reports to Lee and Mitch. Lee’s Goal is $74,000,000.00 and Mitch’s
Goal is $16,451,382.26; Andrew’s Goal should be $90,451,382.26.
For some additional examples, Pete only reports to Lee, so Pete’s total Goal
is Lee’s total Goals, which is $74,000,000.00. Ben reports to Beth, Dave,
Paul, Warren, and Chris. Only Beth has a Goal, and the Goal is
$229,159,560.53. Thus, Ben's Goal is $229,159,560.53. If Dave, Paul,
Warren, and Chris had a Goal, Ben’s Goal would have to be a sum of all these
Goals.
Any thoughts on how to do this?
Thanks so much,
Ryan---
project one step further. The scenario is like this…I have two tables:
Table1 is ‘Goals’ and it consists of IDKey, Employee, and Goals.
Table2 is ‘Mapping’ and it consists of these fields: ID, Name, RVP, and
Changed. The ‘Field’ named ‘Changed ‘ consists of a Yes/No Data Type. I
would like to check a box in the ‘Changed’ Field, and have Access give me the
sum of changes of all Goals for each employee. For instance, I have a query
named ‘qryMappingchanges’; SQL is as follows:
SELECT *
FROM Mapping
WHERE Changed = True;
This gives me the ID, Name, and RVP for each of the persons who experienced
goals changes; the checked items in the Changed Field are displayed as well.
So what I’d like to do is show all people in the ‘Name’ Filed, along with the
RVP they report to (it could be more than one; one employee can report to
multiple RVPs). Anyway, each employee has a Goal. For instance, Opie reports
to both Lee and Jay. On the ‘Goals’ sheet, Lee has a goal of $74,000,000.00
and Jay has a goal of $26,251,632.99. I’d like to check the ‘Changed’ box
next to Opie’s name and get the results transferred to my ‘qryMappingChanges’
which is $100,251,632.99. So, Opie’s cumulative responsibilities are
$74,000,000.00 + $26,251,632.99 = $100,251,632.99. Does that make sense?
Andrew reports to Lee and Mitch. Lee’s Goal is $74,000,000.00 and Mitch’s
Goal is $16,451,382.26; Andrew’s Goal should be $90,451,382.26.
For some additional examples, Pete only reports to Lee, so Pete’s total Goal
is Lee’s total Goals, which is $74,000,000.00. Ben reports to Beth, Dave,
Paul, Warren, and Chris. Only Beth has a Goal, and the Goal is
$229,159,560.53. Thus, Ben's Goal is $229,159,560.53. If Dave, Paul,
Warren, and Chris had a Goal, Ben’s Goal would have to be a sum of all these
Goals.
Any thoughts on how to do this?
Thanks so much,
Ryan---