Database Question

[tapsmiled]

Hello,
I am creating a Master Employee Database for my job. It will contain 400+ employees (regularly changing) and will automatically calculate necessary items such as sick time accruals, evaluation dates, etc. I've created that part along with the Data Entry forms to search and update the database. Now, I'm looking to really expand upon the idea.

On a monthly basis, Quality Assurance (QA) needs to be performed on each employee. There are 4 different categories (911, DLE Radio, FR Radio, Teletype) for the types of QA's, and each of the 4 types will contain anywhere from 12-17 different criteria to be judged ("Calls Answered Correctly", "Extended Reasonable Assistance", "Proper Units Assigned", and a myriad of others). The only acceptable answers are: Satisfactory, Non-Satisfactory, and NA. Currently, the Quality Assurances are written on a simple PDF form and not entered into a database, which is what I am looking to do. The Supervisor selects a different category each month. So Employee A's QA for the first month is 911. That particular form contains 17 questions. The second month, a Teletype QA is performed, and that one only contains 12 questions. There is no specific order for which QA is done when, but that can also change in the future.

What I am trying to do is automate the process by having the Supervisor do the QA on an Excel form and have that data dump into a database. From that database, I would be able to track, across all employees, areas that might require additional training or trending problems. Also, I'd like to be able to determine, on an employee by employee basis, the areas of greatest concern.....of an employee consistentlt has issues with the same category, I'd like to be able to find it quickly and easily. I might eventually assign numeric values to each of the questions in order to develop an Employee Efficiency Rating.

I would attach my database, but the file is too large, and the parts that I already have written do not really apply to this question.

So, does anyone have any suggestions or ideas how I can do this? I am trying to become the uality assurance Supervisor, and some sort of Tracker would really get me on the right path. Thanks!!

[mrice]

You don't mention which type of database you are using. If it is Access then the following link may be of interest.

http://office.microsoft.com/en-gb/ac...010015427.aspx

[tapsmiled]

Sorry, I'm trying to do it in Excel. I know next to nothing about Access, and trying to learn it and write what I need in the timeframe I have is not likely. I have most of the components for employee statuses in an Excel database already...been working on it for a while. Incorporating the QA aspect is new.

To clarify my original post, I want to write each of the 4 PDF's as Excel forms. Each of the questions (14-17 depending upon which QA the supervisor is doing) will have an accompanying drop-down listing the 3 possible scores. The crux of my intent is how do I compile the results so that they are available to search in the database. I'm trying to move away from paper, and this is a huge start.

[tapsmiled]

The overall intention of what I would like to do is multi-faceted:
1) I'd like to be able to track recurring issues on an employee by employee basis, so that if the same areas are deemed non-satisfactory, training can be conducted to bring that employee up to speed. Since a single occurence is not grounds for this, I need to track it over time
2) if multiple employees are struggling on the same criteria, I'd like to be able to recognize trends, so that those can be addressed on a global level
3) it doesn't necessarily have to be part of my existing database, but since that was designed as a one-stop shop, it would make sense to be able to correlate the two.

[newdoverman]

1. Are you trying to track not only the categories for the QA but the questions and responses as well?

2. Would the QA category and result be enough?

[tapsmiled]

To be honest, I could track any or all of those. The position I am applying for is being built from the ground up, so whatever can be accomplished would be great.

In a perfect world, a shift supervisor would be able to select an employee in the database, and in one shot, he/she would see date of Hire, tardies, date evaluation is due, and perhaps a summary of QA's or an average QA score if I chose to quantify the answers. It's pretty much an open book right now.

[newdoverman]

I think that you might want to consider having a way of bringing up the data for an employee by making choices from Drop Down lists, Buttons triggering macros etc. so that you see the area that you are interested in and only that area excluding extraneous data for the employee in question.

I suggest that you build this one area at a time, Personal Data, Pay, Attendance, QA etc. Once each area is completed and tested, tie them together on a report sheet that will generate the report from the other worksheets that are pertinent.

Each area would be a table with all employees from which you filter out the data for the employee that you are interested in. This would limit the number of worksheets required but still allow you to create various reports on each employee or even groups of employees or all employees.

I used a database to do this, not in Excel, but the principles are the pretty much the same.

Try putting your ideas down on paper in an Organizational chart style as if you were dealing with only 1 employee so that you don't get overwhelmed by the numbers involved.

[tapsmiled]

Great suggestion. I was brainstorming and came up with a different idea.

I am going to quantify the results and assign a point value to each response, which will give me an Efficiency Evaluation. That EE will be the only number I am going to apply to my existing database. I will assign a button to one of the pages that will bring up the accompanying Evaluation. The supervisor will enter the scores, and the sheet will tabulate the EE. That EE will then dump into the overall database, along with the other items I previously mentioned (date of evaluation, tardies, etc.). So at a quick glance, the supervisor can see the overall score. What I need to figure out is how to assign a macro to a button to save the Evaluation into a folder (with the employee name and date as the name of the file) so that it can be viewed later, thereby eliminating the need for a paper copy.

[newdoverman]

You can record a macro to do what you want then, draw the button (from the Developer Tab, Insert, Form Control, Button). You will get a prompt to assign a macro.