Is this Possible? - Working Days

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

I have tables, forms and reports set up in my database that work perfectly to
track vacation days taken. The problem is in order for the report to look
right I have to put in each day separately.

Is it possible to enter a range of days ex: start: 05/13/08 End: 05/31/08
and have the database recognize each of the individual dates and based on my
table of dates (indicating holidays) enter the data into my calendar as
individual dates making allowances for the holidays and weekend?
 
You either use a table pre-filled with all working days, and use a simple
WHERE clause like:

SELECT workingDay
FROM workingDays
WHERE workingDay BETWEEN startingDateParameter AND endingDateParameter

either still need a table with holidays and week end, plus a driver table,
call it Iotas, one field, iota, with values from 0 to, say, 999, and :

SELECT startingDateParameter + Iota
FROM Iotas
WHERE iota < endingDateParameter
AND startingDateParameter + Iota NOT IN
(SELECT weekEndAndHoliday
FROM weekEndAndHolidayDays
WHERE weekEndAndHoliday BETWEEN startingDateParameter AND
endingDateParameter)




(you can change the NOT IN(SELECT ... ) into an outer join, if it is too
slow).



Hoping it may help,
Vanderghast, Access MVP
 
should be:

WHERE iota < endingDateParameter +1 - startingDateParameter

instead of :

WHERE iota < endingDateParameter
 
Okay, I've tried this and I'm not getting the results I'm looking for. What I
need would be an update query which would take the date ranges entered by the
user and convert them to individual dates (ex 5/12 - 5/15 would returner as
5/12, 5/13, 5/14 and 5/15). The sample below only pulls out those individuals
who are scheduled off during the "workday". Maybe I did something wrong
(Probably)

HELP
 
The query just pull the possible days, in the given interval.

The easiest way to continue would be to create a table out of it, with extra
fields, as required. Once the table is generated, its fields can be updated
as usual.

If you want to create an updatable query, you would have to associate the
actual query with an existing table which will hold the to be updated data,
but I know nothing about that existing table.


Vanderghast, Access MVP


Lori said:
Okay, I've tried this and I'm not getting the results I'm looking for.
What I
need would be an update query which would take the date ranges entered by
the
user and convert them to individual dates (ex 5/12 - 5/15 would returner
as
5/12, 5/13, 5/14 and 5/15). The sample below only pulls out those
individuals
who are scheduled off during the "workday". Maybe I did something wrong
(Probably)

HELP
Click to expand...
 
Back
Top