it might help for me to show you how I would do this and then you can take a look and let me know if it works for you. You don't *have* to do all this it's just a suggested format, there is no one right answer...
first of all CSR and Manager are both types of employee so put them into one table:
tblEmployee
employeeID, FName, LName, Title, Manager
Data for this table might look like this:
ID | FName | LName | Title | Manager
1 | John | Doe | CSR | 3
2 | Jane | Smith | CSR | 3
3 | Charlie| Brown | MGR | 4
4 | Bill | Gates | CEO | 0
in the above example John & Jane both report to Charlie, and Charlie reports to Bill, and Bill reports to no one. This is a self-joining table, another way to to this would be to drop the Manager field and create a join table instead.
For the stats table I think youy should use the following format, with your stats in rows not columns:
tlbStat
EmplID, Date, Stat_Name, Value
ID | Emplid | StatDate | stat_name | value
1 | 1 | 11/09/2006 | attendance | 0
2 | 1 | 11/09/2006 | talk time | 5.5
3 | 2 | 11/09/2006 | attendance | 1
4 | 2 | 11/09/2006 | talk time | 4.7
5 | 5 | 11/09/2006 | attendance | 5
6 | 5 | 11/09/2006 | talk time | 3.8
7 | 6 | 11/09/2006 | attendance | 0
8 | 6 | 11/09/2006 | talk time | 3.2
Then finally you need a goal table:
tblGoal
goalID, stat_name, low_value, high_value, Goal_Value
goalID| stat_name | low_value | high_value | Goal_Value
1 | talk time | 0 | 3 | 50%
2 |talk time | 4 | 5 | 75%
3 |talk time | 6 | 10 | 100%
4 |attendance | 0 | 1 | 100%
5 |attendance | 2 | 3 | 80%
6 |attendance | 4 | 5 | 60%
7 |attendance | 6 | 7 | 40%
8 attendance | 8 | 9 | 20%
9 attendance | 10 | 11 | 0%
With the your tables structured as I have them above then you can use a crosstab query to produce the results you're looking for:
Code:
TRANSFORM nz(Count([stat_value]),0) AS Expr1
SELECT tblStat.stat_name
FROM tblStat INNER JOIN tblGoal ON tblStat.stat_name = tblGoal.stat_name
WHERE (((tblStat.stat_value) Between [low_Value] And [high_value]))
GROUP BY tblStat.stat_name
ORDER BY tblGoal.Goal_Value
PIVOT tblGoal.Goal_Value;
hth,
Giacomo