Trying to calculate a Sum

[richard]

I have a table with 5 fields into which the user will enter numbers,
which must add up to 100%. Say the fields are Toyotas, Missans, Mazdas,
Hondas and Other. I am having trouble writing a query using Sum which
will add these together and place the result in another field called
"total" which I will then place on a form so the user can check it
contains value 100 for each record. Any help appreciated, thanks.

[Geoff G]

If your fields are called Toyota, Nissan etc

Use an unbound field called Total

In its record source write

=Toyota+Nissan+Mazda+Honda+Other

then as you update the fields Honda, Toyota etc the total will change
showing the current sum.

You could use a VB procedure to do the same which would allow you to
test to see if the answer is less than a hundred or more than a hundred
and pop up a message box with an appropriate warning.

[richard]

In article <11**********************@g47g2000cwa.googlegroups .com>,
ge***@sunny-side.ws opined thusly:

If your fields are called Toyota, Nissan etc

Use an unbound field called Total

In its record source write

=Toyota+Nissan+Mazda+Honda+Other

Sorry, I'm not that great at this yet...where do I create this? I have
succeeded in creating an unbound text frame in the form design view but I
can't see where it's "record source" is. I did succeed in getting it to echo
the value from one of the fields but when I used the expression builder to
sum them, it appended the digits instead

ie. 10, 30, 20, 5, 35 became 103020535 instead of 100.

thanks.

[Tommy T-Bag]

Are these car names the columns in a database table
or just text boxes on a form?

[richard]

In article <11**********************@g43g2000cwa.googlegroups .com>,
th***********@gmail.com opined thusly:

Are these car names the columns in a database table
or just text boxes on a form?

They are each fields in a table. I worked my way through the Access Help
info on summing, and followed the instructions no problem to create the
unbound text box and then fill it with the expression:

=[toyota]+[mazda]+[nissan]+[honda]+[other]

but still it appends the percentages into a 10 digit integer instead of
adding them together!

[Tommy T-Bag]

What are the data types of those columns?

[Mondrogan]

Assuming you have 5 fields on a form, you should be able to force a constant
sum of 100% across the fields, making user verification unnecessary.

For example, start with 20% in each of the 5 fields. When one of the fields
changes value, distribute 100 - [sum of modified fields] evenly across the
remaining unmodified fields. So if the user changes the Toyota field to 30%,
mark the Toyota field modified and distribute (100-30)/4 to each of the 4
remaining unmodified fields. And if the user then changes the Honda field to
25%, distribute (100-55)/3 to each of the 3 remaining unmodified fields.
Etc.

I suppose you will need some global variables (maybe an array?) to keep
track of which fields have been modified/marked; and you will probably need
a Reset button to reset the fields to the default 20% and unmark all fields,
if the user wants to start over; and you probably want to round to 2
decimals to force the math to be exactly right.

Ok, maybe the code gets a little complicated. Should be possible, however.

"richard" <no****@myplace.net> wrote in message
news:xJ******************@news-server.bigpond.net.au...

I have a table with 5 fields into which the user will enter numbers,
which must add up to 100%. Say the fields are Toyotas, Missans, Mazdas,
Hondas and Other. I am having trouble writing a query using Sum which
will add these together and place the result in another field called
"total" which I will then place on a form so the user can check it
contains value 100 for each record. Any help appreciated, thanks.

[Mondrogan]

Maybe my idea is not so workable, after all. I'm concerned about the
possibility of causing an infinite loop which might happen if a user change
to one form field triggers code in another form field which in turn
retriggers the code in the original field, etc. Ugh.

"Mondrogan" <mo*******@comcast.net> wrote in message
news:qb********************@comcast.com...

Assuming you have 5 fields on a form, you should be able to force a
constant sum of 100% across the fields, making user verification
unnecessary.

For example, start with 20% in each of the 5 fields. When one of the
fields changes value, distribute 100 - [sum of modified fields] evenly
across the remaining unmodified fields. So if the user changes the Toyota
field to 30%, mark the Toyota field modified and distribute (100-30)/4 to
each of the 4 remaining unmodified fields. And if the user then changes
the Honda field to 25%, distribute (100-55)/3 to each of the 3 remaining
unmodified fields. Etc.

I suppose you will need some global variables (maybe an array?) to keep
track of which fields have been modified/marked; and you will probably
need a Reset button to reset the fields to the default 20% and unmark all
fields, if the user wants to start over; and you probably want to round to
2 decimals to force the math to be exactly right.

Ok, maybe the code gets a little complicated. Should be possible, however.

"richard" <no****@myplace.net> wrote in message
news:xJ******************@news-server.bigpond.net.au...

I have a table with 5 fields into which the user will enter numbers,
which must add up to 100%. Say the fields are Toyotas, Missans, Mazdas,
Hondas and Other. I am having trouble writing a query using Sum which
will add these together and place the result in another field called
"total" which I will then place on a form so the user can check it
contains value 100 for each record. Any help appreciated, thanks.