Yearly Attendance Tracker/Calendar (1 Viewer)

cmcgill · Jan 30, 2008

I'm currently attempting to setup a small database to track a few things for the company I work for, namely attendance. Currently they use an excel spreadsheet that covers the current year. This worked great for them when they were still a small company, but with the number of employees shooting from 50'ish to a couple hundred the spreadsheet is just not a viable option any more.

What I need is suggestions on the best way(s) to go about implementing this.

First, I need to do a 1 year look back, on a montly level is fine. So from January 2008 back to January 2007 will work fine. What i have currently setup is a form with 12 of the basic Calender controls built into access 2003. Unfortunatley these wont quite work.

Basic layout of my database so far will be an employees info table, a lookup table for Attendance codes and their infraction values and an attendance (or I suppose more technically an absence) table. I would like to have a form that displays a calendar view for the last 12 months, that will allow me to view / update any absences for an employee. I would also like a way to insert company holidays / shutdowns in to the attendance table in a way that marks it on any employees calendar as a scheduled day off. I assumed i could do this with a simple "all" employee along with the dates and have the calendar search for entries with the Employee ID as well as the All flag and mark those on the calendar.

I think if i can just get the calendar setup covered I can figure out the rest of the data aspects, I'm just stuck on finding a decent way to implement the Calendar Aspect.

Thanks in advance!

Sunnray · Jul 7, 2010

What did you end up with?

I'm currently building a yearly calendar to show attendance at a glance. I am starting with 12 calendar controls on the form but would prefer having 2 6-month blocks.

jjnella08 · Nov 7, 2010

I'm looking for this same solution, though I need to have the dates displayed instead of months. Did you find a answer?

Dairy Farmer · Nov 8, 2010

I have also being looking for an attendance db to replace a spreadsheet. I just can't find a suitable way to transpose the employee fields.

Here is some code that I use in another db to create dates for a full month or range of dates. If also fills in missing dates. Just change the start and end to suite your needs. In my case I have a procedure when the db opens to check runs the code to fill in all date till the end of the current month. So no need for a "create current month" button.

Code:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim D As Date

Set db = DBEngine(0)(0)

Set rst = db.OpenRecordset("Table1", dbOpenDynaset, dbAppendOnly)

On Error Resume Next
For D = [First] To [MonthCurrentEnd]

If D = DateAdd("d", DateDiff("d", 1, D), 1) Then

rst.AddNew
rst("Date1") = D
rst.Update

    End If
    Next D

Dim Msg, Style, Title, response, MyString


    Msg = "Dates between" & vbnewline & [First] & " and " & [MonthCurrentEnd] & vbnewline & "have been created or already exist."
    Style = vbOKOnly + vbInformation
    Title = "Dates Created"

response = MsgBox(Msg, Style, Title)

Poppa Smurf · Nov 8, 2010

I just can't find a suitable way to transpose the employee fields

What problem are you having? Post a sample of the current employees and the required data structure.

Dairy Farmer · Nov 8, 2010

Table1
ID (PK)
EmployeeName
EmployeeNumber
StartDate
EndDate
TerminationReason (FK-T2)

Table2
ID (PK)
TerminationReason

Table3
ID (PK)
AttendanceCode
AttendanceType

Table4
ID (PK)
Date (Indexed*)
Employee (FK-T1, Indexed*)
AttendanceCode (FK-T3)
* Duplicate Date ok if Employee not duplicate

I have code that will automatically append the Table4 with all date and employees for a date range and no AttendanceType.

Table5
Date
PublicHoilday

Fine so far. Now the part I am stuck on. The forms.

Form1 (to look like a spreadsheet)
Left side = Employee (Q1)
Top = Dates in selected month
Now for each emloyee and date I would like enter the AttendanceCode (i.e. P=present, A=Absent, O = Off ....).

So basically I want a "spreadsheet" interface for a full month that is editable.

I had made one that used subforms:
SF0 = Employees
SF1 = 1st day of month
SF2 = 2nd day of month
.....
SF31 = 31st day of month

That worked to a degree.

Poppa Smurf · Nov 8, 2010

I would have another table to record the attendance for the current month. This would have emp_id the PK of table1 and day1 to day31 as fields. In the Datasheet view it weill have the employee name or number in the first column and day1 to day31 displayed across the top but in lieu day1 displayed the caption will be 1 to 31.

In the datasheet view you can freeze the first column similar to Excel and move the day columns as required to make it easier to update.

Now at the end of the month you can use code to transpose each day in the temporary table as records in your attendance table Table4. If you want to go this way I can send you a database that will have an example of transposing rows to columns and columns to rows.

Dairy Farmer · Nov 8, 2010

I have this so far. Please note that I have done very little code and the table structures are not best practice yet. This is just an example of what I mean.

