Trying to Subtotal a Subform Price like in Northwind???

[EZ KEY]

Well, now that I have the subform running, I've been able to get the subtotal
to show subtotals for the subform, but only for one item and not the entire
colum in relation to the order number. Any suggestions?

 

[John Vinson]

Well, now that I have the subform running, I've been able to get the subtotal
to show subtotals for the subform, but only for one item and not the entire
colum in relation to the order number. Any suggestions?

Put a textbox in the subform's Footer (you will need to use a
Continuous subform, not a datasheet, and may need to use the View menu
to make the footer visible). Set its Control Source to

=Sum([fieldname])

where fieldname is a field in the query upon which the subform is
based (you can't use a control name, only the name of an actual
field).


John W. Vinson[MVP]

 

[EZ KEY]

I think I can do that and understand how you are saying to do it. I thought
this would be easier, not expecting to have to bug you again.

My follow up is, the Northwind example has a subtotal out side the sub form
and from a data sheet of the subform. Is this a more complicated way? This
is how I would really like to do. Any thoughts?

Thanks again!

Well, now that I have the subform running, I've been able to get the subtotal
to show subtotals for the subform, but only for one item and not the entire
colum in relation to the order number. Any suggestions?

Put a textbox in the subform's Footer (you will need to use a
Continuous subform, not a datasheet, and may need to use the View menu
to make the footer visible). Set its Control Source to

=Sum([fieldname])

where fieldname is a field in the query upon which the subform is
based (you can't use a control name, only the name of an actual
field).


John W. Vinson[MVP]

 

[John Vinson]

I think I can do that and understand how you are saying to do it. I thought
this would be easier, not expecting to have to bug you again.

My follow up is, the Northwind example has a subtotal out side the sub form
and from a data sheet of the subform. Is this a more complicated way? This
is how I would really like to do. Any thoughts?

Just one more step. You still need the textbox on the Footer (but if
you're putting it on the mainform, the textbox or the footer can be
set with Visible = No if you wish).

Let's say the Subform Control on the mainform (not the form itself,
but the box that contains it) is named subOrderDetails, and the
textbox with the Sum is named txtTotal.

Put a textbox on the mainform with a Control Source

=sumOrderDetails.Form!txtTotal

to "relay" the sum to the mainform.

This value will not be (and should not be) stored in your Orders
table, just calculate it as needed.

John W. Vinson[MVP]

 

[EZ KEY]

Hi John:

Okay, I understand. I'm getting "$0.00" in the text box in step 1 in the
Subform Footer.

What I have in this text box is: =Sum([Price])

However, I was thinking, my Orders Details Services query is based upon my
Order Details table, and I'm using a combo box to query the Product name and
price; and a text box in the form is displaying the price by referring to the
combo box's column 2 in the table as such: =[cboProduct].[Column][2]

Would this text box have to refer back to the table?

Continuing on...

My Subtotal text box on the main form refers back to the subform as such:
=[Orders Details Services].[Form]![OrderSub] (refering back to the field
name I gave the text box in the subform) This main form subtotal come up
"$0.00" just like the other,... So, (I could be wrong) I think this is refer
back correctly, my issue is with the subtotal in the Subform.

What do you think?

 

[EZ KEY]

John, I was thinking one more thing. When I refer to the Price, I do get an
amount, but I think what we are missing in communication (My Fault) is my
Subform Data sheet is listing multiple ROWs with totals... and I want to sub
all of these. Some how I have to Subtotal all the prices in the "Colunm2"
that are in separate rows.

I hope I'm making sense.

 

[John Vinson]

John, I was thinking one more thing. When I refer to the Price, I do get an
amount, but I think what we are missing in communication (My Fault) is my
Subform Data sheet is listing multiple ROWs with totals... and I want to sub
all of these. Some how I have to Subtotal all the prices in the "Colunm2"
that are in separate rows.

I hope I'm making sense.

The =Sum([fieldname]) WILL sum over multiple Rows - all the rows
selected in the Subform.

However, [fieldname] *must* be an actual field in the Table's Query.
It cannot be the name of a textbox with (or without!) a calculated
expression - it needs to be a field in the "background", actually in
the Query.

If you're summing an Extended Price (quantity times unit cost) you can
use both fields in the expression:

=Sum([Quantity] * [UnitPrice])

If you're looking up the price from a table other than the Form's
Recordsource, you must either include that table in the Query or use a
calculated field in the Query:

ItemPrice: DLookUp("[Price]", "[Items]", "[ItemNo] = " & Me.ItemNo)

John W. Vinson[MVP]

 

[EZ KEY]

John:

Okay, that makes sense. The "actual" filed name I used for the query was
"Price" however, I changed that from "Product Price" in the table. I was
curious about that too, so maybe that is the case.

This will get me through I believe. Thanks for helping me out again. Merry
Christmas!

John, I was thinking one more thing. When I refer to the Price, I do get an
amount, but I think what we are missing in communication (My Fault) is my
Subform Data sheet is listing multiple ROWs with totals... and I want to sub
all of these. Some how I have to Subtotal all the prices in the "Colunm2"
that are in separate rows.

I hope I'm making sense.

The =Sum([fieldname]) WILL sum over multiple Rows - all the rows
selected in the Subform.

However, [fieldname] *must* be an actual field in the Table's Query.
It cannot be the name of a textbox with (or without!) a calculated
expression - it needs to be a field in the "background", actually in
the Query.

If you're summing an Extended Price (quantity times unit cost) you can
use both fields in the expression:

=Sum([Quantity] * [UnitPrice])

