Add Fields Based On Criteria

[Jusfire]

Here's what I have...

A table of Employees (EMP) with a field that identifies who their Boss (BOSS) is. A table of Sales Volume (SV) conducted for each Employee.

What I need to do...

Construct a query to give me a total of their Personal Sales Volume AND the Group Sales Volume including all Employees below them.

Example:
EMP BOSS PSV GSV
John 0 650
Bob John 350 550
Nick John 100 100
Dave Bob 100 200
Steve Dave 100 100

Thanks for all the assistance ahead of time.

Justin

 

[Golom]

Try

Select Emp, PSV, 
       (PSV +
        (Select SUM(PSV) From tbl Where tbl.BOSS = T.EMP)) 
       As [GSV]

From tbl T1

Group By Emp

 

[Jusfire]

What you have doesn't return the proper figures.

 

[Jusfire]

Here's my SQL statement

SELECT SV.EmpID, SV.PSV, ([PSV]+(Select SUM(PSV) From SV Where Employees.Boss = SV.EmpID)) AS GSV
FROM Employees, SV
GROUP BY SV.EmpID, SV.PSV;

 

[JonFer]

Since you have multiple levels of bosses, a single select statement on your table won't be able to do it without complicated nested subqueries that would be very difficult to maintain.

I would create a table that lists whose personal goals should roll up into each employee's group goals:

PERSON GROUP
------ -----
John John
Nick John
Bob John
Dave John
Steve John
Nick Nick
Bob Bob
Dave Bob
Steve Bob
Dave Dave
Steve Dave
Steve Steve

I would build this table when needed using a VB script or a macro. Then you can join your table to this one and create your individual and group totals.

Build the table manually first with some test data and create the query to see that it works like you want. In the query, you would have these calculated fields:

Max(IIf(PersonalName=GroupName,PersonalGoal,0)) as PSV
Sum(PersonalGoal) as GSV

Post if you need more help.