Sum total if less than certain amount.

[Dmm]

I would like a total for all Unit Costs less than $95. Any items that are
less than that amount will be added up. Right now I am working with:
=DSum("[Extd Cost]","[Department Name]","[Unit Cost]= '<95'")

 

[Allen Browne]

If [Unit Cost] and [Extd Cost] are fields in the report's source query, you
could try this in the Control Source of a text box in the group footer or
report footer:

=Sum(IIf([Unit Cost] < $95, [Extd Cost], 0))

 

[mrmassaro]

If [Unit Cost] and [Extd Cost] are fields in the report's source query, you
could try this in the Control Source of a text box in the group footer or
report footer:

=Sum(IIf([Unit Cost] < $95, [Extd Cost], 0))

No such luck. The syntax is off. In your suggestion there is no
mention of the Dept Name, is that needed to complete the expression?

I have since created a seperate query with <$95 Unit Costs, and now
working on getting that to total.

Any suggestions?

 

[Allen Browne]

Your example of:
=DSum("[Extd Cost]","[Department Name]","[Unit Cost]= '<95'")
means:
Sum the [Extd Cost] field,
from the [Department Name] table,
where the [Unit Cost] field is less than the text "95".

Unless your report has a field named [Extd Cost] from two or more tables,
there is no need to refer to the table name.

If [Department Name] is actually a field name, perhaps you would like to
explain what you are seeking to achieve.

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

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

If [Unit Cost] and [Extd Cost] are fields in the report's source query,
you
could try this in the Control Source of a text box in the group footer or
report footer:

=Sum(IIf([Unit Cost] < $95, [Extd Cost], 0))

No such luck. The syntax is off. In your suggestion there is no
mention of the Dept Name, is that needed to complete the expression?

I have since created a seperate query with <$95 Unit Costs, and now
working on getting that to total.

Any suggestions?

 

[mrmassaro]

Your example of:
=DSum("[Extd Cost]","[Department Name]","[Unit Cost]= '<95'")
means:
Sum the [Extd Cost] field,
from the [Department Name] table,
where the [Unit Cost] field is less than the text "95".

Unless your report has a field named [Extd Cost] from two or more tables,
there is no need to refer to the table name.

If [Department Name] is actually a field name, perhaps you would like to
explain what you are seeking to achieve.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

If [Unit Cost] and [Extd Cost] are fields in the report's source query,
you
could try this in the Control Source of a text box in the group footer or
report footer:
=Sum(IIf([Unit Cost] < $95, [Extd Cost], 0))
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
news[email protected]...

No such luck. The syntax is off. In your suggestion there is no
mention of the Dept Name, is that needed to complete the expression?

I have since created a seperate query with <$95 Unit Costs, and now
working on getting that to total.

Any suggestions?- Hide quoted text -

- Show quoted text -

Rough example:

Model Qty Unit Cost Extd Cost
abc 1 $86.00 $86.00
def 5 $524.25 $2,621.25
ghi 5 $25.25 $126.25

I am after the Unit Costs <$95. My goal is to add the Extd Costs for
those meeting the requirement. In the example above the total would
be $212.25.

 

[skydiver]

Create a new query. Using your table double-click the following fields.
Model, Qty, and Unit Cost. In the 4th column of the query enter Extd Cost:
[Qty]*[Unit Cost] Enter <96 in the criteria row of the Unit Cost column.
Run the query to view the results.

Your example of:
=DSum("[Extd Cost]","[Department Name]","[Unit Cost]= '<95'")

[quoted text clipped - 43 lines]

- Show quoted text -

Rough example:

Model Qty Unit Cost Extd Cost
abc 1 $86.00 $86.00
def 5 $524.25 $2,621.25
ghi 5 $25.25 $126.25

I am after the Unit Costs <$95. My goal is to add the Extd Costs for
those meeting the requirement. In the example above the total would
be $212.25.

 

[mrmassaro]

Create a new query. Using your table double-click the following fields.
Model, Qty, and Unit Cost. In the 4th column of the query enter Extd Cost:
[Qty]*[Unit Cost] Enter <96 in the criteria row of the Unit Cost column.
Run the query to view the results.

Your example of:
=DSum("[Extd Cost]","[Department Name]","[Unit Cost]= '<95'")

[quoted text clipped - 43 lines]

- Show quoted text -

Rough example:

Model Qty Unit Cost Extd Cost
abc 1 $86.00 $86.00
def 5 $524.25 $2,621.25
ghi 5 $25.25 $126.25

I am after the Unit Costs <$95. My goal is to add the Extd Costs for
those meeting the requirement. In the example above the total would
be $212.25.- Hide quoted text -

- Show quoted text -

Skydiver, that has all ready been done, I am now attempting to get the
<$95 Extd Cost Sum Total into a report. Not as easy as it sounds,
since the report is run from a main query.