Calculations in textboxes

[Owl Lover]

I am working in Access 2000-2003, and I have some textboxes in my form that
require calculations such as total quantities, total hours, total charges,
etc. Can
anyone tell me what would be the easiest way to accomplish these totals?
Also, the resulting total has to be saved to a table for future use in
reports. It's
been so many years since I've worked in Access, I feel like I'm having to
learn everything over again. Any help would be very much appreciated.

Owl Lover

 

[molsonexpert]

calculations of two or more textboxes can be made in the control source of
an unbound text box. it sounds like you require a running total, which can
be accomplished in an unbound control on the form's footer. go to the access
help and type total in the index.

saving the total in a table is a bad idea, reasons of which have been cited
in this newsgroup almost on a daily basis. do you really need to do this?
it's much easier (and safer) to perform the calculation directly on a form
or report, instead of saving it to a table's field.

 

[Owl Lover]

Thank you for your response. Unfortunately, the company I am building this
database for requires the totals to be used in various reports they do on a
weekly, monthly, quarterly and yearly basis. This whole database was
originally in Lotus Approach, and they have recently moved into Access and
hired me to recreate the Approach database in Access. Needless to say, this
has been somewhat of a nightmare for me since I knew nothing about Approach,
and it has been 5 years since I have worked with Access. I'm trying very
hard to keep everything simple, but things like this keep popping up! When I
exported the tables into Excel from Approach, they came into Excel as
calculations. Before I could import them into Access tables I had to take
out the calculations, which left the totals only, which was fine. I thought
there would be no problem setting the new tables up with the future totals in
Access. Do you have any suggestions as to what I can do to accomplish this
monumental task!!? Any help you can give me will be much appreciated.

Owl Lover

 

[John W. Vinson]

Also, the resulting total has to be saved to a table for future use in
reports.

No. It does NOT need to be saved in order to generate future reports.

The future reports can and should simply redo the calculation.

John W. Vinson [MVP]

 

[molsonexpert]

If you must, try something like this:

http://support.microsoft.com/kb/292042/en-us

I still believe that if you store in a table all of the numbers that make up
a calculation, then storing the actual calculation is unnecessary.

It also sounds like you're having some difficulty planning your table
structure. If so, post back with some specifics.

 

[Owl Lover]

I'm having a problem understanding how that will be possible when they will
have generated dozens and dozens of daily reports. However, since I am still
working on the input form, I will hold off with this until I get to it. When
I start creating the reports I will post my question and hopefully you can
guide me through. My hope is that by the time I reach that point I will have
a better understanding of what you just told me and will be able to do it on
my own. I really appreciate all the help I have been given so far.

Owl Lover

 

[Owl Lover]

You are correct - I do not need to store the calculations, just the results.
But I still need to figure out how to add the formula to the textbox to get
those results, which is where my problem is. Am I making myself clear? If
not, please let me know and I will try to reword it.

Thanks,

Owl Lover

 

[John W. Vinson]

I'm having a problem understanding how that will be possible when they will
have generated dozens and dozens of daily reports. However, since I am still
working on the input form, I will hold off with this until I get to it. When
I start creating the reports I will post my question and hopefully you can
guide me through. My hope is that by the time I reach that point I will have
a better understanding of what you just told me and will be able to do it on
my own. I really appreciate all the help I have been given so far.

My point is that if you can do a calculation in the data entry form (in order
to store the result), then it is JUST AS EASY to do the calculation in the
Report. A Report textbox can be based on an expression; you can do sums in the
Footer of a report; you can base the report on a query which includes
calculated fields. When you do so, the sums and other calculated fields in the
report will faithfully display the calculation based on the current data in
the table, as of the time when you run the report. If you run the report
tomorrow, it will show the same sum - unless the underlying data has changed,
in which case it will show the new, correct sum.

John W. Vinson [MVP]

 

[Owl Lover]

I have been in touch with the company I am working for and let me see if I
can explain what they want from this input form. Daily they have numerous
entries that will make up each record in the table. Six of those entries on
that input form are results from calculated fields. For example, one of the
fields is "Return Qty Total", which is the total of all the returns for that
entry. There can be up to 6 returns, and the total of those returns will be
the combined total of the "Return Qty Total" field. I do not need to save
the calculation in the "Return Qty Total" field, but I do need to save the
result of the calculation. This is the same case for the other five fields
that will have calculations in them. There may be 20 different input forms
generated for that day, and the results of all 20 will need to be saved. At
the end of the week they will run a report based on all the data that has
been saved for that week, using all the totals from the forms. Basically,
each form is an individual record, and each record will be used to generate
one report with the combined totals of all the records. Reports will also be
generated monthly, quarterly and yearly using all the totals generated for
each record. So, I do not want to save the data used in the calculations,
but I do want to save the results of the calculations in that record's
"Return Qty" field. Another field is called "Rework Charge", and the results
of the calculation in that field are determined by taking the total of the
"Rework Qty" fields (6), and multiplying that total by $75. Again, I only
want to save the result in the "Rework Charge" field on the table. Reports
will be generated using each of the "Rework Charges" over the given period of
time, so each records total has to be saved in the table for future use.
Hopefully this gives you a better understanding of what I'm trying to
accomplish and not confused you. Thank you in advance for your help.

Owl Lover