Need help with 'time off' query/report

[cdoyle]

Hi,
I'm working on a DB that will log vacation and other time off for employees.

In my table I have a 'Start Date' and 'End Date' columns for the time off periods. I then have a form, that the user can select the start/end dates using the calendar control.

Now I need to construct a report (and a DAP later) that will looks something like this.

Employees Not working on:
10/18/05
Joe Smith
Jan Dow

10/19
Joe Smith

10/20/05
Joe Smith

the problem I'm running into is, because my table only stores the Start and End date, I'm not sure how to create a report that would list every date inbetween, like above.

I really don't want to have to enter each day manually into the database that an employee wants off, just enter a date span would be much eaiser.

Anyone have an idea how to accomplish this?

Thanks
Chris.

 

[supercharge]

Somebody may have a better idea, by the way. I'm just brainstorming about this.

In stead of using two columns for StartDate and EndDate, I would use just one, DateOff. An employee will still be able to select the StartDate and EndDate but these two are unbound controls. When an employee selects the start and end dates and hit OK or whatever button, you can use a query to auto-fill the table with all the dates between (using a for loop, can be done very easily, keep posting if you need help on this). This way, you can generate a report just like what you described.

Hope this helps you out.

Life goes on!!!

 

[cdoyle]

Somebody may have a better idea, by the way. I'm just brainstorming about this.

In stead of using two columns for StartDate and EndDate, I would use just one, DateOff. An employee will still be able to select the StartDate and EndDate but these two are unbound controls. When an employee selects the start and end dates and hit OK or whatever button, you can use a query to auto-fill the table with all the dates between (using a for loop, can be done very easily, keep posting if you need help on this). This way, you can generate a report just like what you described.

Hope this helps you out.

Life goes on!!!

This sounds like a plan, I'll give this a try.
I've never created a loop before. How do I go about doing that?

Thanks again
Chris.

 

[supercharge]

How do I go about doing that?

See attached, you'll get the ideas.

Life goes on!!!

 

[cdoyle]

cool, that works pretty good. Thank You.

Have another question, not sure if this will make it much more difficult or not. But is there a way to exclude weekends, and maybe holidays?

Thanks again
Chris.

 

[supercharge]

cool, that works pretty good. Thank You.

You're welcome!

Have another question, not sure if this will make it much more difficult or not. But is there a way to exclude weekends, and maybe holidays?

Thanks again
Chris.

You're are indeed correct. It will be difficult because as you know, weekend dates changes every month and year but why bother, just leave them in there. Holidays are better though 'cause some of them are set-dates. So yup, we can exclude holidays. This just creates more work after all, just be aware.

 

[cdoyle]

The reason I'm thinking it might be needed to remove the weekends is because managment might want to run a report to see how much time off was used for during a month. I'm affraid that because weekends are counted, if they skim the report it might look like they have taken 2 extra days off, when in reality it was the weekend.

What would be a good way to start on removing the holidays?

Thanks again, you've been a big help!!

Chris.

 

[supercharge]

Someone else may have a better ideas. This is what I would do for now.

I would create a one-column table, ExcludedDates. Of course, every year, these dates will change. You will have to enter in the table all the dates that you want to exclude, weekends and holidays, for one whole year or how many years that you want.

One way, before you run the report, run a "delete" query to remove all the records that have those dates in them. This can be done manually or automatically, that's up to you.

Another way, within the "for" loop, before adding new record, it will need to check the DateOff agaist the new table; if it finds one, skip, if not, add new record. However, this process will take a while, depending on the number of dates because for every date that it's adding, it will have to run a check. This is not efficient coding but you won't have to run the "cleanup" query mentioned above.

How's that?

 

[cdoyle]

Another way, within the "for" loop, before adding new record, it will need to check the DateOff agaist the new table; if it finds one, skip, if not, add new record. However, this process will take a while, depending on the number of dates because for every date that it's adding, it will have to run a check. This is not efficient coding but you won't have to run the "cleanup" query mentioned above.

How's that?

It's been awhile since I've worked on this DB, but now I'm back at it.

How do I go about creating this 'for' loop? From how you describe it, sounds like it will work.

I have the dateoff field in my main table, I then created a table named holidays. what do I do next in my query, to excludes any dates that match the entries in the 'holiday' table.

Thanks
chris.

 

[cdoyle]

Are you still here Supercharge?

I hope so, your suggestion sounds like it will work, I just don't know how to accomplish it.

 

[uberDave]

hey cdoyle i'm working on exactley the same kinda project as you, just an HR app to keep track of staff leave (vacation, sickness, medical etc.) I'm using a start_date and end_date for all of my calculations and i'm trying to exclude the weekends. If I have any luck i'll post my work here. If anyone else can help i'd really appreciate it.

Thanks

 

[uberDave]

http://www.access-programmers.co.uk/forums/showthread.php?t=87474&highlight=weekend


ha ha already been covered on this forum, everything you need should be here cdoyle, this has helped me solve my problem!

 

[cdoyle]

I'm not really sure if that's what I need.

Any idea how to modify that query to not display any dates that match what is listed on the 'holiday' table? My report display all the time off requests, and I don't want it to display holidays at all. I think it can be done, just not sure how to write it.

I sort liked Supercharge's idea, from how he described it. If a date span is entered. 12/21-12/26 for example. Since 12/25 would match the date in the holiday table, that day would not be entered into the database, but the rest would.

 

[supercharge]

Run the attached sample then open the table up to see what dates have been recorded or excluded.

Good Luck!

 

[cdoyle]

That's perfect!!

Thanks You!

 

[cdoyle]

hello again,
I just noticed something today, and I might be just not seeing why it's doing this.

I'm running the form in the zip you had attached. For testing, I have 1/2/06 as the only holiday.

when I run the form, and lets say I put down a start date of 1/1/2006 and end date of 1/4/2006

I've noticed that it doesn't insert 1/3 into the database? Even tho that date is not listed on the exclude table. 1/2 should be the only date not entered correct?

Any ideas?

 

[supercharge]

Remove as follow:

        .....
        If DateFound <> 0 Then
            'Do nothing, drink coffee
        Else
        .....