Jump to content

calculate a sum according to 2 fields


MissT
 Share

This topic is 5692 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

"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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5692 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.