Solved

# Highlighting multiple dates on a 12-month calendar

Posted on 2009-02-12
277 Views
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
Question by:OGSan

LVL 44

Expert Comment

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

LVL 44

Expert Comment

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

LVL 1

Author Comment

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

LVL 1

Author Comment

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

LVL 44

Accepted Solution

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
``````
0

LVL 1

Author Comment

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

LVL 1

Author Comment

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

LVL 44

Expert Comment

0

## Featured Post

### Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…