Yearly calender

[oxicottin]

Hello, I have been searching the net for an example on how to create a form that would show the entire years days Example;

Jan 1 2 3 4 5 ect to number of days in that month
Feb 1 2 3 4 5 ect to number of days in that month
Mar 1 2 3 4 5 ect to number of days in that month

I would like to use this for employee attendance so I would have to be able to type a letter like "X" to show the day was worked or "P" if the employee has taken a personal day or "V" for vacation and so on. How would I do this?

Thanks!

 

[TheAceMan1]

How are ya oxicottin . . .

Off the top [blue]your talking 367 fields plus[/blue] any necessary extraneous. This is indicitive of a [blue]SpreadSheet![/blue] However, it is doable in Access, just not in a spreadsheet kind of way.

Assuming you already have an Employees Table, a child table related to Employees of what you specified is in good order. The table would look something like:

[tt][blue]tblAttendance
*************
AttID as Long as PrimaryKey
EmployeeID as Long as ForeignKey to tblEmployees.EmployeeID
attDate as Date
attType as String (X,P,V,Other)[/blue][/tt]

A form based on Employees with subform based on Attendance is all you need for updating.

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[oxicottin]

AceMan, Thanks for the reply! I have been searching the net for months and I have found only 1 example that I think would work for me. The Only thing I would need to do to the example is add some reports and at the bottom of the form there is a few text boxes that total days like vacation. My problem is we have an attendence policy and if an employee has an "Unexcused Tardy" then it doesnt drop off his/her attendance untill 6 moths later and if they aquire 6 within a 6 month period then they are unemployeed. One drops off every 6 months from the day the employee was late. How could I incorperate this into the attached DB? Also, if a vacation day was chosen how could I change the color of the text box to green. I also wanted to say I have been reading and getting little bits of info from this forum for quite some time! This forum is great place for help and information...

Thanks,
Chad

 

[TheAceMan1]

oxicottin . . .

Your latest post should've been your first! Also you've asked for resolution on three different items!

[blue][purple]I would have to be able to type a letter like[/purple] "X" to show the day was worked or "P" if the employee has taken a personal day or "V" for vacation and so on. How would I do this?[/blue]

Your alresdy setup for this with table [blue]tlkCalInput[/blue]. Just include the letter or the description thereof.
Note: you also need to include [blue]Unexcused Tardy[/blue].

[blue] . . . Also, if a vacation day was chosen how could I [purple]change the color[/purple] of the text box to green . . .[/blue]

This is the easiest as the following shows:
[ol][li]Open [blue]frmCalender[/blue] in design view.[/li]
[li]Holding the shift key select all the calendar textboxes (Text1, Text2, Text3, . . .) with the mouse (your group selecting for setting Conditional Formatting).[/li]
[li]From the menubar select [blue]Format[/blue] - [blue]Conditional Formatting[/blue].[/li]
[li]In the left combobox select [blue]Field Value Is[/blue].[/li]
[li]In the next combobox select [blue]equal to[/blue].[/li]
[li]In the textbox enter [blue]"Vacation"[/blue] (include the double quotes).[/li]
[li]Select your desired background/foreground colors and hit OK.[/li]
[li] Save the form then do your testing![/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[oxicottin]

Never thought of doing the color with conditional formating thanks! How would I add an extra text box at the bottom of the calender form just like the ones that show the YTD on vacation and holidays and have it total unexcused tardys but only starting from Date() to six months back? Hope im explaining this well...

Thanks!

 

[MajP]

How would I add an extra text box at the bottom of the calender form just like the ones that show the YTD on vacation and holidays and have it total unexcused tardys but only starting from Date() to six months back

Public Function getTardy(userID As Variant) As Integer
  Dim strWhere As String
  If Not IsNull(userID) Then
    strWhere = "UserID = " & userID & " AND inputText = 'Tardy' AND Inputdate > #" & Format(Date - 183, "mm/dd/yyyy") & "#"
    getTardy = DCount("UserID", "tblInput", strWhere)
  End If
End Function
[code]

on the form in a text box.
=getTardy([cboUser])

 

[MajP]

This design is what I would have originally recommended if you would want to see a month at a time, I have seen this on the web as well, and it is a good start. But, is this really what you want? The original made it sound like you wanted to see a complete year.

Jan 1 2 3 4 5 ect to number of days in that month
Feb 1 2 3 4 5 ect to number of days in that month
Mar 1 2 3 4 5 ect to number of days in that month

The way I envisioned this was a form with 31 textboxes in a row for Jan, and then under that 28/29 textboxes for feb, and under that 31 text boxes for march. Is that more what you want or is the current design correct?

 

[oxicottin]

Yes I would love to see a complete year! And still enter text into each day but the text would be abreviated like this;
1) Vacation would be "V"
2) Personal Day would be "P"
3) Unexcused absence would be "UA"
4) Excused Leave Early would be "ELE"

and so on..... I would also like to keep track of vacation days and personal days for a the year and the Unexcused tardys and so on by using text boxes on the form.

I tried your function and it works great....Thanks!

 

[MajP]

Here is your year view. Not sure if this is what you want.

http://rapidshare.com/files/124162393/VacTrackerWith_YearView.mdb.html

