Attendance Database Query problems (rolling, non-rolling, and incetives)

[minasnoldo]

The Attendance Database:

Tables: tblAssociateInfo (general associate info), tblAssociateOccurrences (where the actual occurrences are stored including point value),

Essential fields:

1. 
   - tblAssociateInfo
   
   o EID (primary key; has a 1 to many relationship with EID on tblAssociateOccurrences, this EID is the “1”) (I Use this to return things like the name for reporting (not important for this question)​
   - tblAssociateOccurences
   
   o EID (the key; has a many to 1 relationship with tblAssociateInfo, this is the many)​
   o OccType (Occurrence type. Eg. Tardy less than 30 minutes, Tardy more than 30 minutes, etc) (This is a Combo Box from a table that holds the occurrence types, description of occurrence, and the point value)​
   o Comments (text box for comments)​
   o Date (MM/DD/YYYY; short date format)​
   o Points (how many points the occurrence is worth)​

The problem:

Normal occurrence points roll off after 12 months and no longer reflect on an associates attendance score. This is a fairly straight forward query using DateAdd and SUM. The problem is that we have attendance incentives. For example, if someone has a perfect peak season, they get an “occurrence” (entered into tblAssociateOccurrences) for negative 3 (-3) points (there are other examples with a variety of values). This is really where I am beyond my knowledge level (not that I really had any knowledge to begin with).

The basic rules are as follows:

- Negatives are always added to the total and DO NOT roll off (ever!). But…​

o They can be used up. If an associates total points equals -3 and they call in sick (1 point), their new total is -2.​

o If the associate then goes a whole year with no occurrences, their point total will still be -2 because being in a negative essentially rolled off the accrued point immediately.​

This leaves me in the following predicament:

- I need a query on points for the last 12 months (completed)​

- I need a query (or queries to stick in a report or other query)) that is able to accurately calculate the total amount of points carried into the trailing 12 month period taking in to account​

o Points that rolled of due to time
o Points that rolled off due to a negative balance
o Returns the points as of 1 year plus 1 day (trailing)​

Does that make sense?

Any Suggestions?

 

[plog]

Can you provide some sample data? I will need 2 sets:

A. Sample data from tblAssociatesOccurences
B. What data should ultimately result from your query based on the sample data in A.

Be sure to provide field names and enough sample data to cover all cases.

 

[minasnoldo]

I put an image of the desired output (hope that is what you were looking for), but the forum won't let me upload tblAssociateOccurrences table as an .xlsx file (invalid file error). How do I get you the table?

 

[GinaWhipp]

Hmm, well I *think* I would create a second query with the negative data and then use it in a UNION query. The second query should be offset by the running total of the first query but pay no attention to what year it is. Make sense?

EDIT: Forgot

Welcome to the Forum!

 

[minasnoldo]

Can you provide some sample data? I will need 2 sets:

A. Sample data from tblAssociatesOccurences
B. What data should ultimately result from your query based on the sample data in A.

Be sure to provide field names and enough sample data to cover all cases.

There, I anonymized my sample data (already scrambled) and uploaded the whole Db. I apologize for the mess as I have not gotten to the house cleaning part yet. The report in the example is rptEmployeeOccurrencesIndividual.

edit: An associate that doesn't calculate correctly would be Patrick Styles (EID 712984)

 

[minasnoldo]

Hmm, well I *think* I would create a second query with the negative data and then use it in a UNION query. The second query should be offset by the running total of the first query but pay no attention to what year it is. Make sense?

EDIT: Forgot

Welcome to the Forum!

If I am not misunderstanding you (a real possibility), I am not sure that would work. I am going to mentally run through the logic by typing it: An associate has 4 occurences, going in to peak season. They have a perfect peak season so the 3 oldest points (note: points, not occurrences) are negated. Assuming that 1 remaining point equaled one occurrence, the associate would still have the point until it rolled off 1 year after it happened.

So...

Straight sums (even joined later) don't quite work because there is the time factor involed. An associate can have negative attendance points overal, but they can only get there via incentives. If an attendance point rolls off, the lowest the total can go is 0. The problems that set of rules creates, perpetuates itself into a number of issues.

