something similar to sumproduct...

[uneakbreed]

hi. i'm building a form that displays how many vacation days/sick days/discretionary days, etc are remaining from a specific allotted amount by employee.

i've built the occurrance table with each occurrance code (i.e. sick=2, vacation=3, etc). i've also built a form where you can track occurances by day (with employees as a subform). these occurances have different times and comments attached to them

tblEmployees
-------------
Empkey (PK)
Vacation Allotted
Sick Allotted
etc etc

tblReps
---------
RepID (PK)
EmployeeOccuranceID(FK)
Empkey(FK)
Hours
Comments


tblReason
----------
ReasonCodeID(PK)
ReasonCode
Definition


tblEmployeeOccurancemm
------------------------
EmployeeOccuranceID (PK)
ReasonCodeID(FK)
OccuranceDate


i've set up a query to pull specific Reason Codes and all Reps, how many hours for each occurance and the EmpKey and a calculated field called "VacationRemaining" that looked like:
[TblEmployees.VacationDays]-[tblEmployeeOccurancesmm.ReasonCodeID]

and i attempted to use the dlookup function in the form to display the remaining days (by type) for each rep using a formula similar to:
=IIf(IsNull(DLookUp("VacationRemaining","qryVacation","EmpKey=" & [EmpKey])),(DLookUp("VacationDays","tblEmployees","EmpKey=" & [EmpKey])),(DLookUp("VacationRemaining","qryVacation","EmpKey=" & [EmpKey])))


The dlookup is rather long but solves my issue if a rep hasn't taken ANY vacations/sick days/discretionary, etc.

but, i'm running into a serious issue since the dlookup is only pulling the first instance found for that type and that rep even if the query might have multiple occurances of the same type listed. i attempted to change the query for the Empkey to the group by "count" but that still didn't seem to work.

Does anyone have any suggestions??