display on a form the sum of a field

[Jen0dorf]

Hi



want to display on a form the totals of one filed/columm in one of the tables the form is based on.

I added a text box to the table and in proporties----data----control source--opened expression builder, selecedt my table under expression elements then under catagoroes selected the field - and from there I am lost as under expression values I see nothing I can relate to Sumtried to input an expression for the sum of all currency values in one columm


can some one assist

thanks

Ian

[JoeM]

You need to put the field in a Form Footer.
See: http://www.599cd.com/tips/access/form-footer-total-sum/

[nick404]

try Sum(YourField) ?

Can you give us more background on what you are trying to do as a whole? In my mind I don't see why one would need to sum an entire field in a form in one textbox unless it was a continuous form?

[Jen0dorf]

Hi

sorry possible was not sufficiently clear

I have a simple database for membership of a club so I haveone table for members & addresses, another with their phone numbers & a third with their subscription payments.

I created a form using the form wizard which displays it all perfectly.

I just want a field on the form to display the total amount paid in subscriptions

thanks

Ian

[nick404]

Sounds like a report is more suitable, if this I something that you / management is viewing. Or are you actively entering new data in?

I would go with the report. Then you can right click the subscription payments field, 'Total On' -> 'Sum' and it will do it for you

[Jen0dorf]

Hi

thanks a lot - the tutorial really helped clarify the problem -which is down to my poor explanation!!!;-)

The form is created using two tables

the first one show the name/address and is one customer per screen

Under that is a tabular table in which all the subscriptions for that individual member are shown.

I want to display the total for all members on that form not just the one on that page

thanks

Ian

[nick404]

Did you try out JoeM's link and put it in the footer?

[Jen0dorf]

Hi

yes I did but it did not seem to work probably my fault

The form contains the data from the main table, access has put in the other table data as a tabular sub form and this sub form does not appear to have a footer

I'll try again but I'm beginning to wonder If I've just approached this from totaly the wrong angle.

Putting it in it's simplest way I want a running total of all moneys paid displayed on the screen all the time.

thanks

Ian

[June7]

If you want to sum records of subform, options:

1. subform set as datasheet - in form view click the subform then click Totals from the ribbon to open a Totals row in the subform

2. subform set as continuous and controls arranged to look like datasheet, textbox in footer section

3. subform set as datasheet with textbox in footer section (it will not display in form view), textbox on main form references the invisible textbox of subform

Note that the sum will not recalculate for last record until leave that field and/or move to another record

[Jen0dorf]

THanks a lot - solved the issue and I now understand a bit more!!!

Couldn't see where in properties the form was listed as Datasheet or continuous but method 1 worked

cheers

Ian

[June7]

Look at form Default View property.

Is your subform container SourceObject a table or query or form?

[Jen0dorf]

Hi

I realise I'm going to seem very slow but

If I open the form in design view

The "top half" shows one record

within the bottom half is a table laid out "like Excel" which only shows the records linked via the relationship to the client in the main form.

If I click on the "sub form the property sheet comes up as follows

Property sheet
selection type: Subform/Subreport


the name is child 33

and then all the properties are below

But I can see no option for default view.

The subform/report is based on a table.

If I highlight the container form the default view is listed as split form.

I will admit I created this using the form wizard and let Access do all the work.

Is this why despite setting headers and footers to be shown in the subform they are not shown

thanks for the help

regards

Ian

[June7]

Because you have only selected the subform/subreport container control. A container control can have a table/query/form/report as SourceObject. Look at the container SourceObject property. If the SourceObject is a form or report, click inside the container to select the object. Or go to the navigation pane and open it directly (but not if already open on main report). Forms and reports have DefaultView property.

Report headers and footers do not show in subform/subreport.

I avoid the wizards.

[Jen0dorf]

Really appreciate the quick response

As you clearly suspected the source object is a table.

More things to research and learn!

In the future I will avoid the wizards

THanks Again

Ian