Summing Fields

[Bob Love]

This hopefully is a very simple answer but I just can't seem to get it. I
want to track my orders with my venders. I'm trying to use a Main Form/ Sub
form format. The main form has my vender and ordeer fields from that table
and my sub form shows the order details (I.E. Quantity, description, unit $.)
I use a query to find all details that match the order # in the main form.
I have a field on the query the uses the expression of Qnty * Unit $ to
calculate the extended $ but I can't seem to get the sum of the extended $
to the subtotal block on the main form. I know this should be simple but I
can't seem to figure it out. Thanks in advance for any and all help.

 

[Klatuu]

Check out the Orders form and the Orders Subform in the Northwind sample
database. It has a very good example of how that is done.

 

[Bob Love]

I viewed the report of the north winds and the use the expression
=Nz(Sum(Extended Price),0)....I tried this substituting my field "Ext $" for
Extended price and it gave me an error. What about using a calculated
expression "=Sum([Forms]![Order Details Subform]![Ext $]) ..... should this
work

 

[Klatuu]

What error did you get?
Is your calculated control in the subform's footer like it is in the Orders
subform?

BTW using a $ in a name is a very bad idea.
--
Dave Hargis, Microsoft Access MVP

I viewed the report of the north winds and the use the expression
=Nz(Sum(Extended Price),0)....I tried this substituting my field "Ext $" for
Extended price and it gave me an error. What about using a calculated
expression "=Sum([Forms]![Order Details Subform]![Ext $]) ..... should this
work

--
Bob Love

Check out the Orders form and the Orders Subform in the Northwind sample
database. It has a very good example of how that is done.

 

[Bob Love]

When I view the form it say "#Error". I took your advice and went back and
changed the field name with the $ for the orders table and order details
table and renamed the field unit price instead of unit $ and Ext price
instead of Ext $. The control is in the detail of the main form not the
header or footer.
--
Bob Love

What error did you get?
Is your calculated control in the subform's footer like it is in the Orders
subform?

BTW using a $ in a name is a very bad idea.
--
Dave Hargis, Microsoft Access MVP

I viewed the report of the north winds and the use the expression
=Nz(Sum(Extended Price),0)....I tried this substituting my field "Ext $" for
Extended price and it gave me an error. What about using a calculated
expression "=Sum([Forms]![Order Details Subform]![Ext $]) ..... should this
work

--
Bob Love

Check out the Orders form and the Orders Subform in the Northwind sample
database. It has a very good example of how that is done.
--
Dave Hargis, Microsoft Access MVP


:

This hopefully is a very simple answer but I just can't seem to get it. I
want to track my orders with my venders. I'm trying to use a Main Form/ Sub
form format. The main form has my vender and ordeer fields from that table
and my sub form shows the order details (I.E. Quantity, description, unit $.)
I use a query to find all details that match the order # in the main form.
I have a field on the query the uses the expression of Qnty * Unit $ to
calculate the extended $ but I can't seem to get the sum of the extended $
to the subtotal block on the main form. I know this should be simple but I
can't seem to figure it out. Thanks in advance for any and all help.

 

[Bob Love]

I think i didn't answer your one question correctly. the subform uses the
query for the order details that match the order shown on the main form. the
query has the field Ext price:=[unit price]*[Qnty]. This Ext price field is
shown on the subform. I have a control in the main form labelled subtotal.
I want the subtotal control to show the sum of the extended price fields
from the sub form....I hope that is more clear.
--
Bob Love

What error did you get?
Is your calculated control in the subform's footer like it is in the Orders
subform?

BTW using a $ in a name is a very bad idea.
--
Dave Hargis, Microsoft Access MVP

I viewed the report of the north winds and the use the expression
=Nz(Sum(Extended Price),0)....I tried this substituting my field "Ext $" for
Extended price and it gave me an error. What about using a calculated
expression "=Sum([Forms]![Order Details Subform]![Ext $]) ..... should this
work

--
Bob Love

Check out the Orders form and the Orders Subform in the Northwind sample
database. It has a very good example of how that is done.
--
Dave Hargis, Microsoft Access MVP


:

