Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 22 votes, 4.91 average. Display Modes
Old 01-29-2008, 10:36 PM   #1
cmcgill
Registered User
 
Join Date: Jan 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
cmcgill is on a distinguished road
Yearly Attendance Tracker/Calendar

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!

cmcgill is offline   Reply With Quote
Old 07-07-2010, 06:52 AM   #2
Sunnray
Registered User
 
Join Date: Jul 2010
Location: Canada
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Sunnray is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

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.
Sunnray is offline   Reply With Quote
Old 11-07-2010, 06:52 AM   #3
jjnella08
Registered User
 
Join Date: Oct 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
jjnella08 is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

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

jjnella08 is offline   Reply With Quote
Old 11-07-2010, 07:46 PM   #4
Dairy Farmer
Newly Registered User
 
Join Date: Sep 2010
Location: S of E, E of GM
Posts: 244
Thanks: 1
Thanked 22 Times in 12 Posts
Dairy Farmer is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

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)
Dairy Farmer is offline   Reply With Quote
Old 11-07-2010, 09:13 PM   #5
Poppa Smurf
Newly Registered User
 
Join Date: Mar 2008
Location: MAITLAND, NSW Australia
Posts: 444
Thanks: 0
Thanked 19 Times in 18 Posts
Poppa Smurf is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

Quote:
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.
__________________
Poppa Smurf
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Poppa Smurf is offline   Reply With Quote
Old 11-08-2010, 01:29 AM   #6
Dairy Farmer
Newly Registered User
 
Join Date: Sep 2010
Location: S of E, E of GM
Posts: 244
Thanks: 1
Thanked 22 Times in 12 Posts
Dairy Farmer is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

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.
Dairy Farmer is offline   Reply With Quote
Old 11-08-2010, 03:48 AM   #7
Poppa Smurf
Newly Registered User
 
Join Date: Mar 2008
Location: MAITLAND, NSW Australia
Posts: 444
Thanks: 0
Thanked 19 Times in 18 Posts
Poppa Smurf is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

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.

__________________
Poppa Smurf
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Poppa Smurf is offline   Reply With Quote
Old 11-08-2010, 06:53 AM   #8
Dairy Farmer
Newly Registered User
 
Join Date: Sep 2010
Location: S of E, E of GM
Posts: 244
Thanks: 1
Thanked 22 Times in 12 Posts
Dairy Farmer is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

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.
Attached Files
File Type: zip Attendance.zip (99.6 KB, 1680 views)
Dairy Farmer is offline   Reply With Quote
Old 11-12-2010, 05:22 AM   #9
oxicottin
Newbie
 
oxicottin's Avatar
 
Join Date: Jun 2007
Location: West Virginia
Posts: 495
Thanks: 16
Thanked 43 Times in 11 Posts
oxicottin is on a distinguished road
Send a message via AIM to oxicottin
Yearly Attendance Tracker/Calendar

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.
__________________
If guns cause crimes, spoons make you fat....
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by oxicottin; 09-03-2019 at 10:34 AM.
oxicottin is offline   Reply With Quote
The Following 4 Users Say Thank You to oxicottin For This Useful Post:
Bartczakm (12-29-2015), hardlyandy7 (02-23-2011), kdundas (08-06-2014), madhuthanki (03-23-2014)
Old 01-17-2011, 08:20 AM   #10
venu_resoju
Newly Registered User
 
Join Date: Dec 2010
Posts: 130
Thanks: 3
Thanked 0 Times in 0 Posts
venu_resoju is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

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.
venu_resoju is offline   Reply With Quote
Old 02-23-2011, 12:57 PM   #11
hardlyandy7
Newly Registered User
 
Join Date: Feb 2011
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
hardlyandy7 is on a distinguished road
Post Re: Yearly Attendance Tracker/Calendar

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
hardlyandy7 is offline   Reply With Quote
Old 02-24-2011, 12:55 AM   #12
Poppa Smurf
Newly Registered User
 
Join Date: Mar 2008
Location: MAITLAND, NSW Australia
Posts: 444
Thanks: 0
Thanked 19 Times in 18 Posts
Poppa Smurf is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

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.
Attached Images
File Type: jpg Slide1.JPG (43.9 KB, 902 views)
__________________
Poppa Smurf
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Poppa Smurf is offline   Reply With Quote
Old 02-25-2011, 05:24 AM   #13
zaustavitezemlju
Newly Registered User
 
Join Date: Feb 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
zaustavitezemlju is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

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...
Attached Files
File Type: zip calendar.zip (130.2 KB, 1971 views)
zaustavitezemlju is offline   Reply With Quote
Old 03-10-2011, 12:08 PM   #14
fjsmileygirl
Newly Registered User
 
Join Date: Mar 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
fjsmileygirl is on a distinguished road
Smile Re: Yearly Attendance Tracker/Calendar

Quote:
Originally Posted by oxicottin View Post
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
fjsmileygirl is offline   Reply With Quote
Old 03-10-2011, 06:13 PM   #15
oxicottin
Newbie
 
oxicottin's Avatar
 
Join Date: Jun 2007
Location: West Virginia
Posts: 495
Thanks: 16
Thanked 43 Times in 11 Posts
oxicottin is on a distinguished road
Send a message via AIM to oxicottin
Re: Yearly Attendance Tracker/Calendar

Try soggycashew

__________________
If guns cause crimes, spoons make you fat....
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
oxicottin is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Attendance Register? Wingeronside General 1 01-25-2007 07:24 PM
Two foreign Keys in One table. Kelemit General 21 04-25-2006 09:45 PM
Using a list box in a form to record attendance Bigideaguy Forms 0 04-06-2006 11:08 AM
Attendance Form - Need to auto update names and dates Surfette Forms 2 03-28-2006 02:00 AM




All times are GMT -8. The time now is 01:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World