[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Highlighting multiple dates on a 12-month calendar

Posted on 2009-02-12
8
Medium Priority
?
328 Views
Last Modified: 2012-05-06
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
Comment
Question by:OGSan
  • 4
  • 4
8 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 23634111
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

by:GRayL
ID: 23634122
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

by:OGSan
ID: 23636591
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:OGSan
ID: 23636646
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

by:
GRayL earned 2000 total points
ID: 23636860
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
 
LVL 1

Author Comment

by:OGSan
ID: 23637085
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

by:OGSan
ID: 23696020
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

by:GRayL
ID: 23696119
Thanks, glad to help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question