Use a Value Based on Date 1

[TigerNoire]

I'm currently building a vacation/sick time database with the intention of tracking an employee's time that is granted, used, and when. I've completed this part of it. After showing this to my supervisor, he added the fact that vacation time can be used after two specific dates if the current date is before one or both of the specific dates, to not include the time granted on the specific date in calculating time remaining.
__________________
Example:

John has the following hours attributing to his 132 hour vacation allotment:

Carry-over: 12
Purchased: 60
March: 30
June: 30

The hours listed under March are not able to be used until March 31st of the current year has passed. The hours listed under June are not able to be used until June 30th of the current year has passed.

If John takes a day off in April, his remaining amount that should be shown would be 94 hours, not 124.

If John takes a day off in February, his remaning amount should be 64, not 124.

If John had no hours in carry over or purchased and took a day off in February, his remaining amount should be -8.
______

Suggestions are much appreciated. My research turned up alot but nothing that I could decipher and apply to my situation. Thank you!

Val

 

[Golom]

Need a bit more detail on how you are storing this information. If, for example, it is all in a single record like this

tblVacation
EmployeeID
CarryOverHours
PurchasedHours
MarchHours
JuneHours

requires one approach while a structure like

tblVacation
EmployeeID
HourType (e.g. "CarryOver", "Purchased", ...)
NumberOfHours

dictates a rather different approach.

If you can provide more detail on how the information is stored and exactly what result you expect to see, we may be able to give you some ideas.

 

[dhookom]

Golom gets a star for replying to what could be a most difficult thread.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TigerNoire]

Golom,

My apologies for not getting back with you sooner... I've just gotten back in the office.

The information you asked for (probably more than but better more than less):

Employees (Table)
FileNo
DeptNo
SSN
First
MidInit
Last

Hours Granted (Table)
HGID
FileNo
YearGranted
VacCarryOver
VacPurchased
VacMarch
VacJune
PersGranted

Hours Used (Table)
HUID
FileNo
DateUsed
VacUsed
PersUsed
Reason

(Primary keys are bolded while foreign keys are italicized.)

The Hours Granted table stores basic information regarding the hours that an employee recieves. Hours Used table stores information regarding how many hours an employee uses on what date and for what reason.

As far as what I would like this to do when done, that's outlined in my initial post. This information will be outlined in reports as an end result.

If there's anything you need further, ask and I'll get back with you sooner this time around!

Thank you muchly in advance!!!