sum specific records in a detail

[mkelly]

I have a report the details all jobs done. One of the fields is "Job Type" within this is Lit-1, Lit-2, Lit-3, Dup-1 and Dup-2.

Is there a way to sum only one type of job in the footer?

for example only sum Lit-1 job types?

Any help appriciated

 

[Rich]

Sum(Iif([Job type]=Lit-1,[Job type],0))

 

[mkelly]

Thanks but it either tries to box the lit-1 and then opens with the error lit-1 so I tried to text it "lit-1" and then I come up with a data criteria mismatch.

 

[neileg]

You don't want to sum [Job Type], or should I say you can't because it's a text field. Perhaps you want to count them, or perhaps you want to sum another field in the record.

 

[The_Doc_Man]

I believe the problem is that, in a report, if you don't GROUP on it, you can't SUM on it very easily. So if you don't GROUP on JobType, you cannot directly get the SUM of a particular job type. Which doesn't mean you can't get there from here, but it DOES mean that you have extra work.

Look up the DSum function. Make an unbound control on your report. Make that function the source of the field. Now, the tricky part will be writing the WHERE clause to match the criteria of the detail section. I believe you can do it if the required values are fields available in the group header associated with the detail section. If not, PUT them there (and then maybe make them invisible) so that the DSum will have data sources for the WHERE clauses. Or you can put those "invisible" fields in the group footer if you wish. Anything to assure criterion availability.

 

[mkelly]

Thanks Neileg

by changing the code to:

COUNT(Iif([job type]=Lit-1,[job type],0))

did the trick and only counted those specific records

Thanks again

 

[Rich]

Sum(Iif([job type]=Lit-1,1,0))

 

[mkelly]

Still not getting it it is counting all the record and not distinguising between the types of jobs. I will attach part of the db it is the "Operator production report"
Password is pbowes.

anyone's help is appriciated

I cannot upload it it is to large if you would like to view it please email me at
mark.kelly@pb.com