running sum from a subform

G

Guest

I am trying to add a calculated control on a main form that pulls its
information from the subform. The subform is in datasheet view and I am
pulling the information from a caluclated control in it as well. Essently I
want it to work just like a recipt where the subform produces a total cost
per items purchased (there can be more than one item) and the main form
produces a total of all items purchased. I have tried the Sum() function and
that is not working. I see info about the DSum function on preivious post
but can't seem to find more indeath info to make sure I am doing it right.
Could anyone please help?

P.S. I would also like to know if it is possible to store a calculated
field. I have another section that will work like this but I will need to be
able to access the total as a seperate peice of information for something
else and don't want to have to have it recalculate everytime I do that
 
G

Guest

Hi, Millisa.

The usual way of handling this is to insert a control in the subform footer,
whose Visible property is set to No, that totals the field, its Control
Source equal to:

=Sum([MyField]), or Sum([MyCalculation])

Then, to display it in the main form, you just reference the subform control:

=Me![YourSubform].Form![TheSummationControlName]

Re: storing calculated fields...of course it is possible, you simply set a
bound control's value to your calculation:

Me![MyBoundControl]=YourExpression,

BUT! it is really redundant. You can recreate this value at will in a query
or in a form control, so why do it?

HTH
Sprinks
 
G

Guest

I could kiss you Sprinks! Thanks so much this worked beautifully! Just out
of curiosity do you happen to know the answer to the PS about storing a
calculated value? I know your not suppose to do this generally speaking but
need to access one of the 2 totals I will be calculating in the sub form for
something else. Or is it possible to access the calculated field in the sub
form without the form being open at all?

Sprinks said:
Hi, Millisa.

The usual way of handling this is to insert a control in the subform footer,
whose Visible property is set to No, that totals the field, its Control
Source equal to:

=Sum([MyField]), or Sum([MyCalculation])

Then, to display it in the main form, you just reference the subform control:

=Me![YourSubform].Form![TheSummationControlName]

Re: storing calculated fields...of course it is possible, you simply set a
bound control's value to your calculation:

Me![MyBoundControl]=YourExpression,

BUT! it is really redundant. You can recreate this value at will in a query
or in a form control, so why do it?

HTH
Sprinks

Millisa Eubanks said:
I am trying to add a calculated control on a main form that pulls its
information from the subform. The subform is in datasheet view and I am
pulling the information from a caluclated control in it as well. Essently I
want it to work just like a recipt where the subform produces a total cost
per items purchased (there can be more than one item) and the main form
produces a total of all items purchased. I have tried the Sum() function and
that is not working. I see info about the DSum function on preivious post
but can't seem to find more indeath info to make sure I am doing it right.
Could anyone please help?

P.S. I would also like to know if it is possible to store a calculated
field. I have another section that will work like this but I will need to be
able to access the total as a seperate peice of information for something
else and don't want to have to have it recalculate everytime I do that
 
G

Guest

Hi, Millisa. Glad it worked out. Yes, you can always store the calculation
as I described earlier, BUT a query can provide the value you need at any
time. Create a Totals query (View, Totals from Query Design View), and cut
and paste the SQL for the query to the place where you need it.

Best regards.
Sprinks

Millisa Eubanks said:
I could kiss you Sprinks! Thanks so much this worked beautifully! Just out
of curiosity do you happen to know the answer to the PS about storing a
calculated value? I know your not suppose to do this generally speaking but
need to access one of the 2 totals I will be calculating in the sub form for
something else. Or is it possible to access the calculated field in the sub
form without the form being open at all?

Sprinks said:
Hi, Millisa.

The usual way of handling this is to insert a control in the subform footer,
whose Visible property is set to No, that totals the field, its Control
Source equal to:

=Sum([MyField]), or Sum([MyCalculation])

Then, to display it in the main form, you just reference the subform control:

=Me![YourSubform].Form![TheSummationControlName]

Re: storing calculated fields...of course it is possible, you simply set a
bound control's value to your calculation:

Me![MyBoundControl]=YourExpression,

BUT! it is really redundant. You can recreate this value at will in a query
or in a form control, so why do it?

HTH
Sprinks

