Expressions in Reports

[Reese]

I'm trying to write an expression that counts "Warranties" in one field only
if the value of another field is "New" and then divides them by the total
number of "New" returns in the overall group.

 

[Marshall Barton]

I'm trying to write an expression that counts "Warranties" in one field only
if the value of another field is "New" and then divides them by the total
number of "New" returns in the overall group.

Try using something like:

=Sum(IIf([one field] = "Warranties" And [another field] =
"New", 1, 0)) / Sum(IIf([another field] = "New", 1, 0))

 

[Reese]

The only thing I'm hung up on with that formula is that the [Warranty Income]
field is a currency field. The only way that we know a warranty was sold was
that there is an income figure there. I've been using ([Warranty Income]

=1) to identify a warranty sale. I'm having trouble writing that formula.

It keeps saying "too complicated".

I'm trying to write an expression that counts "Warranties" in one field only
if the value of another field is "New" and then divides them by the total
number of "New" returns in the overall group.

Try using something like:

=Sum(IIf([one field] = "Warranties" And [another field] =
"New", 1, 0)) / Sum(IIf([another field] = "New", 1, 0))

 

[Marshall Barton]

If you would use the real field names and conditions in your
question, we could avoid all this back and forth while I
guess what you are trying to do.

Try changing it to something more like

=Sum(IIf([Warranty Income] => 0 And [another field] =
"New", 1, 0)) / Sum(IIf([another field] = "New", 1, 0))
--
Marsh
MVP [MS Access]

The only thing I'm hung up on with that formula is that the [Warranty Income]
field is a currency field. The only way that we know a warranty was sold was
that there is an income figure there. I've been using ([Warranty Income]

=1) to identify a warranty sale. I'm having trouble writing that formula.

It keeps saying "too complicated".

 

[Reese]

Sorry. The 3 fields in the expression are: "Warranty Income" which contains
currency value; "New / Used" which is either new or used; And then to divide
by the number of "new" returns in the "New / Used" field. Here is the
formula that I wrote based on your suggestion:

=Sum(IIf([Warranty Income] => 0 And [New / Used] = "New", 1, 0)) /
sum(IIf([New / Used] = "New", 1, 0))

I get the error message: "The expression you entered contains invalid
syntax" "You may have entered a comma without a preceding value or
identifier."

If you would use the real field names and conditions in your
question, we could avoid all this back and forth while I
guess what you are trying to do.

Try changing it to something more like

=Sum(IIf([Warranty Income] => 0 And [another field] =
"New", 1, 0)) / Sum(IIf([another field] = "New", 1, 0))
--
Marsh
MVP [MS Access]

The only thing I'm hung up on with that formula is that the [Warranty Income]
field is a currency field. The only way that we know a warranty was sold was
that there is an income figure there. I've been using ([Warranty Income]

=1) to identify a warranty sale. I'm having trouble writing that formula.

It keeps saying "too complicated".

Reese wrote:
I'm trying to write an expression that counts "Warranties" in one field only
if the value of another field is "New" and then divides them by the total
number of "New" returns in the overall group.

 

[Marshall Barton]

Sorry. The 3 fields in the expression are: "Warranty Income" which contains
currency value; "New / Used" which is either new or used; And then to divide
by the number of "new" returns in the "New / Used" field. Here is the
formula that I wrote based on your suggestion:

=Sum(IIf([Warranty Income] => 0 And [New / Used] = "New", 1, 0)) /
sum(IIf([New / Used] = "New", 1, 0))

Sorry, I had an extra = sign in there. It was supposed to
be:

=Sum(IIf([Warranty Income] > 0 And [New / Used] = "New", 1,
0)) / Sum(IIf([New / Used] = "New", 1, 0))

 

[Reese]

That worked. Thank you. Just a quick question if you don't mind. What do
the "1,0" represent at the end of those expressions? Thanks again....

Sorry. The 3 fields in the expression are: "Warranty Income" which contains
currency value; "New / Used" which is either new or used; And then to divide
by the number of "new" returns in the "New / Used" field. Here is the
formula that I wrote based on your suggestion:

=Sum(IIf([Warranty Income] => 0 And [New / Used] = "New", 1, 0)) /
sum(IIf([New / Used] = "New", 1, 0))

Sorry, I had an extra = sign in there. It was supposed to
be:

=Sum(IIf([Warranty Income] > 0 And [New / Used] = "New", 1,
0)) / Sum(IIf([New / Used] = "New", 1, 0))

 

[Marshall Barton]

If the IIf condition is true then Sum adds a 1, it the
condition is false then it adds 0 so the total is the count
of records meeting the condition.

There are many ways of doing this kind of thing. This one
is more efficient but also more obscure:

=-Sum([Warranty Income] => 0 And [New / Used] = "New") /
-Sum([New / Used] = "New")

or a little less efficient and possibly less obscure:

=Abs(Sum([Warranty Income] => 0 And [New / Used] = "New"))
/ Abs(Sum([New / Used] = "New"))

or this one is about the same efficiency as the
Sun(IIf(...)) I suggested you use:

=Count(IIf([Warranty Income] => 0 And [New / Used] =
"New", 1,Null)) / Count(IIf([New / Used] = "New", 1, Null))
--
Marsh
MVP [MS Access]

That worked. Thank you. Just a quick question if you don't mind. What do
the "1,0" represent at the end of those expressions? Thanks again....

Sorry. The 3 fields in the expression are: "Warranty Income" which contains
currency value; "New / Used" which is either new or used; And then to divide
by the number of "new" returns in the "New / Used" field. Here is the
formula that I wrote based on your suggestion:

=Sum(IIf([Warranty Income] => 0 And [New / Used] = "New", 1, 0)) /
sum(IIf([New / Used] = "New", 1, 0))

Sorry, I had an extra = sign in there. It was supposed to
be:

=Sum(IIf([Warranty Income] > 0 And [New / Used] = "New", 1,
0)) / Sum(IIf([New / Used] = "New", 1, 0))