Link to home
Start Free TrialLog in
Avatar of Rakesh SINGH
Rakesh SINGH

asked on

Need database for storing shift roster and at end of month can check employee attendance?

Shift are of four types.: A,B,C,G
After 6 days Off.
How to make this database so that at the end of the month we check there attendance. Shift changes aftet every 2 days.
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Please see https://www.experts-exchange.com/questions/28058187/Tracking-membership-and-attendance-in-Access-Database.html for a good discussion including Patrick Matthews who excels at Access (pun intended).  I think the design fits your needs.  A member is instead an employee, sessions are valid work dates (you could have a different table of shifts unless I am reading correctly on the shift changes then you could have that be a field on your work date*), then your attendance is the unique association of shift/date and employee.

*In many systems, there is a date table with a flag that says valid work day (or the inverse flags weekends/holidays as non-work days).  In your case, you can have this be a column with shift codes A, B, C, G by specific date if only one shift is valid for specific date.  Then the attendance table just uses the PK of the dates table (e.g., DateID) and the PK of the employees table (e.g., EmplID) as a combo unique key.
Here is the gist of Patrick's solution with the changes I said above:

tblEmployees
----------------------------
EmplID (PK)
EmplFName
EmpLName
...

Open in new window

tblShifts
----------------------------
ShiftID (PK)
ShiftCode
ShiftDate

Open in new window

Where ShiftCode is A, B, C ... and appropriate calendar date.  Again if you can have more than one shift on a day, it probably is better to just have this table be a list of dates and whether or not they are valid work days.

tblAttendance
----------------------------
AttendID (PK)
ShiftID (FK) 
EmplID (FK)

Open in new window

If ShiftCode not in the dates table, you can add it here.  If an employee can be in more than one shift a day, then add ShiftCode to the unique index with ShiftID and EmplID so it will let you have duplicates of the employee for a given shift as long as they clock into a different shift code.  I hope that makes sense.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.