This hopefully is a very simple answer but I just can't seem to get it. I
want to track my orders with my venders. I'm trying to use a Main Form/ Sub
form format. The main form has my vender and ordeer fields from that table
and my sub form shows the order details (I.E. Quantity, description, unit $.)
I use a query to find all details that match the order # in the main form.
I have a field on the query the uses the expression of Qnty * Unit $ to
calculate the extended $ but I can't seem to get the sum of the extended $
to the subtotal block on the main form. I know this should be simple but I
can't seem to figure it out. Thanks in advance for any and all help.

 

[Klatuu]

If you notice in the footer of Orders Subform, there is a control named
OrderSubTotal with the control source of:
=Sum([ExtendedPrice])

That is what does the subtotaling; however, you don't see the footer of a
subform.

ExtendedPrice has a control source of ExtendedPrice which at first glance is
confusing, but if you look at the subform's record soucrce, the ExtendedPrice
is a calculated field:

ExtendedPrice: CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100


Where you see it is in the control on the main form named Subtotal. It has
the control source of:
=[Orders Subform].Form!OrderSubtotal

So that's how it works.
The extended price is calucluated in the record source for each row.
The subtotal for all rows is calculated using the Sum in the form footer of
the subform.
A visible control on the main form shows the current value of the hidden
control on the subform.


--
Dave Hargis, Microsoft Access MVP

I think i didn't answer your one question correctly. the subform uses the
query for the order details that match the order shown on the main form. the
query has the field Ext price:=[unit price]*[Qnty]. This Ext price field is
shown on the subform. I have a control in the main form labelled subtotal.
I want the subtotal control to show the sum of the extended price fields
from the sub form....I hope that is more clear.
--
Bob Love

What error did you get?
Is your calculated control in the subform's footer like it is in the Orders
subform?

BTW using a $ in a name is a very bad idea.
--
Dave Hargis, Microsoft Access MVP

I viewed the report of the north winds and the use the expression
=Nz(Sum(Extended Price),0)....I tried this substituting my field "Ext $" for
Extended price and it gave me an error. What about using a calculated
expression "=Sum([Forms]![Order Details Subform]![Ext $]) ..... should this
work

--
Bob Love


:

Check out the Orders form and the Orders Subform in the Northwind sample
database. It has a very good example of how that is done.
--
Dave Hargis, Microsoft Access MVP


:

This hopefully is a very simple answer but I just can't seem to get it. I
want to track my orders with my venders. I'm trying to use a Main Form/ Sub
form format. The main form has my vender and ordeer fields from that table
and my sub form shows the order details (I.E. Quantity, description, unit $.)
I use a query to find all details that match the order # in the main form.
I have a field on the query the uses the expression of Qnty * Unit $ to
calculate the extended $ but I can't seem to get the sum of the extended $
to the subtotal block on the main form. I know this should be simple but I
can't seem to figure it out. Thanks in advance for any and all help.

 

[Bob Love]

I'm looking at the north wind traders data base for access 2007 and can't
find a form named Orders Subform. I also can't find a control in the footer
of the forms. Is the example your talking about in the access 2003 version
of northwind traders?
--
Bob Love

If you notice in the footer of Orders Subform, there is a control named
OrderSubTotal with the control source of:
=Sum([ExtendedPrice])

That is what does the subtotaling; however, you don't see the footer of a
subform.

ExtendedPrice has a control source of ExtendedPrice which at first glance is
confusing, but if you look at the subform's record soucrce, the ExtendedPrice
is a calculated field:

ExtendedPrice: CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100


Where you see it is in the control on the main form named Subtotal. It has
the control source of:
=[Orders Subform].Form!OrderSubtotal

So that's how it works.
The extended price is calucluated in the record source for each row.
The subtotal for all rows is calculated using the Sum in the form footer of
the subform.
A visible control on the main form shows the current value of the hidden
control on the subform.


--
Dave Hargis, Microsoft Access MVP

I think i didn't answer your one question correctly. the subform uses the
query for the order details that match the order shown on the main form. the
query has the field Ext price:=[unit price]*[Qnty]. This Ext price field is
shown on the subform. I have a control in the main form labelled subtotal.
I want the subtotal control to show the sum of the extended price fields
from the sub form....I hope that is more clear.
--
Bob Love

What error did you get?
Is your calculated control in the subform's footer like it is in the Orders
subform?

BTW using a $ in a name is a very bad idea.
--
Dave Hargis, Microsoft Access MVP


