How to Build an Employee Attendance Database?

Question

Hi Folks -

I have what should be a simple question. I'm trying to build a simple database that tracks my employees' daily schedules and their vacation days. The end result I'm looking for is to be able to generate a report that says exactly who will be around on a given day. This report must take into account (a) their schedules (some employees work Monday - Friday; some work Sunday - Thursday, etc.) and (b) any vacation days or planned absences for the employee.

My records are currently structured as such:

I have one table that has a list of all employees' names, their start, end, break, and lunch times, and then a series of seven 'yes/no' fields--one for each day of the week--where I will check off each employees' schedules. For example, an employee that works Monday - Friday will have a check mark in the Monday field, the Tuesday field, the Wednesday field, etc.

I then have a second table that tracks employees' vacation days. I have created a relationship between the "full_name" field in each table.

So, the report I'm generating will have to do a number of things:
1) check to see what day of the week it is and store the value of the day's name in a variable
2) select the field that matches the contents of the variable, and pull any employee's name that has a 'yes' value in that field, meaning that they work that day.
3) for those employees, then scan through the vacation database and remove them from the list if they are on vacation that day.
4) for those employees that are on vacation, add them to a separate table.

The end result: a report that has a table of everyone who's in and another table of everyone who's out.

I can figure out SQL and VBA so I'm not asking you to build this thing for me. But would you be able to point me to any resources that would help me achieve this? Is this even possible?

Many thanks in advance for any help you can give.

Answer 1

One problem I see with this is that it would not take into account changes in an employee's schedule over time...however, as long as you only want to run the query going by current schedules, that wouldn't be an issue. Something like this pseudocode might do...



1) DELETE FROM dailySchedule (optional...if you want to keep old data for historical puposes, that's fine too)

2) accept @startDate from user

3) create a RecordSet employeeCursor from

SELECT employeeID, schM, schT, schW, schTh, schF, schSa, schSu

FROM employeeSchedule

4) Do until employeeCursor.EOF

a) MoveNext.employeeCursor

b) For @thisDate from @startDate thru @startDate + 6 days

i) @thisDay = Weekday(@thisDate)

ii) IF @thisDay = vbMonday & NOT @schM

OR @thisDay = vbTuesday & NOT @schT

OR @thisDay = vbWednesday & NOT @schW

OR @thisDay = vbThursday & NOT @schTh

OR @thisDay = vbFriday & NOT @schF

OR @thisDay = vbSaturday & NOT @schSa

OR @thisDay = vbSunday & NOT @schSu

THEN @empStatus = "Off (unscheduled)"

ELSE SELECT COUNT(*) INTO @vacationDay FROM empVacation

WHERE vacationDay = @thisDate AND employeeID = @employeeID

IF @vacationDay = 0 THEN @empStatus = "On"

ELSE @empStatus = "Off (vacation)"

iii) INSERT INTO dailySchedule

VALUES (@thisDate, @employeeID, @empStatus)



You can then build reports, charts or whatever based on dailySchedule however you please.

Answer 2

what software are you using to run your payroll. it should have that info, all you need to do is to crate query, table...



I used kronos but IDK how big / small your company is. you can even try Win 2K(im not sure on this one thought). Peach Tree, Oracle...... all of therm have Payroll included so all you do is to run a query and it will include every thing you ask for.



some of those software allow you run run a table and export it using Microsoft Access. export it to Access and there you can run a Query and come up with the report you need.



hopes this help somehow.

Answer 3

One option is microsoft access. You can easily make your setup if you take about 2 -3 days to read a how-to book. It gives you alot of flexibility.

Or open www.zoho.com but a tad more complicated