Millisa Eubanks said:
I am trying to add a calculated control on a main form that pulls its
information from the subform. The subform is in datasheet view and I am
pulling the information from a caluclated control in it as well. Essently I
want it to work just like a recipt where the subform produces a total cost
per items purchased (there can be more than one item) and the main form
produces a total of all items purchased. I have tried the Sum() function and
that is not working. I see info about the DSum function on preivious post
but can't seem to find more indeath info to make sure I am doing it right.
Could anyone please help?

P.S. I would also like to know if it is possible to store a calculated
field. I have another section that will work like this but I will need to be
able to access the total as a seperate peice of information for something
else and don't want to have to have it recalculate everytime I do that
 
G

Guest

Ok I don't know what happened, but I opened the form today and now I'm
getting the Error# again. I have not changed anything so I don't understand
what happened. Help!

Sprinks said:
Hi, Millisa. Glad it worked out. Yes, you can always store the calculation
as I described earlier, BUT a query can provide the value you need at any
time. Create a Totals query (View, Totals from Query Design View), and cut
and paste the SQL for the query to the place where you need it.

Best regards.
Sprinks

Millisa Eubanks said:
I could kiss you Sprinks! Thanks so much this worked beautifully! Just out
of curiosity do you happen to know the answer to the PS about storing a
calculated value? I know your not suppose to do this generally speaking but
need to access one of the 2 totals I will be calculating in the sub form for
something else. Or is it possible to access the calculated field in the sub
form without the form being open at all?

Sprinks said:
Hi, Millisa.

The usual way of handling this is to insert a control in the subform footer,
whose Visible property is set to No, that totals the field, its Control
Source equal to:

=Sum([MyField]), or Sum([MyCalculation])

Then, to display it in the main form, you just reference the subform control:

=Me![YourSubform].Form![TheSummationControlName]

Re: storing calculated fields...of course it is possible, you simply set a
bound control's value to your calculation:

Me![MyBoundControl]=YourExpression,

BUT! it is really redundant. You can recreate this value at will in a query
or in a form control, so why do it?

HTH
Sprinks

:

I am trying to add a calculated control on a main form that pulls its
information from the subform. The subform is in datasheet view and I am
pulling the information from a caluclated control in it as well. Essently I
want it to work just like a recipt where the subform produces a total cost
per items purchased (there can be more than one item) and the main form
produces a total of all items purchased. I have tried the Sum() function and
that is not working. I see info about the DSum function on preivious post
but can't seem to find more indeath info to make sure I am doing it right.
Could anyone please help?

P.S. I would also like to know if it is possible to store a calculated
field. I have another section that will work like this but I will need to be
able to access the total as a seperate peice of information for something
else and don't want to have to have it recalculate everytime I do that
 
G

Guest

Nevermind the last post. I got it working agian but I'm not sure what went
wrong or how I fixed it. Oh well. What I'm trying to do now is to figure
out how to get a query to pull the values. I've tried my referance books but
no dice. Could I please get some pointers
 
G

Guest

Millisa,

Begin a new query, selecting the table or tables that contain the fields of
your expression. In the query design grid, in the Field row, enter a
calculated field, following the syntax:

CalcalatedFieldName: expression

For example, to calculate the extended price of a line item,

Extd: [Qty]*[UnitPrice]

Press the Exclamation Point to see the result, which will be a list of
[Qty]*[UnitPrice] for each line item in your table.

To sum this calculated field for ALL records in your database, switch to
Totals View (View, Totals). Change the default "Group By" in the Totals row
to Sum. Now click the Exclamation Point to see the result.

If you wish to calculate a total, grouping on some other field (such as
Order), drag that table's primary key (OrderNumber in this case) to the grid,
and keep the default "Group By" in the Total: row. This will now calculate a
separate total for each unique OrderNumber.

If, in addition, you have selection criteria, such as selecting records from
this year, drag the field to the grid, select WHERE in the Total: row, and
enter your criteria.

Hope that solves it.
Sprinks
 
G

Guest

Thanks you for your help Sprinks. I only have 1 more real issue (or at least
I think its the last).
I was able to build a query that did the calcuations. But while I am later
applying a filter to theses fields by actively building a WHERE statement for
the OpenForm property, I run into the problem of to use HAVING instead of
WHERE. This means I can't use the calcualted query. So I'm going to have to
store the values after all. I trid what you said by putting Me![TOTAL$] =
Sum([QUANITY] *[UPRICE]) in the bound control property box but I just got a
Name# error. I can't think of where else to put this info. Any ideas.
Sprinks said:
Millisa,

