Dsum expression help please

[manos39]

I am using Dsum to retrieve totals of empolyees holidays or vacation, and i have this table:



adeiestbl

Vacationid = Autonumber
SurnameId = Number
DateofRequest = Date/Time
numberofdays = Number
Starting = Date/Time
TypeofHolidays = Number

then in a form havind the above table as recoursource, i am using an expression which returns the rest of the 30 days employees have the right to spend in a year:

=nz(IIf(30-nz(DSum("[TypeofHolidays]";"adeiestbl";"Year([Starting]) = " & Year([Starting]) & " And  [Vacationid] <= " & ([Vacationid] & " And  [SurnameId]= " & [SurnameId])))<0;
"CHANGE THE DAYS";30-nz(DSum("[TypeofHolidays]";"adeiestbl";"Year([Starting]) = " & Year([Starting]) & " And  [Vacationid] <= " & ([Vacationid] & " And  [SurnameId ]= " & [SurnameId])))))

The employees have the right to spend 30 days of "TypeofHolidays" in a year and this expression is working if employees spend the the whole amount of their 30 days in the specific year.

But they have the right also to claim their days if they didnt spend them all in a year, in the next year untill almost Easter..

Simplier if an employee has taken 20 days of "TypeofHolidays" in 2011,
when he aplies for holidays in 2012 his whole "TypeofHolidays" amount is 10 + 30 = 40 days.

I tried with Dsum to return the remaings of 30 days of "TypeofHolidays" for Year -1 , but its kind of tricky, and within 2012 my database will not return the right values

I would like some help with Dsum or IIf because i have tried to calculate the value unsuccefully and .. i ve run off ideas

[aytee111]

I don't know if you already have, but you need a table containing TypeOfHolidays which specifies the number of days allowed per year and links to your table above. Then you can use queries to calculate how many days an employee has.

How are the years calculated - is it from hire date or are you using calendar year, in which case there will be pro-rating? How many years can they accumulate days, is it continuous?

One query will total how many days an employee has used - by type, for the year. Link to the Type table and get how many they are allowed. Then subtract one from the other.

You will need to figure all this out "on paper" and then translate it into queries. DSum is not the most efficient way to go about this IMO.