:

I viewed the report of the north winds and the use the expression
=Nz(Sum(Extended Price),0)....I tried this substituting my field "Ext $" for
Extended price and it gave me an error. What about using a calculated
expression "=Sum([Forms]![Order Details Subform]![Ext $]) ..... should this
work

--
Bob Love


:

Check out the Orders form and the Orders Subform in the Northwind sample
database. It has a very good example of how that is done.
--
Dave Hargis, Microsoft Access MVP


:

This hopefully is a very simple answer but I just can't seem to get it. I
want to track my orders with my venders. I'm trying to use a Main Form/ Sub
form format. The main form has my vender and ordeer fields from that table
and my sub form shows the order details (I.E. Quantity, description, unit $.)
I use a query to find all details that match the order # in the main form.
I have a field on the query the uses the expression of Qnty * Unit $ to
calculate the extended $ but I can't seem to get the sum of the extended $
to the subtotal block on the main form. I know this should be simple but I
can't seem to figure it out. Thanks in advance for any and all help.

 

[Klatuu]

Yes, I am using 2003.
I have not looked at 2007 Northwind, yet, so I don't know what they are
doing in there.
--
Dave Hargis, Microsoft Access MVP

I'm looking at the north wind traders data base for access 2007 and can't
find a form named Orders Subform. I also can't find a control in the footer
of the forms. Is the example your talking about in the access 2003 version
of northwind traders?
--
Bob Love

If you notice in the footer of Orders Subform, there is a control named
OrderSubTotal with the control source of:
=Sum([ExtendedPrice])

That is what does the subtotaling; however, you don't see the footer of a
subform.

ExtendedPrice has a control source of ExtendedPrice which at first glance is
confusing, but if you look at the subform's record soucrce, the ExtendedPrice
is a calculated field:

ExtendedPrice: CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100


Where you see it is in the control on the main form named Subtotal. It has
the control source of:
=[Orders Subform].Form!OrderSubtotal

So that's how it works.
The extended price is calucluated in the record source for each row.
The subtotal for all rows is calculated using the Sum in the form footer of
the subform.
A visible control on the main form shows the current value of the hidden
control on the subform.


--
Dave Hargis, Microsoft Access MVP

I think i didn't answer your one question correctly. the subform uses the
query for the order details that match the order shown on the main form. the
query has the field Ext price:=[unit price]*[Qnty]. This Ext price field is
shown on the subform. I have a control in the main form labelled subtotal.
I want the subtotal control to show the sum of the extended price fields
from the sub form....I hope that is more clear.
--
Bob Love


:

What error did you get?
Is your calculated control in the subform's footer like it is in the Orders
subform?

BTW using a $ in a name is a very bad idea.
--
Dave Hargis, Microsoft Access MVP


:

I viewed the report of the north winds and the use the expression
=Nz(Sum(Extended Price),0)....I tried this substituting my field "Ext $" for
Extended price and it gave me an error. What about using a calculated
expression "=Sum([Forms]![Order Details Subform]![Ext $]) ..... should this
work

--
Bob Love


:

Check out the Orders form and the Orders Subform in the Northwind sample
database. It has a very good example of how that is done.
--
Dave Hargis, Microsoft Access MVP


:

This hopefully is a very simple answer but I just can't seem to get it. I
want to track my orders with my venders. I'm trying to use a Main Form/ Sub
form format. The main form has my vender and ordeer fields from that table
and my sub form shows the order details (I.E. Quantity, description, unit $.)
I use a query to find all details that match the order # in the main form.
I have a field on the query the uses the expression of Qnty * Unit $ to
calculate the extended $ but I can't seem to get the sum of the extended $
to the subtotal block on the main form. I know this should be simple but I
can't seem to figure it out. Thanks in advance for any and all help.

 

[Bob Love]

Ah...That might be part of my confusion.....Okay I've added a control to the
bottom of my subform with the control source property set to =Sum([Ext
price]) . the subform now displays the total. now I want to set the Visable
property of the that control to no and have the control in the main form
display the same number. I used the expression builder and set the control
source property of the control in the main form to =[Forms]![Order Details
Query Subform]![Subtotal] but the control in the main form says "#name".....
--
Bob Love