Begin a new query, selecting the table or tables that contain the fields of
your expression. In the query design grid, in the Field row, enter a
calculated field, following the syntax:

CalcalatedFieldName: expression

For example, to calculate the extended price of a line item,

Extd: [Qty]*[UnitPrice]

Press the Exclamation Point to see the result, which will be a list of
[Qty]*[UnitPrice] for each line item in your table.

To sum this calculated field for ALL records in your database, switch to
Totals View (View, Totals). Change the default "Group By" in the Totals row
to Sum. Now click the Exclamation Point to see the result.

If you wish to calculate a total, grouping on some other field (such as
Order), drag that table's primary key (OrderNumber in this case) to the grid,
and keep the default "Group By" in the Total: row. This will now calculate a
separate total for each unique OrderNumber.

If, in addition, you have selection criteria, such as selecting records from
this year, drag the field to the grid, select WHERE in the Total: row, and
enter your criteria.

Hope that solves it.
Sprinks

Millisa Eubanks said:
Nevermind the last post. I got it working agian but I'm not sure what went
wrong or how I fixed it. Oh well. What I'm trying to do now is to figure
out how to get a query to pull the values. I've tried my referance books but
no dice. Could I please get some pointers
 
G

Guest

Hello Sprinks,

I have this same problem of needing to store a calculated field from a
subform onto the main form (and hence, back to the table). I have a field on
the main form with
=[frm subform test].Form!Text36
as the control source. It displays the correct data beautifully, however,
it does not store it back in the table. Can you tell me how to do this?

