P
pete.trudell
I have an extensive Database for the Office which covers all aspects of
operations, administration, etc. It has operated without difficulty
for over 5 years, as an Access 97 database. I have now come accross my
first instance of a Query failing to run because the calculations were
too complex.
The query is to determine the amount of leave a staff members has
coming to them each month. The problems started because although every
staff member earns the same amount of sick leave each month, they earn
different amounts of Vac Leave depending on the amount time they have
worked for the agency. Therefore each month I have to evaluate there
leave amount based on years with the angency.
At First I simply subtracted their LGD (Longevity Date, of hire) from
the first of each month. That worked fine, except that I was informed
by personel that if the staff member started after the first of each
month they didn't earn the new amount until the following month.....
So worked out a simple formula for say Dec. :
DecYr: IIf(Day([LGD]>1,(#11/1/2004#-[LGD])/365,(#12/1/2004#-[LGD])/365)
This way if they were hired on the 2 -31st, they didn't get the each
hour until the following month.
The next formula is complex but has worked smoothly for determining the
amount of leave earned:
VTDec:
IIf([DecYr]>=35,21,IIf([DecYr]>=30,19,IIf([DecYr]>=25,17,IIf([DecYr]>=20,15,IIf([DecYr]>=15,13,IIf([DecYr]>=10,11,IIf([DecYr]>=5,10,IIf([DecYr]>=2,9,IIf([DecYr]<2,8)))))))))
This takes the year amount figured in DecYr and gives the amount of
monthly leave earned.
This last part simply post the leave when Dec 1st arrives:
DecVL: IIf([TDate]>="12/1/2004#,[VTDec],0)
All seemed fine until I ran into those staff members who where hired
late in the month, and even on the next month they failed to get the
extra time??? (Leap years etc.)
So I ran a work around change everybodies hire date to either the 1st.
or the 2nd. No other option for this query.
I created a new field called NLGD - which parsed the month and year,
determined if they had a hire date of the 1ts. or other and reassembled
thier hire dates Now.
DecYr:
IIf(Day([NLGD]>1,(#11/1/2004#-[NLGD])/365,(#12/1/2004#-[NLGD])/365)
Could work except for the sudden error message that the query is too
complex?
I even changed a table that is used to supple fields for this query and
made the DecYr determination there , yet this query will not work....
Too Complex.
Now What do I Do. Our Supervisors and Mangement need accurate
information related to the amount of leave earned, which then is udes
in anorther Query which then subtracts leave taken from earned leave.
I appreciate any guidance. Thanks
operations, administration, etc. It has operated without difficulty
for over 5 years, as an Access 97 database. I have now come accross my
first instance of a Query failing to run because the calculations were
too complex.
The query is to determine the amount of leave a staff members has
coming to them each month. The problems started because although every
staff member earns the same amount of sick leave each month, they earn
different amounts of Vac Leave depending on the amount time they have
worked for the agency. Therefore each month I have to evaluate there
leave amount based on years with the angency.
At First I simply subtracted their LGD (Longevity Date, of hire) from
the first of each month. That worked fine, except that I was informed
by personel that if the staff member started after the first of each
month they didn't earn the new amount until the following month.....
So worked out a simple formula for say Dec. :
DecYr: IIf(Day([LGD]>1,(#11/1/2004#-[LGD])/365,(#12/1/2004#-[LGD])/365)
This way if they were hired on the 2 -31st, they didn't get the each
hour until the following month.
The next formula is complex but has worked smoothly for determining the
amount of leave earned:
VTDec:
IIf([DecYr]>=35,21,IIf([DecYr]>=30,19,IIf([DecYr]>=25,17,IIf([DecYr]>=20,15,IIf([DecYr]>=15,13,IIf([DecYr]>=10,11,IIf([DecYr]>=5,10,IIf([DecYr]>=2,9,IIf([DecYr]<2,8)))))))))
This takes the year amount figured in DecYr and gives the amount of
monthly leave earned.
This last part simply post the leave when Dec 1st arrives:
DecVL: IIf([TDate]>="12/1/2004#,[VTDec],0)
All seemed fine until I ran into those staff members who where hired
late in the month, and even on the next month they failed to get the
extra time??? (Leap years etc.)
So I ran a work around change everybodies hire date to either the 1st.
or the 2nd. No other option for this query.
I created a new field called NLGD - which parsed the month and year,
determined if they had a hire date of the 1ts. or other and reassembled
thier hire dates Now.
DecYr:
IIf(Day([NLGD]>1,(#11/1/2004#-[NLGD])/365,(#12/1/2004#-[NLGD])/365)
Could work except for the sudden error message that the query is too
complex?
I even changed a table that is used to supple fields for this query and
made the DecYr determination there , yet this query will not work....
Too Complex.
Now What do I Do. Our Supervisors and Mangement need accurate
information related to the amount of leave earned, which then is udes
in anorther Query which then subtracts leave taken from earned leave.
I appreciate any guidance. Thanks