If you're looking up the price from a table other than the Form's
Recordsource, you must either include that table in the Query or use a
calculated field in the Query:

ItemPrice: DLookUp("[Price]", "[Items]", "[ItemNo] = " & Me.ItemNo)

John W. Vinson[MVP]

 

[EZ KEY]

I'm sorry, one more thought. Okay, the way the Order Details Services finds
the prices in the form is referring to the Product Name lookup and referring
to Column 2. So will I really need to refer to the field name "Product
Name.Column(2)"?

Just a thought! / ?

Thanks again.

John, I was thinking one more thing. When I refer to the Price, I do get an
amount, but I think what we are missing in communication (My Fault) is my
Subform Data sheet is listing multiple ROWs with totals... and I want to sub
all of these. Some how I have to Subtotal all the prices in the "Colunm2"
that are in separate rows.

I hope I'm making sense.

The =Sum([fieldname]) WILL sum over multiple Rows - all the rows
selected in the Subform.

However, [fieldname] *must* be an actual field in the Table's Query.
It cannot be the name of a textbox with (or without!) a calculated
expression - it needs to be a field in the "background", actually in
the Query.

If you're summing an Extended Price (quantity times unit cost) you can
use both fields in the expression:

=Sum([Quantity] * [UnitPrice])

If you're looking up the price from a table other than the Form's
Recordsource, you must either include that table in the Query or use a
calculated field in the Query:

ItemPrice: DLookUp("[Price]", "[Items]", "[ItemNo] = " & Me.ItemNo)

John W. Vinson[MVP]

 

[EZ KEY]

John:

I'm sure I'm reaching the point of aggravation, but I have referred to every
possible fieldname possible on this Subform for subtotal, and nothing comes
up but $0.00 or Error.

I think I figured out the issue.

When I enter a mock-order, in the "Order Details Services" subform Dataview,
the the Order ID; Product Name; and Price show up. The prices show up
because I created a combo box to find the price in column2 of the Product
Name in creation of the form. However, the prices don't show in the Orders
table despite the rest do. In the Order Details table, the Order ID and
Product Name shows up, but nothing shows up under the Price column except
$0.00. I believe my sum is working correctly in refering to price, but there
aren't prices to sum, so it comes up $0.00. Any ideas on correcting this?

Thanks again!

I'm sorry, one more thought. Okay, the way the Order Details Services finds
the prices in the form is referring to the Product Name lookup and referring
to Column 2. So will I really need to refer to the field name "Product
Name.Column(2)"?

Just a thought! / ?

Thanks again.

John, I was thinking one more thing. When I refer to the Price, I do get an
amount, but I think what we are missing in communication (My Fault) is my
Subform Data sheet is listing multiple ROWs with totals... and I want to sub
all of these. Some how I have to Subtotal all the prices in the "Colunm2"
that are in separate rows.

I hope I'm making sense.

The =Sum([fieldname]) WILL sum over multiple Rows - all the rows
selected in the Subform.

However, [fieldname] *must* be an actual field in the Table's Query.
It cannot be the name of a textbox with (or without!) a calculated
expression - it needs to be a field in the "background", actually in
the Query.

If you're summing an Extended Price (quantity times unit cost) you can
use both fields in the expression:

=Sum([Quantity] * [UnitPrice])

If you're looking up the price from a table other than the Form's
Recordsource, you must either include that table in the Query or use a
calculated field in the Query:

ItemPrice: DLookUp("[Price]", "[Items]", "[ItemNo] = " & Me.ItemNo)

John W. Vinson[MVP]

 

[John Vinson]

John:

I'm sure I'm reaching the point of aggravation, but I have referred to every
possible fieldname possible on this Subform for subtotal, and nothing comes
up but $0.00 or Error.

I think I figured out the issue.

When I enter a mock-order, in the "Order Details Services" subform Dataview,
the the Order ID; Product Name; and Price show up. The prices show up
because I created a combo box to find the price in column2 of the Product
Name in creation of the form. However, the prices don't show in the Orders
table despite the rest do.

Of course the prices don't - and shouldn't - show up in the Orders
table. A price refers TO AN ITEM, not to an order as a whole. If you
want to calculate a total price for an order, calculate it on the fly,
as needed - DON'T store it in the table.

In the Order Details table, the Order ID and
Product Name shows up, but nothing shows up under the Price column except
$0.00. I believe my sum is working correctly in refering to price, but there
aren't prices to sum, so it comes up $0.00. Any ideas on correcting this?

Since a) prices can change over time (they usually go up, except in
the computer area <g>) and b) you want to record the price *as of the
time that an order was purchased*, you really should have a Price
field in BOTH the Item table *and* in the OrderDetails table.

You can copy the (current) price from the Items table into the
OrderDetails table with a little bit of VBA code in the AfterUpdate
event of the item combo box. Open the product name combo box - I'll
call it cboProducts, since I don't like blanks in object names and
don't like Access' convention of using the fieldname as the control
name - in design view. On the combo's Properties Events tab click the
.... icon by the AfterUpdate property, and choose Code Builder; edit
the code to something like

Private Sub cboProducts_AfterUpdate()
Me.txtPrice = Me.cboProducts.Column(2)
End Sub

to "push" the price from the third column of the combo box into the
bound Price control (named txtPrice).

John W. Vinson[MVP]

 

[EZ KEY]

John:

Good news! I got it running. I actually ended up creating a new Order
details subform, and it worked. Not sure why. I must have had a mess of
commands in the form.

Thanks for your patience and help. Merry Christmas!