Can you please help me with a problem I'm having with my totals?
I have two tables I'm using in a query for my report:
The first table (Table1) has work order information and associated cost per
work order
The second table (Table2) has deficiencies found during inspections
Since a work order may involve the correction of various deficiencies there
is a field in Table2 for a work order number.
I use this field to join Table2 to Table1 which gives me a listing of all
deficiencies that have an assigned work order and the associated cost.
Which is the total work order cost not the cost per deficiency.
The problem is when there are multiple deficiencies using the same work
order.
Since, I'm using only one query for this report each line (deficiency) has
the total work order cost. The same work order cost is repeated for each
deficiency under that work order.
I designed the report to Group on the work order number with the work order
cost displayed in the group; with the associated deficiencies listed in the
detail, everything looks fine until I try to SUM the work order cost at the
report level.
The report total is not just Summing the group level cost of the work order,
it is also including the work order cost from each of the deficiencies.
Can anyone explain how I can get around this problem??
Any help is greatly appreciated!!!!!!!!!
Duane Hookom said:
Add a text box in the footer of something other than a page. To all non-null
values, just set the control source to:
=Count([womaint_wo])
to count the number of records where this field is 'SomeValue':
=Sum(Abs([womaint_wo] = "SomeValue"))
--
Duane Hookom
MS Access MVP
Berny said:
I have a similar problem, were do I place this code??
When I put in the 'Control Source' field for the text box I get the
following error:
"The expression 'Sum(Abs([womaint_no]='L036348')). Aggregate functions are
only allowed on output fields of the Record Source"
What I'm trying to do is count those recourds in a report group that
have
a
specfic field (womaint_wo) that are not Null (blank)
Can anyone tell me what I'm doing wrong????
Duane Hookom said:
This is a bit confusing since "Count unique records" is not nearly the same
as "count the number of grouped records that contain 'Won'". If you
want
to
count the number of records in a group that have the [Status] field = "Won"
then use:
=Sum(Abs([Status]="Won"))
--
Duane Hookom
MS Access MVP
I have a report which is grouped by salerep. I need to
count the number of unique records in each group. For
example, there's a field on the report named [Status]. I
need to count the number of grouped records that
contain "Won" in said field on the report.
Anyone know how to help me with this?
I also need to grand total the counts at the end of the
report but if I get past the group counts I can probably
figure it out.
Any help much appreciated.
--Steve
