Summing certain values only in a report

  • Thread starter Thread starter tsison7
  • Start date Start date
T

tsison7

I have a report where I want to Sum the qty of sales of a certain product but
I also want to display the Sum of total sales (dollar value).

ProductX_Qty Total Sales ($)
120 $55000

In this example the total dollar value of the 120 pcs may not total $55000
due to parts sales that I don't want to count. I have a column in the query
where it is described as either a "Parts" order or a "Unit" order.
 
Try an expression like this in the Control Source of a text box in the
Report Footer section:
=Sum(IIf([ProductID]="X", [Qty], 0))

Omit the quotes if ProductID is a Number field (not a Text field.)
 
What if my productID can be a number of values and I need to capture them all
with an (*)....(ie. Sum if ProductID = *pump)?
--
TIA


Allen Browne said:
Try an expression like this in the Control Source of a text box in the
Report Footer section:
=Sum(IIf([ProductID]="X", [Qty], 0))

Omit the quotes if ProductID is a Number field (not a Text field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

tsison7 said:
I have a report where I want to Sum the qty of sales of a certain product
but
I also want to display the Sum of total sales (dollar value).

ProductX_Qty Total Sales ($)
120 $55000

In this example the total dollar value of the 120 pcs may not total $55000
due to parts sales that I don't want to count. I have a column in the
query
where it is described as either a "Parts" order or a "Unit" order.
Click to expand...
Click to expand...
 
If you are certain that all the products you want end with "pump", and none
of the other products do, you could try:
=Sum(IIf([ProductID] Like "*pump", [Qty], 0))

Realistically, though, you would be better off with a CategoryID field in
your Products table so you can correctly identify the products you want to
refer to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

tsison7 said:
What if my productID can be a number of values and I need to capture them
all
with an (*)....(ie. Sum if ProductID = *pump)?
--
TIA


Allen Browne said:
Try an expression like this in the Control Source of a text box in the
Report Footer section:
=Sum(IIf([ProductID]="X", [Qty], 0))

Omit the quotes if ProductID is a Number field (not a Text field.)

tsison7 said:
I have a report where I want to Sum the qty of sales of a certain
product
but
I also want to display the Sum of total sales (dollar value).

ProductX_Qty Total Sales ($)
120 $55000

In this example the total dollar value of the 120 pcs may not total
$55000
due to parts sales that I don't want to count. I have a column in the
query
where it is described as either a "Parts" order or a "Unit" order.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Allen,

I agree it would be ideal to set it up with a categoryID unfortunately I
import a table which is generated from our MRP where there are thousands of
orders.

--
TIA


Allen Browne said:
If you are certain that all the products you want end with "pump", and none
of the other products do, you could try:
=Sum(IIf([ProductID] Like "*pump", [Qty], 0))

Realistically, though, you would be better off with a CategoryID field in
your Products table so you can correctly identify the products you want to
refer to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

tsison7 said:
What if my productID can be a number of values and I need to capture them
all
with an (*)....(ie. Sum if ProductID = *pump)?
--
TIA


Allen Browne said:
Try an expression like this in the Control Source of a text box in the
Report Footer section:
=Sum(IIf([ProductID]="X", [Qty], 0))

Omit the quotes if ProductID is a Number field (not a Text field.)

I have a report where I want to Sum the qty of sales of a certain
product
but
I also want to display the Sum of total sales (dollar value).

ProductX_Qty Total Sales ($)
120 $55000

In this example the total dollar value of the 120 pcs may not total
$55000
due to parts sales that I don't want to count. I have a column in the
query
where it is described as either a "Parts" order or a "Unit" order.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top