Question Another attendence help request... (1 Viewer)

[ericryd]

Hi all-

New here, and to an extent new to Access. I spent lots of time in Excel and really think Access can handle this request better based on what I know about it.

I know there have been many attendence questions before, but this one is specific to our company.

Our company has a policy where a employee can only be abscent 5 unique times in a rolling 12 month calendar before it becomes an issue. What I mean by unique is, if you call in sick Monday and Tuesday, that only counts as 1 occurrence. We have a time management program already but isn't flexible for our departments needs.

Here is what i'm looking to do and my thoughts on how it would work:


1) Create 2 tables

• EmpInfo - A table that holds employee information (EmpID, Name, Supervisor)
• Occurences - A table that holds all of the actual occurences (Name, Date)

2) Create a form for entering the occurences. Only 5 people would be accessing this database and they would all use this form. The form would have a drop down to select the employee, a second section for selecting the date (whether its a clickable calendar or you type it in), with a submit button. From this form, you should not view previous data.


3) Reports - Here is the tricky part

• Report 1 - A list of all employees and the amount of current occurrences (note that only ones that should count are ones that occurred in the past 12 months from todays date). The page finally should show the date that the next occurrence falls off, as in if you called in sick on 06/30/2007, the next one that would fall off would be 06/30/2008. You should be able to click on an employee and it should open report 2.
• Report 2 - This is an individual report for each employee that shows all of their current occurrences and the date they happened.

I know thats a lot to take in right away so. First off, is that possible? Second, any thoughts on my layout or ideas for a better layout? Third, how hard would something like this to create from scratch? Any recommended resources or ideas?

 

[ericryd]

oops one additional request. Let's say that an employee is currently at 8 occurrences, on the 1st report, after is shows the date of the next occurrence that falls off, it should show the date that the employee will be under 5 occurrences again.

 

[ericryd]

Update-

I've been able to fumble my way through most of this. So far I have completed:

1) Created table for employee information
2) Created table that holds occurrences
3) Created form to add employees
4) Created form to add occurrences
5) Created report to show all employees and the number of occurrences that are within the past 12 months
6) Created report to show all employees and lists all of their occurrences and when they fall off

I'm looking for help for the next 2 parts of this.

1) On the report that lists all employees and the number of occurrences, I want the next field to show when the next occurrence will fall off. So I need a way to compare all of the occurrence dates of each employee individually, and post the oldest one.
2) I need to create the individual report. I envision it like so. When the report is opened, it will first require the user to select an employee from the list, once an employee is picked, the report will open and it will show all of that employees occurrences and when they fall off.

Any help on this would be greatly appreciated!!!!!

 

[TIbbs]

May I just ask, did the name on the table occurences correspond to the name in the employee table?
To avoid repeated data you should have the first table with primary key EmployeeId and the second with it's own key and employeeID as a foreign key.
The report can be undertaken with a query, add a calculated field to see the next fall out date, as it is 1 year from the original date you do the following:
Add a new column to the query;
on the field add: Fallout Date: DateAdd("y",1, [Date])
Date being your field date of occurence, "y" isolates the year and and 1 to add 1 year to the Date value.
Run the report from the query.

 

[neileg]

1) On the report that lists all employees and the number of occurrences, I want the next field to show when the next occurrence will fall off. So I need a way to compare all of the occurrence dates of each employee individually, and post the oldest one.

So you need the oldest occurance that is less than 12 months old. You already know how to get the last 12 months data. Turn the query into an aggregate query and use Min on the date to get the oldest one. Be aware that there may be no occurances so you will need to handle that eventuality.

2) I need to create the individual report. I envision it like so. When the report is opened, it will first require the user to select an employee from the list, once an employee is picked, the report will open and it will show all of that employees occurrences and when they fall off.

You will need to user to select the employee. There are different ways to do this but my preference would be to use a combo box on a form.