Counting records several groups down

  • Thread starter Thread starter Duane Hookom
  • Start date Start date
rgrantz said:
I have a report whose query source uses 2 form field dates as the date
range. The grouping level is as follows:

Group 1: Date (Date order was produced, grouped from range in StartDate and
EndDate fileds in form)
Group 2: Customer
Group 3: OrderNumber
Detail: ItemNumber Rush

ItemNumber and Rush are in the Detail section. Rush is a checkbox value (as
is the underlying recordsource in the table).

Date, Customer, OrderNumber, and Rush are from the Production table.
ItemNumber is from the Detail table (and Order Number is also in Detail
table, as the relationship between Detail and Production. OrderNumber in
Production has a one to many relationship with ItemNumber in Detail). Yes,
it is redundant to have the Rush field with every item in the report, as the
Rush is actually set at the Order level, so every item in a Rush order will
be rushed, but trust me, it does need to be shown this way on the report.

What I'm trying to do is total the count of ItemNumber and Rush (when set to
yes) in the Date Group footer, but I'm having a hard time getting anything
to work. I can't use the unbound control of "=1" in the Detail section,
Sum over Group or Over All, and put a control bound to it in the Date
footer. How can I total the count of both ItemNumber and "Rush = Yes" in
the Per Date (Date Group footer)?

Example:

1/3/05
Customer: Tom's
Order #: 4
Item#: 12 Rush: Yes (checked)
Item#: 13 Rush: Yes (checked)
Order #: 5
Item#: 78 Rush: No (Unchecked)
Item#: 45 Rush: No
Customer: Pete's
Order #: 6
Item#: 23 Rush: Yes
Customer: Sue's
Order #: 7
Item#: 34 Rush: No
(---Date Group Footer-----)
Total Items: 6 Rushes: 3
Click to expand...


You can get the total number if items with the expression
=Count(*)

You can get the number of Rush items using either of these:
=Count(IIf(Rush, 1, Null))
or
=Abs(Sum(Rush))
depending on which is easiest for you to understand.
 
I have a report whose query source uses 2 form field dates as the date
range. The grouping level is as follows:

Group 1: Date (Date order was produced, grouped from range in StartDate and
EndDate fileds in form)
Group 2: Customer
Group 3: OrderNumber
Detail: ItemNumber Rush

ItemNumber and Rush are in the Detail section. Rush is a checkbox value (as
is the underlying recordsource in the table).

Date, Customer, OrderNumber, and Rush are from the Production table.
ItemNumber is from the Detail table (and Order Number is also in Detail
table, as the relationship between Detail and Production. OrderNumber in
Production has a one to many relationship with ItemNumber in Detail). Yes,
it is redundant to have the Rush field with every item in the report, as the
Rush is actually set at the Order level, so every item in a Rush order will
be rushed, but trust me, it does need to be shown this way on the report.

What I'm trying to do is total the count of ItemNumber and Rush (when set to
yes) in the Date Group footer, but I'm having a hard time getting anything
to work. I can't use the unbound control of "=1" in the Detail section,
Sum over Group or Over All, and put a control bound to it in the Date
footer. How can I total the count of both ItemNumber and "Rush = Yes" in
the Per Date (Date Group footer)?

Example:

1/3/05
Customer: Tom's
Order #: 4
Item#: 12 Rush: Yes (checked)
Item#: 13 Rush: Yes (checked)
Order #: 5
Item#: 78 Rush: No (Unchecked)
Item#: 45 Rush: No
Customer: Pete's
Order #: 6
Item#: 23 Rush: Yes
Customer: Sue's
Order #: 7
Item#: 34 Rush: No
(---Date Group Footer-----)
Total Items: 6 Rushes: 3

1/4/05
Customer: Harry's
Order #: 7
Item#: 35

etc. etc.

Any help would be greatly appreciated, and thanks for reading.
 
Back
Top