Sum Line Totals

[Guest]

I have a subform for a purchase order database that contains the product
information. Anyway, I have text boxes which are calculated fields on each
line. For example, someone is ordering paper. They fill out the price and
quantity and it calculates the total price for each product. You can order
as many products from one company as necessary. In the footer of the
subform, I am attempting to sum all the line totals from above, then I have a
text box that pulls that value to the main form, but am having no success!
The formula I have now is =Sum([LineTotal]) which is the name of the
calculated field. Any ideas? Please help!

Thanks!

 

[AlCamp]

jped,
[LineTotal] is a calculated "unbound" field, so it can't be directly
summed in a footer.

Place this field in the query behind the form... (use your own names)
LineTotal : [Price] * [Qty]
Now the field [LineTotal] is a "bound" field, and can be placed on the
form, displays the LineTotal calculation, and can be Summed in the footer.
hth
Al Camp

 

[Guest]

AlCamp,

Thank you for the help. I created a query LineTotal: [UnitPrice] *
[Quantity] which works fine when I run it. I'm unsure of where to put it on
the form. I think I am missing something. Where do I enter this query at?

Thanks!

jped,
[LineTotal] is a calculated "unbound" field, so it can't be directly
summed in a footer.

Place this field in the query behind the form... (use your own names)
LineTotal : [Price] * [Qty]
Now the field [LineTotal] is a "bound" field, and can be placed on the
form, displays the LineTotal calculation, and can be Summed in the footer.
hth
Al Camp

I have a subform for a purchase order database that contains the product
information. Anyway, I have text boxes which are calculated fields on
each
line. For example, someone is ordering paper. They fill out the price
and
quantity and it calculates the total price for each product. You can
order
as many products from one company as necessary. In the footer of the
subform, I am attempting to sum all the line totals from above, then I
have a
text box that pulls that value to the main form, but am having no success!
The formula I have now is =Sum([LineTotal]) which is the name of the
calculated field. Any ideas? Please help!

Thanks!

 

[Guest]

Never mind! I got it worked out! I put that field in the query statement
and it works now. Thank you so much for your help!

jped,
[LineTotal] is a calculated "unbound" field, so it can't be directly
summed in a footer.

Place this field in the query behind the form... (use your own names)
LineTotal : [Price] * [Qty]
Now the field [LineTotal] is a "bound" field, and can be placed on the
form, displays the LineTotal calculation, and can be Summed in the footer.
hth
Al Camp

I have a subform for a purchase order database that contains the product
information. Anyway, I have text boxes which are calculated fields on
each
line. For example, someone is ordering paper. They fill out the price
and
quantity and it calculates the total price for each product. You can
order
as many products from one company as necessary. In the footer of the
subform, I am attempting to sum all the line totals from above, then I
have a
text box that pulls that value to the main form, but am having no success!
The formula I have now is =Sum([LineTotal]) which is the name of the
calculated field. Any ideas? Please help!

Thanks!

 

[Mwhite via AccessMonster.com]

Jped,
Could You help me?
It sounds like i have the same problem that you had. I am confused on the "Create a Query behind the form" part.
When I create a Query What variables do i add. I pull in the SalesLineItems Table and Pull out the "Quantity" and "Cost" Variables. Do I need to create another variable called ExtendedPrice? And then where do I Sum it in the Query Window?
Any help is appreciated. i have been pulling my hair out trying to figure this one out.
Thanks in Advance

 

[Guest]

Understandable! I have a form with a subform that contains quantity, cost
and Line Total. Right click on the form (or subform in my case) and select
properties. Click on the record source which should pull up a query that
your form is based on. Enter this into a new field in that query:

LineTotal: [UnitPrice]*[Quantity]

In your form, you must have a text box with the Name property set as
LineTotal. It will put price*quantity into that text box. Hope this helps!
Let me know if you need anymore help.

Thanks

 

[Mwhite via AccessMonster.com]

OK I'm still a little confused!
I look under forms and right click on the Subform for Line items Which Has the Item Part number, Description, Quantity, Cost And Extended cost. The properties do not make mention of a "Record"? What am I doing Wrong? The subform is not based on a Query to my knowledge. I pulled Values to it from a Lineitems Table which had the variables in it. then I created the "Extended cost" Field with a text box.
If you have not figured it out I feel a little over my head on this one.
Thanks

 

[Mwhite via AccessMonster.com]

Do you mean control source for the field Extended cost? I try entering that and it gives me an operator error.

 

[Guest]

When you created your form, did you use the wizard or deign view? If you
used the wizard, it should have automatically created a query based on the
table you are using for the information. If you just used the design view,
under properties of the subform select the Data tab. Under Record Source,
this is the query statement I have:

SELECT [tblOrderDetails].[PurcahseOrderID], [tblOrderDetails].[ProductID],
[tblOrderDetails].[UnitPrice], [tblOrderDetails].[Quantity],
[UnitPrice]*[Quantity] AS LineTotal FROM tblOrderDetails;

It created this automatically because I used the form wizard. I just went
in and added the LineTotal part of the statement

If you click on the arrow next to the statement it shows this query in
design view and is easier to understand. If you can get here, just add
LineTotal: [UnitPrice]*[Quantity] in a new field in that query and you have
to have a Text Box on your subform title LineTotal. I hope this helps.
Sorry if I haven't explained it well enough. Let me know. Also, what
version of Access are you using?

Good Luck!

 

[Matt White via AccessMonster.com]

Jped
I do Appreciate the help! Let me explain to give a little more background!
I am using Access 2003. I have a very similar situation that you described at the begining of your thread.
I have a subform that was created from a Wizard. The subform was created by bringing up a table I had created "tblLineItems" in this table were my variables "numPartNumber", "txtDescription", "curPrice", "intQuantity". then I created a text box which I named "curExtendedPrice". I then went into the properties of the text box "curExtendedPrice" and for the "Control source"(maybe they changed the naming convention, it is the first line on the data tab) I entered the following equation...

=nz([curPrice],0)*nz([intQuantity],0)

This produced my Extended Price for the line item. This part of it works fine.
Now When I goto the Footer of the subform and create a Text box trying to SUM all the Line Items, It Does not work. I named the text box "curLineTotals"
The Equation I am using in the "Conrol Source" is =Sum(nz([curPrice],0)*nz([intQuantity],0))
What am I doing wrong?
To clarify a little further on where I am looking...I am going to the "forms" opening my subform and the right clicking. I goto "properties" and the "data" tab. There is no "record source" listed.
Thanks for your patience in helping me!

 

[Guest]

Matt,

The control source for my text box in the footer of my subform contains this:

=Sum([LineTotal])

So maybe for yours it would be =Sum([curExtendedPrice])? Or something like
that? Then, in my main form I have a text box that displays this total. For
the control source of this text box, I have this:

=[frmOrderDetails Subform].Form!Subtotal

Subtotal is the name of my text box in the footer of my subform. Is this
what you're looking for? Let me know.