Getting the sum of several fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

oliver_optic - 02 May 2007 23:30 GMT

I created a field called cash in a MS Access form, I want the totals
for these fields 100 50 20 10 5 to be placed in that cash field. In
the control source for the cash field I am using
=Sum([100]+[50]+[20]+[10]+[5]) and thought that would work but it
does
not. I can do a sum of two fields, but anything larger does not work.
What am I missing

Dave

Reply to this Message

Jeff Boyce - 03 May 2007 00:17 GMT

I'm not entirely clear on the data structure underlying your form. Are you
saying you have multiple fields ([100], [50], ...) and want to total across
them? If so, you've just described ... a spreadsheet! Is there a reason
you aren't doing this in Excel?

Access is a relational database, and you won't get the best use of its
features and functions if you try to feed it 'sheet data. If my above
interpretation is correct, and if you don't want to use Excel, you'll need
to brush up on "normalization" if you want to get the best out of Access.

In a well-normalized Access table, each of those "fields" you described
would be a category in a single field, plus one more for whatever you were
entering in those. With this design, Access can provide a simple Totals
query, and if you ever need to add more categories, you WON'T have to modify
your table structure, your queries, your forms, your reports, your code, ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

>I created a field called cash in a MS Access form, I want the totals
> for these fields 100 50 20 10 5 to be placed in that cash field. In
[quoted text clipped - 5 lines]
>
> Dave

Reply to this Message

Damon Heron - 03 May 2007 00:20 GMT

Yikes! I really don't like your naming conventions. Add something to denote
they represent a control on your form.
In my example, I added ctl to your names:).
Anyway, on the Enter event of your field Cash, put:
Private Sub Cash_Enter()
Me.Cash = ([Ctl100] +[Ctl50] + [Ctl20] + [Ctl10] +[Ctl5])
End Sub
When you tab into the cash field it will be updated with the sum of your
boxes.
Make sure your controls are formatted as numbers.

HTH
Damon

>I created a field called cash in a MS Access form, I want the totals
> for these fields 100 50 20 10 5 to be placed in that cash field. In
[quoted text clipped - 5 lines]
>
> Dave

Reply to this Message