Yes, I am using 2003.
I have not looked at 2007 Northwind, yet, so I don't know what they are
doing in there.
--
Dave Hargis, Microsoft Access MVP

I'm looking at the north wind traders data base for access 2007 and can't
find a form named Orders Subform. I also can't find a control in the footer
of the forms. Is the example your talking about in the access 2003 version
of northwind traders?
--
Bob Love

If you notice in the footer of Orders Subform, there is a control named
OrderSubTotal with the control source of:
=Sum([ExtendedPrice])

That is what does the subtotaling; however, you don't see the footer of a
subform.

ExtendedPrice has a control source of ExtendedPrice which at first glance is
confusing, but if you look at the subform's record soucrce, the ExtendedPrice
is a calculated field:

ExtendedPrice: CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100


Where you see it is in the control on the main form named Subtotal. It has
the control source of:
=[Orders Subform].Form!OrderSubtotal

So that's how it works.
The extended price is calucluated in the record source for each row.
The subtotal for all rows is calculated using the Sum in the form footer of
the subform.
A visible control on the main form shows the current value of the hidden
control on the subform.


--
Dave Hargis, Microsoft Access MVP


:

I think i didn't answer your one question correctly. the subform uses the
query for the order details that match the order shown on the main form. the
query has the field Ext price:=[unit price]*[Qnty]. This Ext price field is
shown on the subform. I have a control in the main form labelled subtotal.
I want the subtotal control to show the sum of the extended price fields
from the sub form....I hope that is more clear.
--
Bob Love


:

What error did you get?
Is your calculated control in the subform's footer like it is in the Orders
subform?

BTW using a $ in a name is a very bad idea.
--
Dave Hargis, Microsoft Access MVP


:

I viewed the report of the north winds and the use the expression
=Nz(Sum(Extended Price),0)....I tried this substituting my field "Ext $" for
Extended price and it gave me an error. What about using a calculated
expression "=Sum([Forms]![Order Details Subform]![Ext $]) ..... should this
work

--
Bob Love


:

Check out the Orders form and the Orders Subform in the Northwind sample
database. It has a very good example of how that is done.
--
Dave Hargis, Microsoft Access MVP


:

This hopefully is a very simple answer but I just can't seem to get it. I
want to track my orders with my venders. I'm trying to use a Main Form/ Sub
form format. The main form has my vender and ordeer fields from that table
and my sub form shows the order details (I.E. Quantity, description, unit $.)
I use a query to find all details that match the order # in the main form.
I have a field on the query the uses the expression of Qnty * Unit $ to
calculate the extended $ but I can't seem to get the sum of the extended $
to the subtotal block on the main form. I know this should be simple but I
can't seem to figure it out. Thanks in advance for any and all help.

 

[Bob Love]

Okay...I removed the "[Forms]" from the control source for the subtotal
control in the main form and it displayed correctly.
--
Bob Love

Ah...That might be part of my confusion.....Okay I've added a control to the
bottom of my subform with the control source property set to =Sum([Ext
price]) . the subform now displays the total. now I want to set the Visable
property of the that control to no and have the control in the main form
display the same number. I used the expression builder and set the control
source property of the control in the main form to =[Forms]![Order Details
Query Subform]![Subtotal] but the control in the main form says "#name".....
--
Bob Love

Yes, I am using 2003.
I have not looked at 2007 Northwind, yet, so I don't know what they are
doing in there.
--
Dave Hargis, Microsoft Access MVP

I'm looking at the north wind traders data base for access 2007 and can't
find a form named Orders Subform. I also can't find a control in the footer
of the forms. Is the example your talking about in the access 2003 version
of northwind traders?
--
Bob Love


:

If you notice in the footer of Orders Subform, there is a control named
OrderSubTotal with the control source of:
=Sum([ExtendedPrice])

That is what does the subtotaling; however, you don't see the footer of a
subform.

ExtendedPrice has a control source of ExtendedPrice which at first glance is
confusing, but if you look at the subform's record soucrce, the ExtendedPrice
is a calculated field:

ExtendedPrice: CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100


Where you see it is in the control on the main form named Subtotal. It has
the control source of:
=[Orders Subform].Form!OrderSubtotal

So that's how it works.
The extended price is calucluated in the record source for each row.
The subtotal for all rows is calculated using the Sum in the form footer of
the subform.
A visible control on the main form shows the current value of the hidden
control on the subform.


