Million dollar Question-Access 2000-Dates

[tekewantabe66]

To keep track of vacations I use a form/table that has a start date and end date. The length of the vacation can be 1-5 days long-varies. I need to print a report showing each day each person is off. But say Joe Blow is entered as having 7/1/01-7/4/01 off, I have it report by start date, so it only picks up 7/1/01, it looks like Joe Blow is not on vacation on 7/2 7/3 etc. Any help with this would be appreciated.
Thanks
Karen

 

[tlbroadbent]

Here is a solution using a UNION query. Open the query designer in SQL View and paste the following query. Change the column and table names as needed. The query assumes that [Start Date] and [End Date] are datetime columns rather than text.

Select [Emp Name], [Start Date] As VacDay From Tbl
Union
Select [Emp Name], [Start Date]+1 From Tbl
Where [Start Date]+1<[End Date]
Union
Select [Emp Name], [Start Date]+2 From Tbl
Where [Start Date]+2<[End Date]
Union
Select [Emp Name], [Start Date]+3 From Tbl
Where [Start Date]+3<[End Date]
Union
Select [Emp Name], [End Date] From Tbl
Terry

http://www.defendscouting.com/

&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[MichaelRed]

Darn!!!!

Terry gets the big prize. I could REALLY use the income!!!!

Still, I will offer a comment. Terry's soloution works - for the problem as stated. There ARE a few issues which I am sure will soom arise.

First, if anyone ever gets more than the five days it is a gonner. Beyond that, if the span includes a company (a-la 7/4/xxxx, it will count aginst vacation. This may not be agreeable to employees.

Next, we should consider wheather the entry could/would include 'weekends'. Again, just using the example, Joe has 7/1/01 through 7/4/01 schedualed as &quot;VACATION&quot; who wants to work for a company charging him vacation time for the Sunday (7/1/01) AND a national Holiday? Better yet, what supervisor will be accepting the report? Is the supervisor going to be in the office Sunday (7/1) or Wednesday (7/4) checking that &quot;Joe Blow&quot; did actually take the day off?

I might be tempted to do this for a LOT less than!$$$$$1,000,000.00 - but not without some discussion on the merits/needs of the overall situation.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[PhilipSchroevers]

Just a small remark: if StartDate=EndDate the employee is selected twice for the same day in Terry's solution. Depends on the reportstructure if this is a problem, but why not put [End date] + 4 in the last select?

Nice solutuion though (and a nice challenge from Michael!).