Comprehensive Attendance Project

[amangupts]

Hi,




I am trying to create a database for daily attendance. Can some please help me in starting how should i design my database. I want an automatic table to be created every month. I should be able to record attendance for each day for each employee.

All this should take place with entry in a form. I am planning to use following five options:

a) Present
b) Work from home
c) Leave
d) Holiday
e) Non Working Day


In case of leave, three options will be available - Approved/Unapproved/Pending for Approval. Something like, a particular day is marked as leave for an employee, so these three options will get activated.

My major cause of concern is automatic switch to next month + updation of employee records.


Any information on how to start would be of great help.

Thanks

Aman

[Bob Fitz]

Hi

I want an automatic table to be created every month

What is the need for a new table every month?

[rpeare]

There's no need to create a new table for every month for an attendance database. But this kind of question is a little open ended, databases are necessarily simple and what you track and what you want to report and HOW you want to report it has a big bearing on it. Likely you would want a table that has your employee information, a table that has your day off types (holiday, vacation, etc). Typically you do not need to record someone being present unless there's a specific reason for it because you can get their present days by subtracting their absent day from total days. A table that recorded the employee, the time off type and the number of hours (if they took partial days). This would probably be the minimum data set then your queries would revolve around what you wanted to report, for instance you want to give bonuses to the people who missed the least amount of work for any reason you could sum the time off by person in a specific month and sort it in descending order to give you the top contenders.

[amangupts]

Hi,

Thanks a lot for your replies. Actually we calculate total number of days worked and days payable through these time sheets, in excel we do something like this for calculation purposes:

We assign following values to the categories defined above:


a) Present-1
b) Work from home-1
c) Leave- if approved-1/if unapproved, it is treated as absent
d) Holiday-1
e) Non Working Day-1

We sum up all these for calculation of monthly leave entitlements, which is 1 leave for 15 days worked and 2 leaves after 20 days of working. So the criteria is something like:

1<15 days- Employee gets entitled to 0 leaves.
15<20 days-Employee gets entitled to 1 leave.
>20 Days-Employee gets entitled to 2 leaves

An employee can accumulate these leaves. An adjustment to these is made when an employee takes a leave.

We also calculate days payable using the same criteria except we include NWD also for calculation for the entire month, i.e. if a month has 30 days, and it has 20 working days, we will use above criteria to determine total days payable: Present+Work from Home+Approved Leaves+Holiday+NWD-Absent.
For leaves: Present+Work from Home+Holiday+Approved Leaves-Absent

I hope I have made somethings clear, please suggest a possible approach to simply these tasks.

Thanks a lot.
Aman

[rpeare]

The approach I mentioned above will be fine for what you want, your attendance model is very simple and realistically keeping track of anything but absent in your database is really not needed, but you probably want to keep it just for your records.

Just to be clear I mentioned three tables
1. An employee table
2. A day off type
3. A day off table which would include an employee ID and a day off ID

When you are doing your monthly totals you would basically just need to take the day part of the last day of the month and extract the total days in the month, then compare it to their total ABSENT days only.

As an example let's say you had an employee with 11 absent days for march (last day is 3/31/xxxx) you would extract the last day of the month with the datepart function then subtract 11 to get 20 days worked, so this person would get 2 leaves.

The only thing I you will probably need in addition to what I previously mentioned is a table tracking the accumulation of leave days. I'm assuming that table 3(mentioned above) will track the subtraction of leave days but you need some way to accumulate them to which can only be done at the end of the month.

[amangupts]

Hi,

Thanks for your detailed reply. Please help me in defining the contents of employee table. Will following be sufficient:

a) Employee ID
b) Employee Name
c) Designation
d) Department

Also please let me know how should I go about in recording day offs. What I mean to say is what content should I display on my form.

Also will append query work in this case?

Aman

[rpeare]

First, don't use spaces in your field names (or any special characters other than the underscore _, it can really cause you a hassle)

Second use compound names if you can things like NAME are reserved words and you want to stay away from accidentally hitting any that could foul up your code.

As for the contents of your database that's really not for me to say, you have a list of what is going to be important to your company not me and I can't devote that type of time to understanding your need unless I'm getting paid

I can't tell you what's going to go in your days off table any easier either because I don't know the need of your company, you do. Think about what is important to YOUR company in tracking time off, it may be as simple as storing a identifier (autonumber), person identifier, time off identifier and what portion of a day it was (if you can have portions of a day full days or there are rigidly upheld portions one can take off, 2 hour increments for example)