Create calendar with color coded dates

[jazminecat]

Hi - I need to create a report which will color code dates and display in a calendar format, a year per page. This is so our HR manager can see at a glance a single employee's sick, vacation, and other time off over the course of a year. all dates are to be displayed, and the dates the employee is off will be, for example, red for a sick day, and blue for a vacation day. The source for the data is going to be a simple table with EmployeeID as FK to Employee Table, DateOffID as PK, and DateOffType to indicate sick, vacation, leave without pay, suspension, etc. Can anyone point me in the right direction?

Thanks!!

 

[payback]

Have you tried Conditional Formatting?

 

[MichaelRed]

some issues with the number of fields / controls necessary and the proper "calendar" look and feel. more issues in getting this to work on a year to year basis (day of week for starting of each month) how many "weeks" in each month, actual (physical) size of the resultant report ( ... see at a glance ... ) but perhaps only with a sufficiemntly powerful magnifying device?




MichaelRed

 

[rssql]

1. you need a plotter instead of a 8.5x11 printer.

2. on the form, create an object(shape or textbox or label???) for each day. 365 of them named day1, day2, day3, etc.

3. have a table/query which gives a number for the type of day (sick, vacation, tardy,...).

4. When form opens, use code in the form to loop thru the table/query and loop thru the objects. assign the background color of a certain day to a certain object.

5. have a coke and a smile.

 

[ProgramError]

try

http://www.mvps.org/access/resources/downloads.htm

and download the 'attendance calendar' file.

Ian M(uk)



Program Error
Programmers do it one finger at a time!

 

[jazminecat]

We don't have a plotter, but I will take a look at the attendance calendar you linked to ProgramError, and will investigate Conditional Formatting. I know how to set up the tables and accompanying forms so she can enter their sick days and such. She just wants to be able to look a calendar displaying all the days of the year in a monthly format. Sort of like how you view a year in Outlook, and it shows the days you have things scheduled in a darker color. Anyway, on this calendar she wants to see a selected employee's sick days in red, vacation in blue, etc. Her goal here is to be able to look at a year and see if any individual employee has an attendance pattern - taking every other monday as a sick day, etc. Displaying dates in a calendar format seems to be a common issue in Access, from what I can tell in these forums. Unfortunately, I am not getting the feeling this is an easy task. I am using Access 2003, if that is any help.

 

[MichaelRed]

The Calendar itself is not difficult. Showing 365 of anything(s) with individual formatting may be.

There are several to numerous examples of calendars (some with color coding) even in these (Tek-Tips) fora. They are generally based on the concept of a month-at-a-time view, re the more traditional paper models. In the month view, you only need 42 "controls" to represent the complete set of possabilities of the days vs dates. Multiply this by 12 and you suddenly need ~~ 500+. Ms. A. limits the number of controls to 255, so you are thrown into needing some careful planning and use of subforms to even try to do the display.

Having not ventured into this arena, I'm not about to plunge into the helpful hints syndrome.

Other issues would appear to be looming in the future of this project as well, like the actual total real estate necessary. Forms / reports are generally limited in size, so adding the labels and other information controls necessary to the alredy large number of items would appear to make it even more daunting.



MichaelRed