--
Dave Hargis, Microsoft Access MVP


:

I think i didn't answer your one question correctly. the subform uses the
query for the order details that match the order shown on the main form. the
query has the field Ext price:=[unit price]*[Qnty]. This Ext price field is
shown on the subform. I have a control in the main form labelled subtotal.
I want the subtotal control to show the sum of the extended price fields
from the sub form....I hope that is more clear.
--
Bob Love


:

What error did you get?
Is your calculated control in the subform's footer like it is in the Orders
subform?

BTW using a $ in a name is a very bad idea.
--
Dave Hargis, Microsoft Access MVP


:

I viewed the report of the north winds and the use the expression
=Nz(Sum(Extended Price),0)....I tried this substituting my field "Ext $" for
Extended price and it gave me an error. What about using a calculated
expression "=Sum([Forms]![Order Details Subform]![Ext $]) ..... should this
work

--
Bob Love


:

Check out the Orders form and the Orders Subform in the Northwind sample
database. It has a very good example of how that is done.
--
Dave Hargis, Microsoft Access MVP


:

This hopefully is a very simple answer but I just can't seem to get it. I
want to track my orders with my venders. I'm trying to use a Main Form/ Sub
form format. The main form has my vender and ordeer fields from that table
and my sub form shows the order details (I.E. Quantity, description, unit $.)
I use a query to find all details that match the order # in the main form.
I have a field on the query the uses the expression of Qnty * Unit $ to
calculate the extended $ but I can't seem to get the sum of the extended $
to the subtotal block on the main form. I know this should be simple but I
can't seem to figure it out. Thanks in advance for any and all help.

 

[John Spencer]

TRY the following

=[Forms]![Order Details Query Subform].Form![Subtotal]

Assumption the name of the subform control is [Order Details Query Subform].
The NAME of the control can be different than the name of the form that the
control is bound to.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Love]

I will try the "=[Forms]![Order Details Query Subform].Form![Subtotal])
Should there be an s after the second form? I.E. Forms![Subtotal])....Thanks
for all the help
--
Bob Love

TRY the following

=[Forms]![Order Details Query Subform].Form![Subtotal]

Assumption the name of the subform control is [Order Details Query Subform].
The NAME of the control can be different than the name of the form that the
control is bound to.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Ah...That might be part of my confusion.....Okay I've added a control to the
bottom of my subform with the control source property set to =Sum([Ext
price]) . the subform now displays the total. now I want to set the Visable
property of the that control to no and have the control in the main form
display the same number. I used the expression builder and set the control
source property of the control in the main form to =[Forms]![Order Details
Query Subform]![Subtotal] but the control in the main form says "#name".....

 

[Klatuu]

No, there should not be an s after the second form.
That is a different object reference.
What you are doing is referring the the subform control on the main form,
not directly to the subform that is the Source Object of the subform control.
The second form refers to the the form itselft, then after that you refer to
the controls so it is:
Forms!NameOfForm!NameOfSubformControl.Form!TextBoxOnSubForm

If you put the sum text box in the footer of the subform, you should not
have to explicitly make it invisible. The form header and footer sections of
a form are not visible when being used as a subform.
--
Dave Hargis, Microsoft Access MVP

I will try the "=[Forms]![Order Details Query Subform].Form![Subtotal])
Should there be an s after the second form? I.E. Forms![Subtotal])....Thanks
for all the help
--
Bob Love

TRY the following

=[Forms]![Order Details Query Subform].Form![Subtotal]

Assumption the name of the subform control is [Order Details Query Subform].
The NAME of the control can be different than the name of the form that the
control is bound to.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Ah...That might be part of my confusion.....Okay I've added a control to the
bottom of my subform with the control source property set to =Sum([Ext
price]) . the subform now displays the total. now I want to set the Visable
property of the that control to no and have the control in the main form
display the same number. I used the expression builder and set the control
source property of the control in the main form to =[Forms]![Order Details
Query Subform]![Subtotal] but the control in the main form says "#name".....

 

[John Spencer]

UHMMM! I think Dave Hargis is thinking of Report Headers and Footers on
subreports.

The form headers and footers of subforms are visible in the subform. I use
them quite frequently.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Klatuu]

Well, not in datasheet view.