Calculations in form

[bebo2]

Hi,
I am trying to sum up AmountPaid by NameID in a form.
I can do it with a subform referring to a query but isn't there
a SUM calculation I could use? I can't find a formula that works.
Barb

 

[wizcow]

Barb

If I understand what you are trying to do, you could put an unbound textbox on your form.

Name it something like 'SumAmountPaid'
in the Control Source enter this code;

=Sum[AmountPaid]

Hope that helps
Tom

 

[bebo2]

Thank you for your reply but I have tried that and it
adds up ALL of the AmountPaid entries of ALL of the
NameIDs and what I need it to do is:
add up ALL AmountPaid per NameID. Thanks again for
your efforts does this clarify my need? And is there a
possible solution? Barb

 

[DBL]

How's your form laid out? Is it a subform with lots of NameID fields, multiple fields for one NameID?

 

[bebo2]

No subforms. One field each for NameID, AmountPaid,
PledgeID, DatePaid, AmountPledged, DatePledged....etc. As
each person pays their pledge/portion of pledge, that data
is input into this form. I had an unbound textbox that sums
the total of all AmountPledged, one that sums how many
NameIDs have pledged, and one for the total of all of the
AmountPaid to date. Does that make sense?

 

[wizcow]

Barb

I take it that your form's default view is set to 'Continous Forms'.

You could filter the data on your form to show one NameID and have a Sum.

Or maybe a Report would work for you, as all NameIDs could be grouped with a Sum.

As far as grouping on a form, with a group total... I don't know if you can do that.

Tom

 

[Rich]

Since you can have many payments per member, you should have a one to many tables set up, thereby using a form/subform where you can total per member without using a totals query

 

[bebo2]

Thank you both! Yes it is a continuous form. I did create a subform based on a query to add up by NameID on the main form and was hoping to be able to just set up an unbound text box - oh well! And I also realized that I could do the sum in a report by grouping per NameID...so you have answered my question! I appreciate your time and answers!! Barb

 

[Stephanie T.]

I’ve been following this thread and was hoping one of you could help me with a small calculation problem I’m having that seems similar.

I have a subform in continuous form that has a Quantity field, a CasePrice Field, and an Amount Discounted. These three fields are calculated in the Extended Price field with this in the Control Source:

=([Quantity]*[CasePrice])-[AmountDiscounted]

This works just fine.

Now I want to Sum the Extended Price field. But it seems that =Sum[ExtendedPrice] doesn’t work when one of the fields already has a calculation in it. This will work if I try to sum any of the other fields, but not any of the fields that have calculations in them.

Do any of you know how to Sum a field like this?

Any help is appreciated.

Thank you,
Stephanie

 

[wizcow]

Stephanie

Access can't sum calculated fields.

The calculation has to be done in actual input fields.
Although you can calculate a sum field.

On your sub form detail, add a text box called say, 'Sum'. In the 'Control Source' enter
=Sum([Quantity]*[CasePrice])-[AmountDiscounted]


To total your 'Sum' field, add a text box to you main form. Call it say, 'Total Sum'.
In the 'Control Source' enter something like this
=[YourSubFormName].Form![Sum]

I didn't try this, but it should work.

Hope that helps!

Tom

 

[Stephanie T.]

Dear Tom,

Thanks for your help. The problem is that with this code, the Sum field adds all of the Extended Price information (that is what I want to Sum) but subtracts the Amount Discounted for the line item the cursor is on. The Amount Discounted is made up of this

=([Quantity]*[CasePrice])*[Discount]

I suspect the problem has to do with another equation. I thought about creating a field that sums the total Amount Discounted, if that is an easier way to go, that actually works very well for me, as I was going to add that equation into the report later on.

Perhaps, I should do this through a query? If so, do you know how to write that?

Thanks again,
Stephanie

 

[wizcow]

Stephanie

I think you are right about the query.

Create a select query, with your table info in it.
Add the fields you need, including; [Quantity], [CasePrice]
and [Discount]

In a new column enter something like this in the 'field' line
AmountDiscounted: ([Quantity]*[CasePrice])*[Discount]

(The query knows that previous to the colon is the name of the colomn and after the colon is the calculation)


Then in a new column enter something like this in the 'field' line
ExtendedPrice: ([Quantity]*[CasePrice])-[AmountDiscounted]


Then you should be able to 'Sum' the ExtendedPrice on your form.

On your main form add a text box called say, 'TotalSum'
In the 'Control Source' enter this syntax
=Sum([ExtendedPrice])

Good Luck
Tom

 

[Stephanie T.]

Tom,

OK, I wrote the Select Query, but now I don't quite understand what I need to have as the control source for my Amount Discounted field, and my ExtendedPrice field. These are both line items fields, then the Sum is a sum total for the subform. But I would actually rather the Sum be on the Main form instead.

Thanks for you assistance, I know we're on the right track.

Stephanie

 

[wizcow]

Stephanie

On your subform;
In your control source for 'AmountDiscounted' add AmountDiscounted

Or you can click into the control source line and a pull down arrow will appear. If you pull down the list it will show the names of the fields in your query. 'AmountDiscounted' should be there.

The same goes for 'ExtendedPrice'



Now....

On your MAIN form add a text box called say, 'TotalSum'
In the 'Control Source' enter this syntax
=Sum([ExtendedPrice])

I hope this is making sense!
Tom

 

[Stephanie T.]

Tom,

OK, this is not working. I have two concerns, first when I add AmountDiscounted & ExtendedPrice (respectively) to their control fields, they become the control field and I loose the equation that was driving those fields. If I put it before the = sign, I get “Error” in the field. So, I suspect (concern #2) that I do not have the select query hooked up to the form properly. Here is the query I have:

All from the table tblSalesDetailSynch (that is the table that drives the Subform)

1st column
Quantity
tblSalesDetailSynch

2nd column
CasePrice
tblSalesDetailSynch

3rd column
Discount
tblSalesDetailSynch

4th column
AmountDiscounted: ([Quantity]*[CasePrice])*[Discount] (this is in the field only)

5th column
ExtendedPrice: ([Quantity]*[CasePrice])-[AmountDiscounted] (this is in the field only)

I know this can be done. Have I also told you that I am working in Access 97? Could make a difference.

Essentially, I am at the same point I was. All of the first round of equations work just fine, but I cannot seem to Sum any of the text boxes with equations already in them. I built the query above, but do not think that I have linked it or used it correctly.

Thanks for your continued help,
Stephanie