how do I track employee attendence?

[Guest]

I need to set up a tracking method with 6 employees and 5 variables ( weekday
1st or 2nd call, weekend 1st or 2nd. call or vacation) can this be done is
Access or better in Excel and how?

 

[John Vinson]

I need to set up a tracking method with 6 employees and 5 variables ( weekday
1st or 2nd call, weekend 1st or 2nd. call or vacation) can this be done is
Access or better in Excel and how?

I'm biased, of course, but Access would seem preferable. I'd do it
with two tables:

Employees
EmployeeID
LastName
FirstName
<other bio data as needed>

Attendance
EmployeeID <link to Employees>
WorkDate <Date/Time>
Shift <Text>

Use a Form based on Employees with a Subform based on Attendance; the
Shift field could be bound to a Combo Box offering the choices "1st
Call", "2nd Call", "Vacation". You can determine whether it's a
weekday or a weekend from the date:

DatePart("w", [WorkDate], 2)

will be 6 or 7 on weekends, 1-5 during the week.

John W. Vinson[MVP]

 

[Todd Shillam]

Without a doubt, I would go with Microsoft Access--there's so much more you can do with Access with built-in support for forms, queries, reports in comparison to Excel.

I would start by identifying what information (fields) you want to track and the tables you would need, such as:

==========
TABLES
==========
tblAttendence
tblEmployees

==========
tblEmployees
==========
EmployeeID
FirstName
LastName
MiddleName
HireDate
.....

==========
tblAttendence
==========
AttendenceID
EmployeeID
ShiftDate
.....

Note: You can always view the date an employee worked in any format you would like by specifying the format for output in a query, such as:

Expr: Format("ShiftDate", "ddd") 'Displays Day of Week

Good luck,

Todd
I need to set up a tracking method with 6 employees and 5 variables ( weekday
1st or 2nd call, weekend 1st or 2nd. call or vacation) can this be done is
Access or better in Excel and how?

 

[Guest]

Thank you John your answer looks relatively easy...however I am a novice at
Access and you lost me when you addressed the form information. Could you
possibly explain this section again in simple terms?
Thanks,BLMSkier

I need to set up a tracking method with 6 employees and 5 variables ( weekday
1st or 2nd call, weekend 1st or 2nd. call or vacation) can this be done is
Access or better in Excel and how?

I'm biased, of course, but Access would seem preferable. I'd do it
with two tables:

Employees
EmployeeID
LastName
FirstName
<other bio data as needed>

Attendance
EmployeeID <link to Employees>
WorkDate <Date/Time>
Shift <Text>

Use a Form based on Employees with a Subform based on Attendance; the
Shift field could be bound to a Combo Box offering the choices "1st
Call", "2nd Call", "Vacation". You can determine whether it's a
weekday or a weekend from the date:

DatePart("w", [WorkDate], 2)

will be 6 or 7 on weekends, 1-5 during the week.

John W. Vinson[MVP]

 

[Guest]

Thanks for your reply Todd, your suggestion is pretty much the same as John's
in setting up the tables. Where you both loose me however is with regard to
the date info whereby weekdays and weekends are determined by the actual
date? I am an Access novice and don't have a clue as to what I'm doing.
Thanks, BLMSkier

 

[Todd Shillam]

Start with creating the tables first--don't worry about the forms until you get your tables built. After your tables are built, then you can move to creating forms.

Regarding your question about how the date would get displayed, I'll do my best to explain.

Let's say your attendance table stores a date value in the following format:

01/01/2005

Well...that doesn't display the day of the week does it?

What you do is create a query--here's the best explanation of a query that I have heard.

As query is not a table of data, it is a set of instructions to 'find' data in tables and display those results in a table format. So what does this mean? We build a query (a set of instructions) to display the attendance data, including the date with day of the week:

Expr: Format("ShiftDate", "ddd") 'Displays Day of Week

If I added the above expression to a query of the attendance table, I would get the day of the week returned in the results (also called result set) for each record.

If need be, I can send you an example; however, I would need to know what version of Microsoft Access you have.

Best regards,

Todd

Thanks for your reply Todd, your suggestion is pretty much the same as John's
in setting up the tables. Where you both loose me however is with regard to
the date info whereby weekdays and weekends are determined by the actual
date? I am an Access novice and don't have a clue as to what I'm doing.
Thanks, BLMSkier

 

[Guest]

Once again Todd you have been a BIG help! Today I purchased MS Access 2003
Step By Step and I am going to print your suggestions and see if I can put
this all together. I should be able to do this as I took a dBase traiining
course about 25 years ago....however used Lotus everyday of my professional
career which is why I was hoping you would all say that my time off tracking
could be done in Excel.
Thanks again!
BLMSkier also known as Bonnie Mayefsky

 

[Todd Shillam]

Bonnie,

My expression for the query was a little off--here's the correct syntax:

Expr: Format([ShiftDate],"dddd")

If you use only 'ddd' the query only returns 'Sat' for example.

Good luck, and I am glad I could help.

Best regards,

Todd