Running Sums shown for multiple group levels

[Guest]

Howdy All!

I have an sales report that shows all my clients, each order that the
client made (within a given time period) and then order details (item
description and item cost). It has three grouping levels, one for each
order, one for each client, and one for all clients. Without totals, it
might look like this:

Client 1
Order 1
Item 1 $1.00
Item 2 $2.00
Order 2
Item 3 $3.00
Item 4 $4.00

So, I know how to get a total for individual Orders. You just put an
invisible unbound control in the detail line, set the data source equal to
the item cost, and set the running sum to Over Group. Also, I know how to
get a total for all of my clients, as you add another invisible control and
set the running sum property to Over All.

But, how do you handle intermediate levels of grouping? For example, is
there a way for me to get a total for all of a customer's orders?

Marty

 

[Kevin Jameson]

you cant do this with microsoft access 2003

you must upgrade to access 20004 by clicking here:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Kevin

 

[Duane Hookom]

Please ignore the spam "you cant do this with microsoft access 2003".

Why do you think you need to use running sums? To total any numeric field in
a group or report header or footer section, use a text box with a Control
Source like:

=Sum([YourNumericFieldOrExpression])

 

[Guest]

Howdy Duane,

Thanks for both the warning about the Access 2003 spam and also for your
response to my question. I'm certain that you've got the answer, but I
failed to implement your proposed solution. Here is what is happening.

My lowest level group is for each Order. Each detail line includes an
invisible textbox called txtDetailRSGroup that is a simple running sum for
the group of Order Details.

In the group footer for Orders I have another textbox call
txtSingleOrderTotal and the recordsource for this is just
=[txtDetailRSGroup]. This works well, and shows the correctly summed order
details.

In the group footer for the Client (who may have several Orders) I created a
textbox called txtAllOrdersTotal. As suggested, the record source for this
is:

=Sum([txtSingleOrderTotal])

However, when I run the report I what looks like a prompt titled:
"Enter Parameter Value"
and in the body of the dialog box is just the literal:
"txtSingleOrderTotal"

It looks as though Access thinks I'm writing a query rather than running a
simple sum statement.

Any thoughts would be very much appreciated.

Marty

Please ignore the spam "you cant do this with microsoft access 2003".

Why do you think you need to use running sums? To total any numeric field in
a group or report header or footer section, use a text box with a Control
Source like:

=Sum([YourNumericFieldOrExpression])

--
Duane Hookom
MS Access MVP

Howdy All!

I have an sales report that shows all my clients, each order that the
client made (within a given time period) and then order details (item
description and item cost). It has three grouping levels, one for each
order, one for each client, and one for all clients. Without totals, it
might look like this:

Client 1
Order 1
Item 1 $1.00
Item 2 $2.00
Order 2
Item 3 $3.00
Item 4 $4.00

So, I know how to get a total for individual Orders. You just put an
invisible unbound control in the detail line, set the data source equal to
the item cost, and set the running sum to Over Group. Also, I know how to
get a total for all of my clients, as you add another invisible control
and
set the running sum property to Over All.

But, how do you handle intermediate levels of grouping? For example, is
there a way for me to get a total for all of a customer's orders?

Marty

 

[Guest]

One other thought,

The "Sum" function is listed in the Builder as being an SQL function.
That makes me wonder if it won't default to looking for the item being summed
in a recordset rather than in an unbound control.

Marty

Please ignore the spam "you cant do this with microsoft access 2003".

Why do you think you need to use running sums? To total any numeric field in
a group or report header or footer section, use a text box with a Control
Source like:

=Sum([YourNumericFieldOrExpression])

--
Duane Hookom
MS Access MVP

Howdy All!

I have an sales report that shows all my clients, each order that the
client made (within a given time period) and then order details (item
description and item cost). It has three grouping levels, one for each
order, one for each client, and one for all clients. Without totals, it
might look like this:

Client 1
Order 1
Item 1 $1.00
Item 2 $2.00
Order 2
Item 3 $3.00
Item 4 $4.00

So, I know how to get a total for individual Orders. You just put an
invisible unbound control in the detail line, set the data source equal to
the item cost, and set the running sum to Over Group. Also, I know how to
get a total for all of my clients, as you add another invisible control
and
set the running sum property to Over All.

But, how do you handle intermediate levels of grouping? For example, is
there a way for me to get a total for all of a customer's orders?

Marty

 

[Duane Hookom]

I still don't think you need any running sums. What is the Control Source of
txtDetailRSGroup? If it is something like:
=Quantity * Price
you can add a text box to any Group Footer section with a Control Source of:
=Sum(Quantity * Price)

--
Duane Hookom
MS Access MVP

Howdy Duane,

Thanks for both the warning about the Access 2003 spam and also for your
response to my question. I'm certain that you've got the answer, but I
failed to implement your proposed solution. Here is what is happening.

My lowest level group is for each Order. Each detail line includes an
invisible textbox called txtDetailRSGroup that is a simple running sum for
the group of Order Details.

In the group footer for Orders I have another textbox call
txtSingleOrderTotal and the recordsource for this is just
=[txtDetailRSGroup]. This works well, and shows the correctly summed
order
details.

In the group footer for the Client (who may have several Orders) I created
a
textbox called txtAllOrdersTotal. As suggested, the record source for
this
is:

=Sum([txtSingleOrderTotal])