Pretty much done. You just need to figure out when you click on a day of the how to handle the input form. Don't get wrapped around the class module, that just allows me to have all controls fire a single event. Look at the standard module and the common double click event procedure. The rest should be easy.

 

[MajP]

I noticed that you Need to run the clear query on the forms on open event. Also I think you are going to want another field.

you have inputText, but you probably want inputCode. Example the Employee is on vacation , but they are in the local area

inputCode = V
inputText = Some note about being in the local area

That way inputCode only has the codes for calculations and queries. Your example seemed to have a lot of other information in the inputText.

On the year view you could concatentate the field in the display

V
In local area

 

[oxicottin]

The example was created by someone else I just uploaded it for an example. I wanted to change how the month was viewed and I wanted to be able to see the entire year.

Thanks!

 

[fneily]

I've been looking for that example database for nearly five years! I found it back then and have expanded it for use in companies. For your help:
Maybe this'll help since what you're asking for is somewhat like what I did. A gypsum plant with 12 departments. So the tblUser had 12 departments. When a machine part went down it affected different departments. So someone would input a date that those departments would be down. I had that date appear Red on their calendar. I also kept track of vacation time, holidays, etc. I added fields to tblInput, such as DownDayType and checkboxex for which department ordered it. Here's some example code: This produced the down day to turn Red: Notice I put it under PutInData()
Public Sub PutInData()
If rs2.RecordCount > 0 Then
For i = 1 To 37
strdept = ""

If IsDate(f("date" & i)) Then
rs2.FindFirst "inputdate=#" & f("date" & i) & "#"
If Not rs2.NoMatch Then
f("text" & i) = " "
f("text" & i).BackColor = 255
f("text" & i).FontBold = True
f("text" & i).FontSize = 9
If rs2![admin] = True Then f("text" & i) = strdept & " " & "Adm." & " "
strdept = f("text" & i)
If rs2![safety] = True Then f("text" & i) = strdept & " " & "Sfty" & " "
strdept = f("text" & i)
If rs2![quality] = True Then f("text" & i) = strdept & " " & "Qual" & " "
strdept = f("text" & i)
If rs2![instrument] = True Then f("text" & i) = strdept & " " & "Inst" & " "
strdept = f("text" & i)
If rs2![elemain] = True Then f("text" & i) = strdept & " " & "El.M." & " "
strdept = f("text" & i)
If rs2![mill/dock] = True Then f("text" & i) = strdept & " " & "M/D" & " "
strdept = f("text" & i)
If rs2![pur/inv] = True Then f("text" & i) = strdept & " " & "P/I" & " "
strdept = f("text" & i)
If rs2![bp1] = True Then f("text" & i) = strdept & " " & "BP1" & " "
strdept = f("text" & i)
If rs2![bp2] = True Then f("text" & i) = strdept & " " & "BP2" & " "
strdept = f("text" & i)
If rs2![readymix] = True Then f("text" & i) = strdept & " " & "R.M." & " "
strdept = f("text" & i)
If rs2![mechmain] = True Then f("text" & i) = strdept & " " & "Me.M." & " "
strdept = f("text" & i)
If rs2![wh] = True Then f("text" & i) = strdept & " " & "WH"
If rs2![all] = True Then f("text" & i) = "ALL"
End If
End If


This kept track of time off:
Public Function GetVacationAndHolidays()
Dim strSQL
Dim frm As Form
Dim db As Database
Dim rs As Recordset
Dim holdhrs As Integer

Set frm = Forms!frmCalender
Set db = CurrentDb



' Vacation Current Month
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText)='Vacation'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtVacMo = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Vacation YTD
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText)='Vacation'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtVacYTD = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Holidays Current Month
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText) Like '*Holiday*'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtHolMo = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Holidays YTD
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText) Like '*Holiday*'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtHolYTD = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Training Current Month
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText) = 'Training'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtTrMo = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Training YTD
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText) = 'Training'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtTrYTD = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Day Shift Early Current Month
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText) = 'Day Shift Early'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtESMo = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Day Shift Early YTD
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText) = 'Day Shift Early'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtESYTD = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Day Shift Late Current Month
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText) = 'Day Shift Late'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtLSMo = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Day Shift Late YTD
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND ((tblInput.InputText) = 'Day Shift Late'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtLSYTD = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Other Current Month
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND (((tblInput.InputText) = 'Bereavement') OR ((tblInput.InputText) = 'Jury Duty') OR ((tblInput.InputText) = 'Sick Day')));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtOthMo = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

' Other YTD
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & ") AND ((tblInput.UserID)=" & glngUserID & ") AND (((tblInput.InputText) = 'Bereavement') OR ((tblInput.InputText) = 'Jury Duty') OR ((tblInput.InputText) = 'Sick Day')));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtOthYTD = rs!TotDays * holdhrs
rs.Close
strSQL = "'"

Set rs = Nothing
db.Close
Set db = Nothing
End Function

 

[oxicottin]

Majap, That is what I wanted thanks a million..... Sorry for the delay....

 

[oxicottin]

Majap, Im new to Access and still learning so im having a hard time figuring out how this works and how to use what was origionaly used to save data. Could you explain how I could acomplish this? Also, fneily Thanks for the reply it was helpfull.

Thanks,
Chad