HELP! subform - adding up a field in the rows for the subform

G

Guest

Hi to anyone that can help

I have a form with a subform, within subform there is a field that has a
value called subtotal for each row in the subform.

Is there a way for me to add up all the values in the field for all the rows
created in the subform.

Could have a cmd button to show totalvalue when clicked. But how do I
reference the field subtotal from the first row to the last row created and
ask it to give me a total sum for those fields?

Would really appreciate some help with this.

Thanks in advance.
 
G

Guest

You can use a calculated control in the subform using the DSum function. How
to set it up depends on how you select and sort the data on the subform so I
can't give exact details, but as an example consider a subform based on a
table called "PAYMENTS" containing payments ([Amount]) sorted by date
([PaymentDate]) for a particular customer ([Customer]) determined by your
main form; the calculation to create a running subtotal here would be:
=DSum("[Amount]","PAYMENTS","[Customer]='"&[MainForm].[Customer]&"' AND
[PaymentDate]<=#"&[Subform].[PaymentDate]&"#")

One note: Any of the domain aggregate functions can slow things down
noticeably for large databases.
 
G

Guest

Thanks for getting back so quick, but it sounds a bit complicated, not an
experienced access user so not sure what to do here..

I have a form called order with subform called orderline.
Order has a date field and orderno field.
Orderline has a orderlineno and orderno field, with also quantityOrd and
price field.
the subtotal is field for each row is calculated and displayed in the row
but not stored, it is quantityOrd * price.

I then have a field out of the subform which totaled the subtotal field, but
doesnt work to well.

This is why was thinking of just having a cmdbutton button that user could
click at anytime on the form to add up the fields subtotal rows in the
subform.
Want it to look at the subform and see if any rows created and then to look
at the subtotal field, take the value from first and any other rows after
that and sum them up.

But dont know how you could reference the rows in a subform and the field in
the subform to do the calculation.

If your way is better, then not sure which fields go where in code you
provided etc... some help here would be very much appreciated.

Thanks in advance.

K Dales said:
You can use a calculated control in the subform using the DSum function. How
to set it up depends on how you select and sort the data on the subform so I
can't give exact details, but as an example consider a subform based on a
table called "PAYMENTS" containing payments ([Amount]) sorted by date
([PaymentDate]) for a particular customer ([Customer]) determined by your
main form; the calculation to create a running subtotal here would be:
=DSum("[Amount]","PAYMENTS","[Customer]='"&[MainForm].[Customer]&"' AND
[PaymentDate]<=#"&[Subform].[PaymentDate]&"#")

One note: Any of the domain aggregate functions can slow things down
noticeably for large databases.
--
- K Dales


Saj said:
Hi to anyone that can help

I have a form with a subform, within subform there is a field that has a
value called subtotal for each row in the subform.

Is there a way for me to add up all the values in the field for all the rows
created in the subform.

Could have a cmd button to show totalvalue when clicked. But how do I
reference the field subtotal from the first row to the last row created and
ask it to give me a total sum for those fields?

Would really appreciate some help with this.

Thanks in advance.
 

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