Sum based on criteria in multiple fields

P

Pat Z.

Field names:
CreatedBy, CommRep,PaymentDate,PaymentAmount

My recordset should sum the PaymentAmount field if CreatedBy OR CommRep =
CPRYBAL (sales rep name). This works fine when I do this for one Sales Rep
but I have 4 different Sales Reps I need to calculate.

Field: CPrybalTTL: ([Payment Amount])
Total: Sum
Criteria: [CreatedBy]="CPrybal" or [CommRep]="CPRYBAL"

Field: KFetteTTL: ([Payment Amount])
Total: Sum
Criteria: [CreatedBy]="KFette" or [CommRep]="KFette"

As soon as I add the second the rep, the sum for both appear in the
recordset,i.e.
CPrybalttl = 18,309 KFettettl = 18,309
I'm stuck...
Thanks in advance!
 
T

TPratt

You shouldn't put in the criteria section.

It should look more like this:

Field: CPrybalTTL: IIF([CreatedBy]="CPrybal" OR [CommRep]
="CPrybal",[Payment Amount],0)
Total: Sum
Criteria:

Field: KFetteTTL: IIF([CreatedBy]="KFette" OR [CommRep] ="KFette",[Payment
Amount],0)
Total: Sum
Criteria:

and so on...
 
P

Pat Z.

Perfect!! Thanks!!

TPratt said:
You shouldn't put in the criteria section.

It should look more like this:

Field: CPrybalTTL: IIF([CreatedBy]="CPrybal" OR [CommRep]
="CPrybal",[Payment Amount],0)
Total: Sum
Criteria:

Field: KFetteTTL: IIF([CreatedBy]="KFette" OR [CommRep] ="KFette",[Payment
Amount],0)
Total: Sum
Criteria:

and so on...


Pat Z. said:
Field names:
CreatedBy, CommRep,PaymentDate,PaymentAmount

My recordset should sum the PaymentAmount field if CreatedBy OR CommRep =
CPRYBAL (sales rep name). This works fine when I do this for one Sales Rep
but I have 4 different Sales Reps I need to calculate.

Field: CPrybalTTL: ([Payment Amount])
Total: Sum
Criteria: [CreatedBy]="CPrybal" or [CommRep]="CPRYBAL"

Field: KFetteTTL: ([Payment Amount])
Total: Sum
Criteria: [CreatedBy]="KFette" or [CommRep]="KFette"

As soon as I add the second the rep, the sum for both appear in the
recordset,i.e.
CPrybalttl = 18,309 KFettettl = 18,309
I'm stuck...
Thanks in advance!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top