Report that calculates based on another field's criteria

[Michele E]

I have created a report that shows our plant's production for each machine
and each product made. We often run "Trial" products that are new
developments on the machines. For each grade/machine I have a [Standard]
production that the machine operator is expected to produce during his shift.
Trials are an exception, because we don't know how much we should be
producing since they are new products.
I want my report to filter out all "Trial" products when calculating the
[Total Production] and [% of Standard] achieved during a shift.

How can I do this? I have several trial products listed. Is there a way to
tell the query to not use [Product] that have the word "Trial" in them when
making calculations? Can the report exclude these alltogether?

My [Product] list looks like this (but I have a lot more of them). There is
a [Standard] associated with each product in a ProductList table.
Trial - Press
Trial - Rewinder
Paper - Press
Paper - Rewinder
Film - Press
Film - Rewinder

I only want the paper and film data to be used in the [% Standard] and
[Total Production] calculations in my report.
Thanks.
Michele E

 

[John W. Vinson]

I have created a report that shows our plant's production for each machine
and each product made. We often run "Trial" products that are new
developments on the machines. For each grade/machine I have a [Standard]
production that the machine operator is expected to produce during his shift.
Trials are an exception, because we don't know how much we should be
producing since they are new products.
I want my report to filter out all "Trial" products when calculating the
[Total Production] and [% of Standard] achieved during a shift.

How can I do this? I have several trial products listed. Is there a way to
tell the query to not use [Product] that have the word "Trial" in them when
making calculations? Can the report exclude these alltogether?

My [Product] list looks like this (but I have a lot more of them). There is
a [Standard] associated with each product in a ProductList table.
Trial - Press
Trial - Rewinder
Paper - Press
Paper - Rewinder
Film - Press
Film - Rewinder

I only want the paper and film data to be used in the [% Standard] and
[Total Production] calculations in my report.
Thanks.
Michele E

Use a criterion of

NOT LIKE "Trial*"

as a criterion on the field to exclude them.

 

[Michele E]

Thank you. I ended up changing all trial products' standards to zero and used
that in my expression. It is working well.
--
Michele E

Michele:

If you want the report to ignore trial products completely, in the query's
WHERE clause put:

NOT [Product] LIKE "*Trial*"

but remember that this will also exclude any values where 'trial' is a
substring of another word, e.g. 'industrial', 'terrestrial' etc. If the word
'Trial' always comes at the start of the value and is always followed by a
space then:

LEFT([Product], 6) <> "Trial "

would be better.

One thing to be aware of is that if the report's RecordSource should happen
to be a query using an Outer join you cannot restrict the results on a column
in a table on the right side of a LEFT JOIN , or conversely on the left side
of a RIGHT JOIN.

If on the other hand you want the report to include trial products in the
detail, but not in the aggregated values then you'd need to amend the
expressions used to compute the aggregated values so that they don't take
account of the trial products. As a simple example to count all the detail
rows bar those where the product is a trial an expression like one of the
following, both of which give the same result, could be used:

=Count(IIf(Left([Product], 6) <> "Trial ",1,Null))

or:

=Sum(IIf(Left([Product], 6) <> "Trial ",1,0))

To conditionally sum a value you can again call the IIf function, but in this
case multiply the value being summed by its return value:

=Sum([Quantity]*IIf(Left([Product], 6) <> "Trial ",1,0))

Ken Sheridan
Stafford, England

I have created a report that shows our plant's production for each machine
and each product made. We often run "Trial" products that are new
developments on the machines. For each grade/machine I have a [Standard]
production that the machine operator is expected to produce during his shift.
Trials are an exception, because we don't know how much we should be
producing since they are new products.
I want my report to filter out all "Trial" products when calculating the
[Total Production] and [% of Standard] achieved during a shift.

How can I do this? I have several trial products listed. Is there a way to
tell the query to not use [Product] that have the word "Trial" in them when
making calculations? Can the report exclude these alltogether?

My [Product] list looks like this (but I have a lot more of them). There is
a [Standard] associated with each product in a ProductList table.
Trial - Press
Trial - Rewinder
Paper - Press
Paper - Rewinder
Film - Press
Film - Rewinder

I only want the paper and film data to be used in the [% Standard] and
[Total Production] calculations in my report.
Thanks.
Michele E

--
Message posted via AccessMonster.com


.