Attendance database help

[Coleman984]

I've been posting various questions about a database I'm working on and haven't been making much progress and as a result I've come to the conclusion that maybe I'm asking the wrong questions.

I'm looking for general help in regards to how to create a database to track employee attendance on a weekly basis using excel as the entry form.

Required components are:

Employee Name
Employee ID
DoH (Date of Hire)
Department
Job Title
Days of the week starting with Sunday and ending with Saturday
Total Points (points determined from absences / lates etc.

 

[jzwp22]

If you want a database to do the tracking then go strictly with Access. Data in a spreadsheet such as Excel is typically not normalized so it would not be a good way to enter data in a relational database.

If you want to structure this properly in Access, you will need to create more than one table.

First a table to hold the basic info about the employee

tblEmployee
-pkEmployeeID primary key, autonumber (used by Access to uniquely identify the record; should have no significance to the user)
-txtFname (text field to hold the first name)
-txtLName (text field to hold the last name)
-EmpIDNo (a field to hold the employee's ID number, can be text or a number field depending on your requirements)
-dteHire (date field to hold date of hire)

Now as to the department, can a person be a member of multiple departments over time and is it something you want to track?

With respect to job title, can a person have multiple job titles over time and again is it something you want to track?

With respect to the attendance, since a person attends on many days, you have a one(person)-to-many(attendance days) relationship which requires the attendance info to be in a separate but related table

tblEmployeeAttendance
-pkEmpAttID primary key, autonumber
-fkEmployeeID foreign key field related back to tblEmployee (this needs to be a long integer number datatype field)
-dteAttendance (date of attendance)

Do you need to track the number of hours for each day the person was in attendance? Are you going to track vacation time, sick leave etc.?

How are the points determined?

 

[Coleman984]

A person can have more than one job title simultaneously. A person will not work in more than one department at one time. Simple tracking will take place for such things as vac, sick and what not. Ff a person is sick and it is not backed up by a doctors note then it would counted as Absent (ABS). Points are calculate as follows: ABS or NCNS (No call no show) are 1 point. LE (left early) and LATE are .5 points. Other things would be tracked but would not count towards an employee points. A report should be available to show when an employee has 5 or more points and again at 7 points (2 reports)

I've been working on this but what I'm having trouble with is how to deal with the tables in regards to the weeks. What I mean is, if a separate table is maintained for each week then that would be over 50 tables linked per year. Also weeks older than 52 weeks should be archived to another database. And removed from the current database.

I had the idea of just combining two tables into one each week so that way only 1 table would be in existence, however I don't know how to do that. And I would want the separate tables so that way it would be possible to view when an employee was absent or what not.

So I guess it would be best to have the 50+ tables and the one with the combined info? I'm not sure.

Time is not tracked other than if a person leaves early or not. The purpose of this is only to track tardies, absences and what not. The time punches are handled with a different program.

 

[Coleman984]

The reason why I wanted to use Excel as the entry method is the users know how to excel more than access and may be intimidated with access. I do not posses the knowledge (currently) to create a solution using access that would emulate excel.

 

[spikepl]

Using a separate table to store data, just because it is for a differerent period, is Excel. Not Access. In Access such data is just tagged by the date, and stored in ONE table. How it is diplayed is in a relational database not related to how it is stored.

And in Access users just have to do what is indicated on a form, and not learn Access as such, so don't worry about that part.

 

[jzwp22]

As spikepl points out Access is totally different from Excel in the way the data is structure. As the database programmer, it is your job to design your forms so that they are easy to use by the users of the database.

If you are ready to build an Access solution, we are here to help. Just let us know.

 

[Coleman984]

As spikepl points out Access is totally different from Excel in the way the data is structure. As the database programmer, it is your job to design your forms so that they are easy to use by the users of the database.

If you are ready to build an Access solution, we are here to help. Just let us know.

Of coarse my goal is to use access exclusively however right now my vba skills with excel are better than access so I've been using a part of the program through excel. However if excel could be eliminated that would be fine. I currently have the following tables in my database.

tblEmpDepartments
tblEmpJobs
tblEmpNames
tblTotalPoints_byWeek

tblEmpDepartments contains the following fields. EmployeeID and Department.

tblEmpJobs contains the following fields. EmployeeID and JobTitle.

tblEmpNames contains the following fields. EmployeeID and EmployeeName.

tblTotalPoints_byWeek contains the following fields. refNumber, EmployeeID, Week, Month, Year.
This is the table I'm unsure how it should be designed. I was thinking that this would be the master database with all the points totals for the employees. Should the fields be broken down by week, month, year or should it just be one field? How do I cause entries older than 52 weeks to be moved to an archive table?

 

[Coleman984]

Upon thinking of this more and trying to not think of it as an excel spreadsheet. I think I will also need a table that shows an employees days off. I created one titled tblEmpSchedule and this table contains the following fields. EmployeeID, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday. Now should I have the fields set as Yes/No for if they are off or not. Yes would be off or should I have them as text that shows the text "OFF"? This this the proper structure?

 

[Coleman984]

Using the setup that I've noted above and can't seem to sum the total points. I have a feeling this is due to having a different primary key in the total points table (needed due to the employeeID repeating for each week). How will I be able to create a query that will show the total of all weeks when the primary keys don't match?

 

[jzwp22]

. EmployeeID, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

No, you are still thinking as if you were using Excel.

Going back to the structure I posted earlier:

tblEmployee
-pkEmployeeID primary key, autonumber (used by Access to uniquely identify the record; should have no significance to the user)
-txtFname (text field to hold the first name)
-txtLName (text field to hold the last name)
-EmpIDNo (a field to hold the employee's ID number, can be text or a number field depending on your requirements)
-dteHire (date field to hold date of hire)


tblEmployeeAttendance
-pkEmpAttID primary key, autonumber
-fkEmployeeID foreign key field related back to tblEmployee (this needs to be a long integer number datatype field)
-dteAttendance (date of attendance)
-fkAttendanceTypeID foreign key to tblAttendanceType

The tblAttendanceType will hold all possible types of attendance and their corresponding points if applicable as RECORDS not fields:

tblAttendanceTypes
-pkAttendanceTypeID primary key, autonumber
-txtAttendanceType
-longPoints


Your records in the above table would include:
Vacation
Absent (ABS)
NCNS
LE (left early)
LATE

You would use a query to get the points for a time period based on
tblEmployeeAttendance.

A person can have more than one job title simultaneously

This would require a table to hold all possible job descriptions as records

tblJobDescriptions
-pkJobDescID primary key, autonumber
-txtJobDesc

Now relate the many job descriptions to the employee via this table

tblEmployeeJobDescriptions
-pkEmpJobDescID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees
-fkJobDescID foreign key to tblJobDescriptions

A person will not work in more than one department at one time

That's fine but are interested in tracking people as they move from one department to another? If not then you can include the department reference in the employee table

tblEmployee
-pkEmployeeID primary key, autonumber (used by Access to uniquely identify the record; should have no significance to the user)
-txtFname (text field to hold the first name)
-txtLName (text field to hold the last name)
-EmpIDNo (a field to hold the employee's ID number, can be text or a number field depending on your requirements)
-dteHire (date field to hold date of hire)
-fkDeptID foreign key to tblDepartments

tblDepartments (holds all possible department names as records)
-pkDeptID primary key autonumber
-txtDepartmentName

Now if you do want to track the movement of employees from one dept to another, you would use this structure

tblEmployee
-pkEmployeeID primary key, autonumber (used by Access to uniquely identify the record; should have no significance to the user)
-txtFname (text field to hold the first name)
-txtLName (text field to hold the last name)
-EmpIDNo (a field to hold the employee's ID number, can be text or a number field depending on your requirements)
-dteHire (date field to hold date of hire)

tblEmployeeDept
-pkEmpDeptID primary key, autonumber
-fkEmployeeID foreign key to tblEmployee
-fkDeptID foreign key to tblDepartments
-dteEndEffective (end effective date for the employee in this dept.)


tblTotalPoints_byWeek NOT NEEDED (get using a query)

So I guess it would be best to have the 50+ tables and the one with the combined info? I'm not sure.

No, get data you need using a query from tblEmployeeAttendance

 

[Coleman984]

i will read over this advice and try to make sense of it. Been using excel for so long it is hard to forget my methods for excel. I will just try starting over from scratch using the model you've suggested.

 

[Coleman984]

Will it be alright if the data type for longPoints is double? Or is there a different way to deal with a decimal? Some point values will be .5 which I can't get to show up while the type is set to long.

Also I didn't realize but yes I guess some persons will work in more than one department at a time. Some employees work in produce / deli / and frozen. I'm sorry to have changed this on you. So far everything does seem to be more logical and moving forward.

 

[Coleman984]

I have the database setup as you had outlined jzwp22 (thanks btw). No idea how to get this thing to do anything really as my brain wants things to look like excel for input method. Would I make a form that lists the employees along with the days of the current week? I think it would be the most simple for the users of this database if they were able to view all employees and once and able to make changes to any employee without having to switch records. Is this possible?

When removing an employee how would that be done with a user form, same for adding an employee. I mean I could probably code the actual adding for employees, but since this database setup is beyond my level of understanding (trying to learn access, but I seldom try with easy projects (for beginners when I try to learn something). Before I started this project I never had used access before. some vba experience however from excel. Which I think is honestly more of a problem then a help so far heh.

 

[jzwp22]

Usually in a database, you would not remove an employee (or their associated attendance records). You would only "deactivate" them. This can be done with a simple yes/no field in the employee record or an end effective date. I tend to use date fields since they tell me when the person became inactive. To re-activate some one, you can just clear the date field.

Would I make a form that lists the employees along with the days of the current week?

At this stage of the game, I would use a main form based on the employee table and then a subform based on the employee attendance table to keep it simple. You would then enter the date and the type of attendance for that person. You can get the day of the week using built in functions.

I think it would be the most simple for the users of this database if they were able to view all employees and once and able to make changes to any employee without having to switch records. Is this possible

You have to "switch" records in some way otherwise the data pertinent to one person may get assigned to another.

Keep in mind that it is more important to have a normalized table structure when using a relational database and make your forms work with that structure rather than changing the structure in order to have a form work a particular way.

This site has some tutorials for someone just starting out with Access. Perhaps it will give you a feel for what the tables should look like and how you build the forms around the tables. From there you can decide whether Access is really what you want or whether you would prefer to stay with Excel. I'm a little biased towards Access, of course; I try to do just about everything dealing with datasets in Access rather than Excel.

 

[Coleman984]

I tried using excel, the project frustrated me to no end, I was basically building a database in excel and it was confusing as all hell. Therefore I decided why fight it, this needs to be a database so I might as well learn Access too.

Will a form with Employee First and Last name, EmployeeID in one combo box, a list box with the dates of the current week, and a list box with the attendance types. Along with a submit button work?

One thing I've noticed is that there doesn't appear to be a way in the current design to designate when an employee is off. It is cumbersome to have to enter this in every day when there are around 60 employees scheduled per day, kind of unrealistic to expect the person doing the schedule to memorize everyones days off.

It should be noted that not everyone will only have 2 days off. How can I add days off tracking. As well as LoA tracking (leave of absence).

 

[jzwp22]

One thing I've noticed is that there doesn't appear to be a way in the current design to designate when an employee is off. It is cumbersome to have to enter this in every day when there are around 60 employees scheduled per day, kind of unrealistic to expect the person doing the schedule to memorize everyones days off.

Do you mean each person is off on the same days of the week, every week? For example, John might be off on Tuesdays & Wednesday every week and Joe might be off on Wednesdays and Thursdays. If so, then you can create a table that holds that info and then run an append query to add those days for any period of time (you might need a little code for that). If a person can have many days off that describes a one-to-many relationship, so you need a table related to the employee

tblEmpDaysOff
-pkEmpDaysOffID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees
-longDayNumber (number corresponding to day of the week the person is off)

As well as LoA tracking (leave of absence)

Wouldn't a leave of absence be just another type of attendance? You would enter a record in tblEmployeeAttendance for each day of the leave. You can automate this with some VBA code (specify the employee and beginning and ending dates of the leave and have code that appends a record for each day in tblEmployeeAttendance).

 

[Coleman984]

I'm having trouble setting up an append query. Could you help me with an append query to show how it is done. From there I should know how to do it (well that is how I learned VBA in excel, frankinstening code together till I understood. it.)

What I mean by this is, I'm having trouble getting the query to do anything. I can set it up, but everytime I run it nothing gets copied. Which leads me to believe that I'm not doing something right (only obvious).

 

[Coleman984]

Also do I enter in each employee multiple times into the days off table for each day off?

How would I code it so that when the database is opened the employees that are off are added into tblEmployeeAttendance automatically with the entry "OFF"?

Is it possible to use a subform on a form to add the needed info into the tblEmployeeAttendance table? Meaning if I use a subform query that shows empIDNo, first and last name date and attendance type. Along with a button for submission of the data. I guess the question here is how do I get info out of the subform using vba and put it into tblEmployeeAttendance ? Getting the info out of the list boxes should be easy enough, can just use a rs.additem no?

 

[jzwp22]

If you create forms/subforms that are bound to their respective tables, when data is entered into the forms, it is automatically entered into the bound table. Append queries are not needed when using bound forms. However, if you want to automatically enter records for a person for a date range (like you wanted for leaves of absence then that is when you would need an append query. If you could zip and post a copy of your database (with any sensitive data removed), I can create a couple simple forms and illustrate the append query.

 

[Coleman984]

I did get the append query working. I'm just having a heck of a time understanding how the days off table is doing anything useful. If I zipped everything up do you think you could create a query that would show how many persons per day are scheduled in one department? Based on the tables setup and them not being flagged as off with the daysoff table?

This is frustrating. I'm not sure if I have everything even setup correctly. I think that once everything is setup correctly I can start to make sense of this project and start making the forms needed to get this thing working.

Anyway, attached the database. Thanks so much for your continued help. And sorry if I'm asking the same questions over and over again.