"sumif" help

[abomb]

Been awhile since I last made a post but could really use some help. I have created a data base to record hours related to specific jobs. This data will be compared to hours quoted. Here's the setup...

Each Job is Unique, with anywhere from one Activity entry to 30. An employee will punch in with his/her ID, Select JobID and Activity. Then later punch out giving an elapsed time. I've been able to create a portal to list all the punches in/out per job, but I can't figure out how to sum up hours per activity and compare to quoted hours. Quick Example:

JobID=1

Employee "A" punches in for "Painting" Activity for 1 hr. Employee "B" punches in for "Painting" Activity for 2 hrs. Employee "C" punches in for "Sanding" for 2 hrs.

Now when I go to Job Table I see a list of all three entries. What I can't get is a sum of all "Painting" hours and all "Sanding" hours based on the job.

I tried using "sum" but it just adds all records for all jobs. Tried "if" and "case" but couldn't make it work. Your help is greatly appreciated.

[Razumovsky]

Hi bomb,

Have a gander at the GetSummary function. You can specify the break field, which would be 'Activity' in your case. You cant break by a related field, so this calc would have to be in your Activity table.

-Raz

[abomb]

Hey Raz,

Thanks for the quick response but that didn't work. The "elapsed time" isn't a summary field, it's a calc field "out" - "in". Should it be? If it is a summary field, won't it just total all the elapsed time fields, and not per each JOBID record. Any other help would be appreciated.

[Razumovsky]

Sure,

create a summary field ElapsedSum, which is the sum of your elapsedtime field.

create a calc field:

getSummary(ElapsedSum; Activity)

-Raz

[abomb]

OK, pretty sure I got it. I have to change a few things in my database that I left out of my post but I get what I need to do (in theory-be back if I can't actually do it). Thanks again Raz.

[abomb]

Hello all,

Couldn't quite get what I wanted. Let Me give some more detail.I am trying to track hours assigned to jobs based on certain activities.

Job, LaborReq, and Activity Table. For every one job, there are multiple "punch-ins" for direct work. When an employee "punches in", a record is created on the LaborReq Table. On the LaborReq screen, I created a VL to JobID so each "punch-in" added to that jobs total, and a VL based on my Activities. I have been able to create a portal to list all laborreqs and sum the total through a summary field for each job. What I can't figure out is how to sum hours per job and by various activities. I was reffered to the getsummary function, which I did get to work, but only if I placed it in the activity table. I would like to get a total, for each Job and Activity, and place in its own field on the Job Table.

What I'm trying to do is track actual hours to quoted hours. I have a table for quoted hours, where it's data entry per our quote, and enter hours for each activity in it's own field. I want to have a report to compare the numbers side by side.

Any help or refference to another post will be most helpful. Thanks in advance.

Sorry about the double post. Know now when and where to post or add to existing post.