Stat Tracking...

[dezeptus]

Just need a general idea on how to do this. I am going to be tracking several stats, in different formats (%, time, decimals, whole numbers, etc etc). Each one has a percentage scale. For example:

Attendance:

0 10% 20% 30% ...... 100% (scale)
16 15 10 8 ...... 0 (occurances)


How would I go about starting this off? So far I created tables for each stat (i.e. I have an Attendance table, with one field being the % for scale and the other field being the goal amount). How should I go about entering in the actual info, and how do I make it reference its corresponding table?


Thanks in advance for your help!

 

[MyBoo]

With what you have provided, simply creating tables to store each stat might be the wrong approach. This will probably cause you a lot more grief creating forms, queries and reports than is actually necessary.

You might be better off creating fewer tables to manage like data... by like data, I mean stats for a particular event, not like data as in time, decimals, etc. I can see two tables at this point... one for identifying the event and one for holding the stats on each event. The fields for the stats table can be configured to store your %, time, etc.

Once you set up the proper relationships between your tables, you may find it easier to create forms, queries and reports than doing it the way you've explained things.

 

[dezeptus]

Thanks for the help. Is there a way you can give me an example on this? Not a big ol example, but just something to point me in the right direction.(Sorry for the delay as well, been working on other projects).

Thanks again!

 

[giacomo]

can you provide some detail on your table structure? I'm having a little trouble following your example.

Giacomo

 

[dezeptus]

Sure.. Here goes as best as I can explain it.

Say I have 8 stat catagories that I need to track (in various formats like time, percentages, whole numbers, etc etc). Each stat has it's own scale that measures performance.

Example, if one of the stats is attendance, then 0 would be 100% met of goal, 1 would be 90%, all the way to 5 and above would be 0% met. Well, every stat has it's own measurement. What I was thinking of doing (and correct me if I'm wrong) would be a formula that says if between X and X then 100% or whatever the measurement would be. But I would do this later in queries right?

What I need is a an idea on how to set up my tables (should I do one for Associates, then one for stats, and where would I put each stats measurements/scale?) and do the formulas in queries.

Hope this helps and thanks in advance.

 

[giacomo]

Well, it's a little clearer. Maybe I'm just slow. If you're just starting out with the structure. Read thru the sites linked in my signature below. Especially the ones regarding Normalization. If you design the database properly this shouldn't be very difficult. How you design will depend on how you want to store the data. If you're just using the db to churn this report then the level you store the data will be different than if you're using it to run other business processes. It's hard to say what is the correct way with the limited data you've provided. Again, I'd start by reading up on Normalization.

hth,
Giacomo

 

[dezeptus]

Ok.. So this is what I have so far..

I have 3 Tables:

CSR - Employee first and last name
Manager - Manager first and last name
Stats - CSR ID, Manager ID, Date, and all the statistic fields (attendance, talk time, etc etc)

I believe from here I can go ahead and start with queries right? In these queries, would this be where I would enter in the weight scale for each stat or would that also be a table?

 

[giacomo]

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:

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

 

[dezeptus]

Thanks for the example. I will try this right now and get back to you with questions if I have any. Thanks again!