Topic: MSAccess Classification by Incidents

Consider the following MS Access table:

DATE, EMPLOYEE NUMBER, INFRACTION
01-Jan-08, 1, Missed Deadline
02-Jan-08, 1, Missed Deadline
02-Jan-08, 2, Out of Uniform
02-Jan-08, 1, Out of Uniform

I want to be able to classify employees are GOOD, AVERAGE, or BAD based on their infractions. I want to classify 2 ?Missed Deadline? incidents in the past 6 months as BAD, while only 1 is AVERAGE. If they have 1 ?Out of Uniform? in the past year they are BAD, even if they have no ?Missed Deadline? incidents. If they are BAD in any category, then they are BAD; if they are otherwise GOOD but have AVERAGE in any one category then they are AVERAGE, etc.

I?d like to be able to view a list of all the employees (by their number) with their level next to them.

I hope that was not confusing. This is a simplification of what I'm trying to do, it's complex and work-related (and nothing to do with employee infractions). Heh- if I ever found myself tracking employee behavior in Access I have a stiff drink laced with cyanide.

I have no idea how to start doing this in Access, though I have all the data to do it. Does this make sense? Does anyone know how to have Access give me such a list?

(Edited by Petskull on 09-14-2008 17:17)

How much of a simplification is this?
How many records do you anticipate, how many fields do you have?
It it's a small table, few fields, then this could be done in MS Excel in 10 seconds flat including the export of data from Access to Excel.

If you go to Access, you could build additional tables, for example a table with the number as the key and Good, Average, Bad as the description, or you could use conditional statements in a form or report, keeping just one table.

Nature & Travel Photography
Main Entrance

Think 300 employees and dozens of infractions per day. The data is already in Access and updated daily. The table presently has tens of thousands of records. While I can put them into Excel, what I need is that list of 'Employee 26 -> BAD', and I'll need it several times a month.

The problem is that I need to apply a set of rules in order, so it's not a simple SQL query. Instead of a Missed Deadline in the last year or an Out of Uniform incident in the past 6 months, I need to check 13 different categories and come up with an 'Overall Employee Rating' for each guy. The good part is that if he has a 'Missed Deadline' in the past year, then I can tag him BAD and not even worry about the rest of the categories. However, I need to be able to disregard 'Out of Uniform' incidents before 6 months ago even though I need to include Missed Deadlines during all of the past year.

Right now I'll go thru each incident for each employee and count one by one, which sucks. I was hoping to automagify this process but I don't even know where to start. Do I need a form in Access with some VB code? Just SQL statements somewhere? Is there already a GUI way of doing something like this? Etc, etc..

It's a simplification only in that this is the exact problem that I'm trying to solve. In other words, solving this problem would resolve my whole situation. The rest of the context of this is difficult to explain, but irrelevant to what I'm trying to do.

Because you have 13 categories as well as business rules, why not start writing the conditional statements in pseudo code with If then else - nesting them where appropriate.

If infraction = "out of uniform" then .....

Once that's done you would limit the query to the last 6 months or whatever the time period.

Nature & Travel Photography
Main Entrance

I think the code would look something like this:

SELECT WHERE Date >= "1 Year Ago"

IF EmployeeLevel < BAD THEN
If infraction = "Missed Deadline" then EmployeeLevel = BAD
END IF

IF EmployeeLevel < AVERAGE THEN
If infraction = "out of uniform" then EmployeeLevel = AVERAGE
END IF


SELECT WHERE Date >= "6 Months Ago"

IF EmployeeLevel < BAD THEN
If infraction = "out of uniform" then EmployeeLevel = BAD
END IF


....but I wouldn't know what to do with it. Where does it go? Do I even really need to code at all? How do people usually do something like this in Access?