However, when I run the report I what looks like a prompt titled:
"Enter Parameter Value"
and in the body of the dialog box is just the literal:
"txtSingleOrderTotal"

It looks as though Access thinks I'm writing a query rather than running a
simple sum statement.

Any thoughts would be very much appreciated.

Marty

Please ignore the spam "you cant do this with microsoft access 2003".

Why do you think you need to use running sums? To total any numeric field
in
a group or report header or footer section, use a text box with a Control
Source like:

=Sum([YourNumericFieldOrExpression])

--
Duane Hookom
MS Access MVP

Howdy All!

I have an sales report that shows all my clients, each order that the
client made (within a given time period) and then order details (item
description and item cost). It has three grouping levels, one for each
order, one for each client, and one for all clients. Without totals,
it
might look like this:

Client 1
Order 1
Item 1 $1.00
Item 2 $2.00
Order 2
Item 3 $3.00
Item 4 $4.00

So, I know how to get a total for individual Orders. You just put an
invisible unbound control in the detail line, set the data source equal
to
the item cost, and set the running sum to Over Group. Also, I know how
to
get a total for all of my clients, as you add another invisible control
and
set the running sum property to Over All.

But, how do you handle intermediate levels of grouping? For example,
is
there a way for me to get a total for all of a customer's orders?

Marty

 

[Duane Hookom]

I should have added that you can't sum a control.

--
Duane Hookom
MS Access MVP

Howdy Duane,

Thanks for both the warning about the Access 2003 spam and also for your
response to my question. I'm certain that you've got the answer, but I
failed to implement your proposed solution. Here is what is happening.

My lowest level group is for each Order. Each detail line includes an
invisible textbox called txtDetailRSGroup that is a simple running sum for
the group of Order Details.

In the group footer for Orders I have another textbox call
txtSingleOrderTotal and the recordsource for this is just
=[txtDetailRSGroup]. This works well, and shows the correctly summed
order
details.

In the group footer for the Client (who may have several Orders) I created
a
textbox called txtAllOrdersTotal. As suggested, the record source for
this
is:

=Sum([txtSingleOrderTotal])

However, when I run the report I what looks like a prompt titled:
"Enter Parameter Value"
and in the body of the dialog box is just the literal:
"txtSingleOrderTotal"

It looks as though Access thinks I'm writing a query rather than running a
simple sum statement.

Any thoughts would be very much appreciated.

Marty

Please ignore the spam "you cant do this with microsoft access 2003".

Why do you think you need to use running sums? To total any numeric field
in
a group or report header or footer section, use a text box with a Control
Source like:

=Sum([YourNumericFieldOrExpression])

--
Duane Hookom
MS Access MVP

Howdy All!

I have an sales report that shows all my clients, each order that the
client made (within a given time period) and then order details (item
description and item cost). It has three grouping levels, one for each
order, one for each client, and one for all clients. Without totals,
it
might look like this:

Client 1
Order 1
Item 1 $1.00
Item 2 $2.00
Order 2
Item 3 $3.00
Item 4 $4.00

So, I know how to get a total for individual Orders. You just put an
invisible unbound control in the detail line, set the data source equal
to
the item cost, and set the running sum to Over Group. Also, I know how
to
get a total for all of my clients, as you add another invisible control
and
set the running sum property to Over All.

But, how do you handle intermediate levels of grouping? For example,
is
there a way for me to get a total for all of a customer's orders?

Marty

 

[Guest]

I oversimplified the statement of the problem. What I was calling a "detail
line" (listing the name of an item and its cost) is actually several lines in
the underlying query. The items being sold are fairly complicated pieces of
gear and I need to describe all the options that were sold with them, so the
underlying recordset might look like:

clientID orderID item cost option description
-------- -------- -------- ------ ----------------------
Client 1, Order 1, Item 1, $1.00, Option Description 1
Client 1, Order 1, Item 1, $1.00, Option Description 2
Client 1, Order 1, Item 1, $1.00, Option Description 3
Client 1, Order 1, Item 2, $2.00, Option Description 2
Client 1, Order 1, Item 2, $2.00, Option Description 5

That is, each order really has only one cost associated with it, but that
cost is represented in the recordset once for every option description
related to the item. The report should show this cost just once for each
item (of course). So, the actual report shows the Item Name and the Item
Cost in an Item header, and only the option description is being show in a
item detail area.

Getting a total for any one order is easy. I have an invisible textbox in
the Item header that uses Item Cost as a record source and is set up as a
running sum for the group. Then, in the footer for the Order there is a
visible txtbox that just reports the result of the running sum for the Orders
group. (Note a problem: the Sum function isn't useful because the cost field
is duplicated for each description).

But, if getting the total for one order is easy, I'm stumped at how to get a
total for the all Orders listed for a client. The Sum function won't work
because the Cost field is duplicated. The running sum feature doesn't work
if the record source is a control rather than a recordset field. The option
of setting up a running sum that sums "Over All" won't work because I will
have multiple clients on the report. Hmm. Sounds like I need to do some VBA.

Marty

 

[Duane Hookom]

I would remove the Options from the main report record source and place them
in a subreport. This allows you to do simple sum()s in the group footers.

 

[Guest]

Done!

Thanks for all those suggestions, Duane. It saved me huge amount of
time messing around with VB.

Marty

 

[Duane Hookom]

I like coding but Access offers so many easier solutions.