Summary based on multi-field relationship

[allanc]

Hi,

I have an Employee file (EmpID as primary key) and Attendance file (EmpID as foreign key). The Attendace contains LogDate {date}, LeaveType {text}, and LeaveCount {number} fields. What i want to do is to display in Employee file total of LeaveCount per year per LeaveType in each employee record (browse mode).

I've tried creating a relationship using calc multi-field combination of EmpID, Year {from LogDate}, and LeaveType in both files but i can't get the right output.

Any help?

[BobWeaver]

I would make a calc field:

cEmpIDYearLeave = EmpID & Year(LogDate) & LeaveType

Then make a selfjoin relationship "sjEmpIDYearLeave" using this field.

Finally make a calc field

cLeaveCount = Count(sjEmpIDYearLeave::cEmpIDYearLeave)

[allanc]

Thanks Bob.

That does it.