Tracking vacation on a calendar 2

[stupiet]

I'm building a database to track vacation for each employee.

I've been looking at a lot of tips from other threads but just can't find the one I'm really looking for.

What I've built so far is this:

-a table with all employees listed
-a table that tracks an employee's vacation where they give a start date and how many days the vacation is.

Now I want to build a calendar that list the people gone underneath a given day.
What I'm struggling with is that I can only group the calendar by start date. If they are gone for multiple days I can't somehow list them under the day after using only one entry that gives the range.

If it's confusing here's how it would work:

User enters in vacation that starts 1/23/2006 for 4 days (will end on 1/26/2006) for employee named Karl.
Then User enters in vacation that starts on 1/24/2006 for 2 days (end on 1/25/2006) for emplyee called Susan.

I want the calendar to show the whole month of january. Then under 1/23/2006 Karl's name should show, Under 1/24/2006, both names should show etc.

Is this possible? If worked on this for so long and can't make it to work. Any help would be really appreciated. Calendar does not necessarily need to look like a calendar, as long as the report lists all days of the month.

Thanks!

 

[dhookom]

Create a table of dates with one record per date:
tblDates
TheDate Date/time field

Add values/records for all possible dates and use this in a query;

SELECT tblDates.TheDate, tblThatTracksAnEmployeesVacation.*
FROM tblDates,  tblThatTracksAnEmployeesVacation  
WHERE TheDate Between [Start Date] and DateAdd("d",[HowManyDaysTheVacationIs]-1,[Start Date]);

You can then add your employee table and join appropriate fields.

There are some sample calendar style reports at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BoxHead]

I'd suggest using [ReturnDate] as your field value instead of number of days on vacation (or maybe both). With the return date you don't have to spend as much time calculating vacations that span over weekends, comp days, holidays, etc.

Create a table with all of the days for this year and next (and the next if you'd like): tblDates with field myDate.

The following SQL assumes you have a table named tblVacations with fields named EmpName (text),vStart (date), vReturn (date). Edit as necessary.

It will return a column of all dates that have someone on vacation (multiple people on vacation will return multiple records for that day), a column with the person's name, a column with their vacation start date and a column with their return date.

SELECT tblDates.myDate, tblVacations.EmpName, tblVacations.vStart, tblVacations.vReturn
FROM tblDates, tblVacations
WHERE (((tblVacations.vStart)<=[mydate]) AND ((tblVacations.vReturn)>[mydate]))
ORDER BY tblDates.myDate;

To list all the days of the month, save the above query and build a second query that joins this query with the tblDates table. Join the myDate fields with the join type to include all records from tblDates.

SELECT tblDates.myDate, qryVacationDates.EmpName
FROM tblDates LEFT JOIN qryVacationDates ON tblDates.myDate = qryVacationDates.myDate
WHERE (((tblDates.myDate) Between #1/1/2006# And #1/31/2006#));

If you build a report based on this second query and specify the date range you want to show, it should give you what you need. In the report, Group by myDate with employee names in the detail.


HTH


John

When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[stupiet]

Thanks DHookum and John,

I think what John wrote is exactly what I'm looking for. I'm going to try it out now and wil let you know how it goes.

I appreciate all your help.

 

[bocker]

Try this!

http://www.referee-assistant.com/calcontrol.htm

 

[stupiet]

Boxhead, tried out your code and it worked like a charm. Thanks for all the help, you saved my day!!!

 

[BoxHead]

Glad to help.

If you look close, you'll see Duane said the same thing


Thanks!



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.