My main thought right now is that I could append a Points field on to tblAssociateInfo, and then use it to keep a running total. this solves most of the "outside of the last 12 months" issues, but I don't know for the life of me how to work points that roll off into that calculation.

Does that make sense?

(and thanks for the welcome!)

edit: spelling

 

[GinaWhipp]

Let's take this one at a time because now I think I am the one that doesn't understand...

If I am not misunderstanding you (a real possibility), I am not sure that would work. I am going to mentally run through the logic by typing it: An associate has 4 occurences, going in to peak season. They have a perfect peak season so the 3 oldest points (note: points, not occurrences) are negated. Assuming that 1 remaining point equaled one occurrence, the associate would still have the point until it rolled off 1 year after it happened.

Based on the above, if I have a -3 at the end of the yer, does it carry over?

 

[minasnoldo]

Let's take this one at a time because now I think I am the one that doesn't understand...



Based on the above, if I have a -3 at the end of the yer, does it carry over?

Yes.

Occurences that deduct points (such as perfect peak season attendance) are permenant (they don't roll off) and they do stack (there are some long time employess with years of perfect attendance in the negative 20s). It's just that they (usually) get cancelled out by positive points (attendance infractions), which, do roll off the record after 12 months.

I probably should have split my explanation into bite sized chunks, lol (run on sentences are a very personal menace!).

Does that help?

 

[GinaWhipp]

Hmm, okay so based on your statement if I have -3 but then have an attendance infraction which brings me down to -2 then I should stay at -2 even after 12 months rolls over and that negative event drops off. If that is correct then no a query won't work. You're going to need more than a query to accomplish your goal.

 

[minasnoldo]

That would be correct. So if a query won't work, what would my options be?

 

[GinaWhipp]

Thinking out loud...

Sounds to me like going to need a table to hold the -## numbers associated to each entity that will be updated accordingly. That table would have to be a one to one with the main entity table. That combined with a query might do the trick.

 

[plog]

I get an unrecognized format when I try to open your database. Can you post a better file? Also, keep in mind, I want 2 sets of sample data--starting data and then what results should be produced based on that starting data.

 

[GinaWhipp]

@plog

I was able to open the database. Perhaps it's the version, what version of Access do you have?

 

[GinaWhipp]

@minasnoldo

Hmm, before we even go any further you need to change the name of your field Date in table tblAssociateOccurances. This is a Reserved Word for Access and will cause you issues down the road. For a complete list of those words see...
http://allenbrowne.com/AppIssueBadWord.html

In the meantime, have you had a chance to try my suggestion?

 

[plog]

2007. Honestly though, I would prefer not Access because she won't be able to show me what results she wants. An Excel file with starting data on one tab and the expected results on another would be best.

 

[minasnoldo]

Plog - It is an .accdb file. I thought they were 2007 and higher compatible, but it may be an access 2010 or higher only. The main reason I beleive that HR wants a Db of some sort is because using excel sheets for 400+ associates across 20+ departments gets cumbersome.

GinaWhipp - Just got back to work, I will try it this afternoon at somepoint after I rename any Access special names I may have used.

P.S. Thanks for all the ideas so far everyone!

 

[GinaWhipp]

We'll be here...

 

[minasnoldo]

Thinking out loud...

Sounds to me like going to need a table to hold the -## numbers associated to each entity that will be updated accordingly. That table would have to be a one to one with the main entity table. That combined with a query might do the trick.

Which table do you consider the main entity table here? tblAssociateInfo (1 to 1 with an associate - running total) or tblAssociateOccurences (1 to 1 with each occurrence)?

 

[GinaWhipp]

Hmm, see your point... tblAssociateInfo

 

[minasnoldo]

Things got really busy at work so I put this on the back burner for the last 2 weeks. In that time, I was talking to our lead data analyst about it and he thought it would be a fun challenge. So, as time permits we meet and go over each step that needs to be solved (one at a time) and he writes it out in SQL using TOAD and an OCDB driver for the access tables. I then drop the code into a query and test. We hope to have the working solution in about a week but we shall see.

Just wanted to let you know that I have not forgotten you guys and that I still intend to post the solution!

EDIT: Readability