Question Employee Database with Attendance Tracker

[durga.sarathi]

I have created 5 tables as shown below

tbl_Employees EmployeeId*Number EmployeeNameTexttbl_EmployeeDetails ID*AutoNumber EmployeeIdNumber Designation_At_JoiningText'with combo options "Process Associate";"Sr. Process Associate";"SME";"Team Leader";"Dep Manager";"Assitant Manager";"Trainer" Designation_At_PresentText'with combo options "Process Associate";"Sr. Process Associate";"SME";"Team Leader";"Dep Manager";"Assitant Manager";"Trainer"Initially, this is equal to Join Designation but if the designation is changed in the future, the date would be captured. Designation_Start_DateDate/TimeInitially Equal to DOJ but captures the date for new designation and this change date will be the start date for new ones. Designation_End_DateDate/TimeEnd Date will become start date for the new designation Employee_Active_StatusYes/NoYes for working in the process and No for Movement/IJP/Resign/Terminated, etc ... Employee_Inactive_ReasonTextLateral Movement/IJP/Resignation/Terminated, ... etc Employee_Inactive_DateDate/Time Employee_Is_SupervisorYes/No Employee_Supervisor_StartDateDate/TimeDate when No is made Yes Employee_Supervisor_EndDateDate/TimeDate When Yes is made No Employee_Supervisor_EndReasonTextReason when Yes is made Notbl_TeamDetails ID*AutoNumber EmployeeIdNumber Reporting_To_IdNumber Reporting_Start_DateDate/Time Reporting_End_DateDate/Timetbl_Attendance ID*AutoNumber EmployeeIdNumber Attendance_DateDate/Time Attendance_ShiftText'with combo options "Day Shift";"08:30 - 18:30";"Mid Shift";"15:00 - 00:00";"Night Shift";"19:30 - 04:30" Attendance_StatusText'with combo options "Present";"";"Scheduled Leave";"";"Unscheduled Leave";"Approved";"Unscheduled Leave";"UnApproved";"NCSC" Attendance_Status_ReasonText Attendance_Status_InfoTypeText'with combo options "SMS";"Call";"Email";"Informed by Others/Family Members" Attendance_Late_To_WorkTextValue to be entered in minutes only Attendance_Late_To_Work_ReasonText tbl_Schedule_adherence ID*AutoNumber EmployeeIdNumber Schedule_DateDate/Time Schedule_DelayNumber Schedule_ReasonText Temp_DelayNumber

* is the primary Key

With the relationships as tbl_Employees EmployeeId to all Employees in other tables (1 to Many with Enforce Referential Integrity Checked)

Objective is to create a Emp DB to Add/Modify Employee Details
Maintain Attendance Details and pull up reports Agent wise or team wise or complete data
Kindly suggest or guide me where and what should i start with as i am very new to doing this kind of project.

Thanks in advance.

Regards
Partha.

 

[nanscombe]

Edited to make it easier to read ...

I have created 5 tables as shown below

tbl_Employees
EmployeeId*Number
EmployeeNameText

tbl_EmployeeDetails
ID*AutoNumber
EmployeeId Number
Designation_At_Joining Text 'with combo options "Process Associate";"Sr. Process Associate";"SME";"Team Leader";"Dep Manager";"Assitant Manager";"Trainer"
Designation_At_Present Text 'with combo options "Process Associate";"Sr. Process Associate";"SME";"Team Leader";"Dep Manager";"Assitant Manager";"Trainer"
--Initially, this is equal to Join Designation but if the designation is changed in the future, the date would be captured.
Designation_Start_Date Date/Time Initially Equal to DOJ but captures the date for new designation and this change date will be the start date for new ones.
Designation_End_Date Date/Time End Date will become start date for the new designation
Employee_Active_Status Yes/NoYes for working in the process and No for Movement/IJP/Resign/Terminated, etc ...
Employee_Inactive_Reason Text Lateral Movement/IJP/Resignation/Terminated, ... etc
Employee_Inactive_Date Date/Time
Employee_Is_Supervisor Yes/No
Employee_Supervisor_StartDate Date/Time Date when No is made Yes Employee_Supervisor_EndDateDate/TimeDate When Yes is made No Employee_Supervisor_EndReasonTextReason when Yes is made No

tbl_TeamDetails
ID*AutoNumber
EmployeeId Number
Reporting_To_Id Number
Reporting_Start_Date Date/Time
Reporting_End_Date Date/Time

tbl_Attendance
ID*AutoNumber
EmployeeId Number
Attendance_Date Date/Time
Attendance_Shift Text'with combo options "Day Shift";"08:30 - 18:30";"Mid Shift";"15:00 - 00:00";"Night Shift";"19:30 - 04:30"
Attendance_Status Text 'with combo options "Present";"";"Scheduled Leave";"";"Unscheduled Leave";"Approved";"Unscheduled Leave";"UnApproved";"NCSC"
Attendance_Status_Reason Text
Attendance_Status_InfoType Text 'with combo options "SMS";"Call";"Email";"Informed by Others/Family Members"
Attendance_Late_To_Work Text 'Value to be entered in minutes only
Attendance_Late_To_Work_Reason Text

tbl_Schedule_adherence
ID*AutoNumber
EmployeeId Number
Schedule_Date Date/Time
Schedule_Delay Number
Schedule_Reason Text
Temp_Delay Number

* is the primary Key

With the relationships as tbl_Employees EmployeeId to all Employees in other tables (1 to Many with Enforce Referential Integrity Checked)

Objective is to create a Emp DB to Add/Modify Employee Details
Maintain Attendance Details and pull up reports Agent wise or team wise or complete data
Kindly suggest or guide me where and what should i start with as i am very new to doing this kind of project.

Thanks in advance.

Regards
Partha.

 

[durga.sarathi]

Thank you for correcting the post ..

Please let me know if i should update the database on net so that some one can help me out ??

 

[McSwifty]

Hi,

I used an attendance calendar from these forums ( http://www.access-programmers.co.uk/forums/showthread.php?t=154187 )
I would check this out and modify the details in the tbl_Attendance to match that rather than changing the form to match your data.
It is a good place to start. I have used parts of it and now it looks like this, but a lot more info on each 'cell' and more options based on my needs.
(thanks oxcottin - marvelous sample.)