oxicottin · Nov 12, 2010

Here is the one I built in access 2003.... I have data for the year 2008 included so you can see what it does. click on dates to add data ect. there are alot if clickable images and text boxes on calendar so try them all. Hope this helps you and others.... Oh, I tried the "Yearly View" button and it gave me a debug on bold I would remove that and it should work. Everthing worked when I quit using it.

venu_resoju · Jan 17, 2011

Hai.. I am new to this blog and trying to create attendance tracking in access.please help me how to create..I have created 2 tables those who are mentioned below.

TblEmpDetails:
EmpID
EmpName
Address

Tblattendance:
EmpID
EmpName
Attendance

now I want to create one attendance form containing EmpID, EmpName, Attendance Group Bpx(containing "Present"/"Absent" Check/Option) and all the attendance status data should be inserted into Attendance field in TblAttendance table like "Present"/"Absent"
anyone please suggest/guide me. thanks in advance.

hardlyandy7 · Feb 23, 2011

Hi Oxicottin

I need you help on the project above please let me know your mail ID so that i can contact you this is bit urgent please respond to my mail thanks.You can write me to aandrew1983ataol.com

Thanks
Andrew

Poppa Smurf · Feb 24, 2011

Hello Andrew

Have you considered using arrays?

Here is a very short overview of how I use arrays for a work project that is used to record details for each employee. The number of employees vary depending on the criteria selected.

The attached jpg is a possible layout of your input field using a main form and a subform.
Using two arrays and code you can dynamically
- Display your employees with a maximum of ten to a screen, this depends on the screen resolution, font etc.
- Use the navigations buttons at the bottom of the screen to move between screens or pages
- Only display the fields for months that have less than 31 days.
- If the number of employees is say 52 then your will have 5 screens of 10 on each and the last screen will only display the remaining two
employees.
- If you have new employees then the screen will be populated depending on the new total.
- The two arrays are used, one to store the existing data in your attendance table. The second array is used to capture any changes on the screen. When the Save button is selected the second array is compared to the first array and any changes update your attendance table.
- The Cancel will close the form without saving the data.

zaustavitezemlju · Feb 25, 2011

I found this database and modified it for my needs. Because of the design form you will need Access 2007. I hope I helped you...

fjsmileygirl · Mar 10, 2011

oxicottin said:
Here is the one I built in access 2003.... I have data for the year 2008 included so you can see what it does. click on dates to add data ect. there are alot if clickable images and text boxes on calendar so try them all. Hope this helps you and others.... Oh, I tried the "Yearly View" button and it gave me a debug on bold I would remove that and it should work. Everthing worked when I quit using it.

Hi Oxicottin - this is a great database. I would love to "tweek" it a litle bit but it is asking for a password. Would you mind sharing with me.?.? thanks, fjsmileygirl

oxicottin · Mar 11, 2011

Try soggycashew

juice881998 · Nov 11, 2011

oxicottin, how can I see your code? I would really like to learn how it all works because it's great! Thanks.

oxicottin · Nov 11, 2011

oxicottin, how can I see your code? I would really like to learn how it all works because it's great! Thanks.

Im on my phone so i cant open but you should be able to get in the back door by hold the shift key while opening the DB. Good luck!

messiah165 · Feb 21, 2012

oxicottin, if you can contact me via email at messiah_johnson at southwire.com I'd like to talk to you about this DB. I have this same sort of thing set up in excel and would like to convert it over to a DB and would like your guidance.

WimDC · Feb 19, 2013

Hi oxicottin,

I found your Employee Monthly Attendance Year View database and would like to use the principles for my HRM-database.

Is this database build with the built-in-Access components (like unbound textboxes for the dates?) or is almost everything programmed in VBA?
Can you provide me some code, so I can have a look how you made this splendid database?
And how do you update the totals in de left column? VBA?

I'm using Access 2010.

Any help is welcome.

Thanks a lot.

Ramesh_vm · Mar 19, 2013

oxicottin said:
Here is the one I built in access 2003.... I have data for the year 2008 included so you can see what it does. click on dates to add data ect. there are alot if clickable images and text boxes on calendar so try them all. Hope this helps you and others.... Oh, I tried the "Yearly View" button and it gave me a debug on bold I would remove that and it should work. Everthing worked when I quit using it.

Hi there...I am not able to add the Team Name .. So can u pls help me with that...

Yearly Attendance Tracker/Calendar (1 Viewer)

New member

Registered User.

New member

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Learning by pecking away....

Registered User.

New member

Registered User.

Attachments

New member

Attachments

New member

Learning by pecking away....

New member

Learning by pecking away....

New member

Registered User.

New member

Similar threads

Users who are viewing this thread