The reason I need the calc'd field stored on the main form's table is that
that the field represents the sum of any number of purchases from one vendor
that must be entered individually in the subform for cost accounting purposes
but used as a single number (the calc'd value) for reconciling to a credit
card st atement. A query needs to be run based on a date range and then the
ysn field is checked off as the calc'd value is reconciled.
Therefore, the form(s) must be based on tables and not queries, otherwise
when a query is run on those queries, there is no way to "update" the table
by checking off the ysn field. Make sense?


Sprinks said:
Hi, Millisa.

The usual way of handling this is to insert a control in the subform footer,
whose Visible property is set to No, that totals the field, its Control
Source equal to:

=Sum([MyField]), or Sum([MyCalculation])

Then, to display it in the main form, you just reference the subform control:

=Me![YourSubform].Form![TheSummationControlName]

Re: storing calculated fields...of course it is possible, you simply set a
bound control's value to your calculation:

Me![MyBoundControl]=YourExpression,

BUT! it is really redundant. You can recreate this value at will in a query
or in a form control, so why do it?

HTH
Sprinks

Millisa Eubanks said:
I am trying to add a calculated control on a main form that pulls its
information from the subform. The subform is in datasheet view and I am
pulling the information from a caluclated control in it as well. Essently I
want it to work just like a recipt where the subform produces a total cost
per items purchased (there can be more than one item) and the main form
produces a total of all items purchased. I have tried the Sum() function and
that is not working. I see info about the DSum function on preivious post
but can't seem to find more indeath info to make sure I am doing it right.
Could anyone please help?

P.S. I would also like to know if it is possible to store a calculated
field. I have another section that will work like this but I will need to be
able to access the total as a seperate peice of information for something
else and don't want to have to have it recalculate everytime I do that
 
G

Guest

Don't ever, ever store calculated fields in tables. That is a complete NO! NO!

The purpose of a table is to store the essential information you require.
Any other information that can be gained can be calculated through queries.

--
How many buildings collapsed on 9/11?

I can tell you the answer isn''t 2 !!


natalie said:
Hello Sprinks,

I have this same problem of needing to store a calculated field from a
subform onto the main form (and hence, back to the table). I have a field on
the main form with
=[frm subform test].Form!Text36
as the control source. It displays the correct data beautifully, however,
it does not store it back in the table. Can you tell me how to do this?

The reason I need the calc'd field stored on the main form's table is that
that the field represents the sum of any number of purchases from one vendor
that must be entered individually in the subform for cost accounting purposes
but used as a single number (the calc'd value) for reconciling to a credit
card st atement. A query needs to be run based on a date range and then the
ysn field is checked off as the calc'd value is reconciled.
Therefore, the form(s) must be based on tables and not queries, otherwise
when a query is run on those queries, there is no way to "update" the table
by checking off the ysn field. Make sense?


Sprinks said:
Hi, Millisa.

The usual way of handling this is to insert a control in the subform footer,
whose Visible property is set to No, that totals the field, its Control
Source equal to:

=Sum([MyField]), or Sum([MyCalculation])

Then, to display it in the main form, you just reference the subform control:

=Me![YourSubform].Form![TheSummationControlName]

Re: storing calculated fields...of course it is possible, you simply set a
bound control's value to your calculation:

Me![MyBoundControl]=YourExpression,

BUT! it is really redundant. You can recreate this value at will in a query
or in a form control, so why do it?

HTH
Sprinks

Millisa Eubanks said:
I am trying to add a calculated control on a main form that pulls its
information from the subform. The subform is in datasheet view and I am
pulling the information from a caluclated control in it as well. Essently I
want it to work just like a recipt where the subform produces a total cost
per items purchased (there can be more than one item) and the main form
produces a total of all items purchased. I have tried the Sum() function and
that is not working. I see info about the DSum function on preivious post
but can't seem to find more indeath info to make sure I am doing it right.
Could anyone please help?

P.S. I would also like to know if it is possible to store a calculated
field. I have another section that will work like this but I will need to be
able to access the total as a seperate peice of information for something
else and don't want to have to have it recalculate everytime I do that
 
D

Douglas J. Steele

<picky>
There are times when it is legitmate to store a calculated value. This
doesn't sound like one of those cases, though.
</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


scubadiver said:
Don't ever, ever store calculated fields in tables. That is a complete NO!
NO!

The purpose of a table is to store the essential information you require.
Any other information that can be gained can be calculated through
queries.

--
How many buildings collapsed on 9/11?

I can tell you the answer isn''t 2 !!


natalie said:
Hello Sprinks,

I have this same problem of needing to store a calculated field from a
subform onto the main form (and hence, back to the table). I have a
field on
the main form with
=[frm subform test].Form!Text36
as the control source. It displays the correct data beautifully,
however,
it does not store it back in the table. Can you tell me how to do this?

The reason I need the calc'd field stored on the main form's table is
that
that the field represents the sum of any number of purchases from one
vendor
that must be entered individually in the subform for cost accounting
purposes
but used as a single number (the calc'd value) for reconciling to a
credit
card st atement. A query needs to be run based on a date range and then
the
ysn field is checked off as the calc'd value is reconciled.
Therefore, the form(s) must be based on tables and not queries, otherwise
when a query is run on those queries, there is no way to "update" the
table
by checking off the ysn field. Make sense?


Sprinks said:
Hi, Millisa.

The usual way of handling this is to insert a control in the subform
footer,
whose Visible property is set to No, that totals the field, its
Control
Source equal to:

=Sum([MyField]), or Sum([MyCalculation])

Then, to display it in the main form, you just reference the subform
control:

=Me![YourSubform].Form![TheSummationControlName]

Re: storing calculated fields...of course it is possible, you simply
set a
bound control's value to your calculation:

Me![MyBoundControl]=YourExpression,

BUT! it is really redundant. You can recreate this value at will in a
query
or in a form control, so why do it?

HTH
Sprinks

:

I am trying to add a calculated control on a main form that pulls its
information from the subform. The subform is in datasheet view and I
am
pulling the information from a caluclated control in it as well.
Essently I
want it to work just like a recipt where the subform produces a total
cost
per items purchased (there can be more than one item) and the main
form
produces a total of all items purchased. I have tried the Sum()
function and
that is not working. I see info about the DSum function on preivious
post
but can't seem to find more indeath info to make sure I am doing it
right.
Could anyone please help?

P.S. I would also like to know if it is possible to store a
calculated
field. I have another section that will work like this but I will
need to be
able to access the total as a seperate peice of information for
something
else and don't want to have to have it recalculate everytime I do
that
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top