Question How To Sort Raw Data (1 Viewer)

[mveda2004]

Hello Everyone

I need to create a absenteeism database for work and I have no idea how to go about doing it. I have attached a sample data for you to see how my table is in access.

so the fields that i have are name, team leader, date, code, department, minutes.

from this raw data I need:
1) a high level summary report that groups all ccps by team leader and how many hours the team leader has and each individual ccp.

2) summarize by code the hours each ccp has and also occurence. if the ccp has been sick 3 days in a row then it is considered 1 occurence. if they have been sick multiple days but are not concurrent then each one is an occurence so i need that info categorized by month and tl.

3) a form where the user can pick what codes they want to see from a check box / list box (which one would be better?) and group those codes by ccp and team leader month over month.

any help in this would b appreciated.

 

[jzwp22]

For some reason I could not download your database. You might have to zip it and then post the zipped file.

so the fields that i have are name, team leader, date, code, department, minutes.

You said that you only have 1 table. Based on your fields indicated above, you probably will need to separate distinct data into multiple tables: one for departments, one for people and then one for capturing the absenteeism

 

[mveda2004]

OK now I have them in separate tables. How do I go about grouping and calculating the hours by team leader and employee

 

[jzwp22]

Can you show how you relate the separate tables such as through a screen shot of your relationship window? Once the relationships are defined, then you would create queries to bring the information together.

 

[mveda2004]

i have attached the database and zipped it. hopefully you can view it.

 

[jzwp22]

I was able to open the database. You have not related your three tables, but before we do that, I have to understand some of the fields. In the employee table, you have the first and last names of the employees and then a field for analyst name and team leader. If other people are related to the employee and those other people are also employees, you have a many-to-many relationship between people, so that will have to be restructured. Can you explain what the analyst & team leader fields are for?

Also, in the abseteeism table are the fields SICK, ULOA and MIA referring to different types of abseteeism? If so, that is not structured correctly. It should go more like this:

tblAbsent
-pkAbsentID primary key, autonumber
-fkEmpID foreign key to table emp
-fkAbsentTypeID foreign key to a table that holds the types of absenteeism
-hoursAbsent

Additionally, could you explain the fields in the rop table?

 

[mveda2004]

so the employee table is a table that is exported from another database that was previously created by somebody else in the organization. Every system in our department has employees a different way.. one system has first name, last name, another has last name, first name and yet another has first name, middle initital and last name.

Tables: All the tables are a raw data dump from different systems in our organization that export to excel. Abseenteeism stats are taken from one system, ROP is call center stats taken from another system. AHT, CHT, ACW and Talk are different states that an employee can be in and how long they were in that state. Few other statistics like dollar value etc will be another table that may be added in later on from yet another source. Again.. all these sources have the employee's name a different way.

Goal: Combine all these different metrics in one report. Report should have all the metrics by CCP and grouped by Team Leader. Every month we go into each source and do a raw data dump into access to pull the most updated report.

 

[jzwp22]

What I would recommend is to create a properly normalized table structure and take your dumped data and migrate it into the normalized structure. When the data has been moved, the tables that have the original dumped data can be deleted. This will allow you to use consistent data. From there, you should be able to create a query to get you to the solution.

We can help you with setting up the normalized structure, but we would still need to understand the nature of the data you have in the various fields as I mentioned in my earlier post.

 

[mveda2004]

So in abseenteeism table, SICK, ULOA and MIA are different types of abseeneeism that an employee is coded as and it is recorded in minutes.

ROP table: ACW, CHT, Talk, are call center stats that are also recorded in minutes.

Employee Information: we have emp id field, we have emp name, their functions (we have about 6 functions/ department), their team leaders. Each team leader has anywhere betwen 14 to 20 employees reporting to them.

 

[awse]

this is useful

 

[jzwp22]

OK. All employees including team leaders should have corresponding records in a table

tblEmployee
-pkEmpID primary key, autonumber
-EmpNo
-txtFName
-txtLName

If an employee is related to another employee, i.e. a leader to a team member, we need a table to capture that relationship

tblRelatedEmployees
-pkRelatedEmpID primary key, autonumber
-fkLEmpID foreign key to tblEmployees--represents the leader
-fkTEmpID foreign key to tblEmployees--represents the team member who reports to the leader

If a leader has 12 people reporting to him, then there would be 12 records in tblRelatedEmployees for that leader.


Now, a table to hold the types of leave/absenteeism

tblLeave
-pkLeaveID primary key, autonumber
-txtLeave

Now relate the employee to the leave they have taken

tblEmployeeLeave
-pkEmpLeaveID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkLeaveID foreign key to tblLeave
-LeaveAmount

Similarly, we need a table for the types of call center stats

tblStatTypes
-pkStatTypeID primary key, autonumber
-txtStatType

Now relate the employee to the statistics

tblEmployeeStats
-pkEmpStatID primary key, autonumber
-fkEmpID foreign key to tblEmployee
-fkStatTypeID foreign key to tblStatTypes
-StatAmount

Note: all foreign key fields need to be long integer number data types

 

[mveda2004]

Thank you for this information. While going through the exercise of creating these tables and relationships I realized something. Right now I am manually putting the pkLeaveID numbers into the tblEmployeeLeave. To do this manually for 200 employees is going to be a very manual process. How do you go around that?

 

[jzwp22]

If you have the data in another electronic format such as a spreadsheet, you can import the spreadsheet into Access as another table and then run some append queries to move the data into the tables you have set up. Once you are done with that, then you can delete the imported data table.

 

[mveda2004]

I will go to work tomorrow and try this. Thank you.