• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Highlighting multiple dates on a 12-month calendar

I have to assist users in identifying patterns of absences taken by employees at our company.  I (will) have a table of absences for each employee - with associated absence code - for all of calendar year 2008.  What I need to do is - for each employee - highlight the dates of absence on a 12-month calendar, and insert the associated absence code used (by the employee)  that day.  One 12-month calendar per employee.  I was fairly certain that this had already been done - but a solution search (for me) has turned up empty.  Is this even possible?
0
OGSan
Asked:
OGSan
  • 4
  • 4
1 Solution
 
GRayLCommented:
Try this  cross tab query:

TRANSFORM Count(AbsenceCode)
SELECT EmpID, Count(AbsenceCode)
FROM tblAbsences
GROUP BY EmpID
WHERE Year(AbsenceDate)=2008
PIVOT Format(AbsenceDate,"yymm") IN ("0801","0802","0803","0804","0805","0806","0807","0808","0809","0810","0811","0812");

Of course use your real table and field names.
0
 
GRayLCommented:
Sorry, you wanted by absence code:

TRANSFORM Count(AbsenceCode)
SELECT EmpID, AbsenceCode, Count(AbsenceCode)
FROM tblAbsences
GROUP BY EmpID, AbsenceCode
WHERE Year(AbsenceDate)=2008
PIVOT Format(AbsenceDate,"yymm") IN ("0801","0802","0803","0804","0805","0806","0807","0808","0809","0810","0811","0812");
0
 
OGSanAuthor Commented:
Thank you for the suggestion, GRayL.  I created the query you describe and it provided numbers for me - but I don't think I explained the desired output clearly enough in my original question.  I've attached a picture below to try and help illustrate what I'm looking for.

12mosCal.jpg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
OGSanAuthor Commented:
The above sample was found at http://www.lebans.com/monthcalendar.htm.  It comes close to doing what I need to do...but I'm trying to find a way to highlight the absence days taken along with an indication of the absence type taken (we have many different reasons for an absence, e.g., Sick 50%, Sick 100%, Injury cont, Hosp 1st Day, Hosp cont., etc., etc.).  I hope this helps clarify what I'm looking to do.  Thanks again!
0
 
GRayLCommented:
So for each employee listed vertically, you could have a 3 or 4 letter abbreviation of the reason for absence going off to the right and in each reason, the number of absences for that employee, along with the yearly total as shown in the snippet.  I imagine you have an employee list to which one could join to get the employee name.  How many absence codes?  With the crosstab, you could have several queries, as I suggested above, as I suggested below, even by day of the week, for the Friday/Monday slacks ;-)






         YrTot S50  s100  Ho1  Ho2 HoC  Inj1  Inj2
John Doe    10   3     2                   1     4

Open in new window

0
 
OGSanAuthor Commented:
Hi, GRayL - I'm not being clear enough, so I'm going to work on coding a solution and will continue this thread showing my progress.  I think once some code becomes visible it will be clearer (I hope) as to what my objective for this project is.  == To be continued! ==
0
 
OGSanAuthor Commented:
I've awarded points even though my question wasn't stated clearly - because the solution offered was a good alternative...and it works well.  I'm going to open up another question in an effort to get assistance with my desired solution.  Thank you, GRayL.
0
 
GRayLCommented:
Thanks, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now