calculate a sum according to 2 fields

[MissT]

I'm trying to calculate a sum with two relationships.

This is my database structure:

*Database - x

--Fields:

total activity (calculation: Sum(activity::activity time total)

job id

staff name

---Relationship: activity, jobid::jobid

*Database - activity

--Fields:

activity time total

job id

staff name

So I have a table in x database that displays:

job id, staff name, total activity

So far i have the records showing the total time of that job id.

I would like it to show the total time where jobid and staff name = whats in the record.

In total activity (calculation: Sum(activity::activity time total) field, it would need to be modified so that it will only sum up the records that equal the job id AND the staff name, because now its summing up by job id only.

Any help would be appreciated.

[T-Square]

You need to create a *second* relationship between x and activity that adds the StaffID as a filter criterion. Then, a sum calculation of records in this "second" activity database will cover only the records for that staff member.

HTH,

David

[MissT]

"Then, a sum calculation of records in this "second" activity database will cover only the records for that staff member."

I don't quite understand what you mean, so i would have a calculation like this:

Sum(*Bystaff::activity time total)

*Bystaff relationship - staff name::staff name

But i need the sum for that staff member and its job ID.

[T-Square]

Oh right. You're in 6, and you can't set up a multi-field join. I recall that the way I used to work around that limitation was to create a stored calc field in each file that concatenates the jobID and StaffName fields, and then build the relationship on them.

Something as simple as:

JobID & StaffID

should suffice.

Cheers,

David

[comment]

A separator would be advisable, to eliminate ambiguities:

JobID & "|" & StaffID

[T-Square]

Yeah. I originally had "." in there (and that's how I always did it), but I figured that it wouldn't be necessary, and so I took it out. I can see how jumbled together, they could result in duplicates.