Calculations

[Guest]

I have been discussing my problem with a few of you, but I thought I would
throw this out there to see if anyone has any suggestions. I am working with
Access 2000-2003. I am building an input form and a portion of the scenerio
is as follows:

On a daily basis the individual who will use this form may have numerous
entries that will make up each record in the table. Each record requires
that she generate a new form. There are six (6) textboxes on the input form
that require some calculations. 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 on one form, 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 final
result of the calculation. This is the same case for the other five fields
that 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. So 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 again, I do not want to save the numbers 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 a better understanding of what I'm trying to
accomplish.

Can the formulas be embedded on the input form (but not saved in the table)
and then have only the results be saved to a table? I was told by someone
earlier that the calculations should be done in the report. But the report
is generated based on the information from the table, so how do I get the
results into the table without using a calculation on the input form? I
think I am probably making this harder than it is, but I'm just not
understanding what I have to do to get the results they need. I would
appreciate any help I can get to figure this out.

Thank you,

Owl Lover

 

[Guest]

Wasn't it you I helped before? I didn't notice the username.

 

[Guest]

As far as I am aware it isn't possible to insert numbers into unbound text
boxes because they are floating numbers (ie they aren't bound to a field that
Access recognises). As a consequence, you can't do any calculations.

That is if I understand your problem correctly!

 

[Guest]

I will add that you may (which I emphasise) have a design issue here.

 

[Arvin Meyer [MVP]]

All of those calculations can easily be regenerated. Your only concern might
be the number of records. Since there will be a maximum of only 150,000
records over a 5 year period, I suggest you just calculate each time. It
should only take a few seconds. I am doing complex calculations on over
500,000 records daily, and the processing time averages 9 seconds.

 

[Guest]

It may have been. I just reposted my question because I didn't think I made
myself very clear before. I'm not sure what to do now. I've used formulas
in Excel, but I'm not at all familiar with Access when it comes to something
like this. Is there any other way I can get the results I'm looking for?

 

[Arvin Meyer [MVP]]

Sorry, but that is incorrect. It is quite simple to insert numbers or
calculations into either bound or unbound textboxes, or even save the
results of calculations, with or without a form that's bound to a table. The
only calculations that you can't do are those involving Nulls or divisions
by 0. Even those instances can be done with some creative error handling.

 

[Guest]

I tried putting numbers into unbound text boxes and doing a simple
calculation but it didn't work.

I got "#name" or "#error"

So how is it done then?

--
www.ae911truth.org

Sorry, but that is incorrect. It is quite simple to insert numbers or
calculations into either bound or unbound textboxes, or even save the
results of calculations, with or without a form that's bound to a table. The
only calculations that you can't do are those involving Nulls or divisions
by 0. Even those instances can be done with some creative error handling.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

I know this is going to sound stupid, but can you please elaborate on what
you just told me. How does the data that I will use to recalculate the
information in the report get into the appropriate table in the first place?
I'm not sure I'm wording this right, but I'm really (obviously) confused. In
other words, on my input form I have 6 fields that total 18, and I want to
use a calculation somewhere that sums up those 6 fields and puts that 18 into
the total field. If I'm understanding everyone correctly I have to enter
that formula every time in order to get the 18 (or whatever the total is),
but I can't save the 18 (or total) to my table. Is that correct? I'm really
hoping I'm just not understanding this because if I am correct I have no idea
how to accomplish the end result on the report.

 

[Guest]

I'm beginning to think I have more than a design issue! At this point I'm
totally lost and I'm not sure how to get back on track!

 

[Arvin Meyer [MVP]]

In your code in the AfterUpdate event for the bound or unbound text boxes,
you do the calculation and "push" the result into the final box. Consider
the following code in 3 textboxes Text1, Text2, and the calculated result
Text3:

Sub Text1_AfterUpdate()
If IsNumeric(Me.Text1) and IsNumeric(Me.Text2) Then
If Len(Me.Text2) > 0 Then
Me.Text3 = Me.Text1 * Me.Text2
End If
End If
End Sub

Do the same for Text2

If Text3 is a bound textbox, the result will be saved to the table. If not
it won't without additional code.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

As a recordsource for your report, you use a query and add an alias column.
That is a calculated column that allows you to input or calculate each row
of data in the QBE grid (query design window) the column might look
something like:

Total: [Field1]+[Field2]+[Field3]+[Field4]+[Field5]+[Field6]

Now in your report, use your new Total field to dispaly the calculation. If
you build a group footer, you can display a Grand Total for the Total
column. so you can group by Months, Quarters, and Years and have a Report
Footer that totals everything in the query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[John W. Vinson]

Can the formulas be embedded on the input form (but not saved in the table)
and then have only the results be saved to a table? I was told by someone
earlier that the calculations should be done in the report. But the report
is generated based on the information from the table, so how do I get the
results into the table without using a calculation on the input form? I
think I am probably making this harder than it is, but I'm just not
understanding what I have to do to get the results they need. I would
appreciate any help I can get to figure this out.

What we've been suggesting is that - unless the user needs to *see* the
calculations on the form - that you don't do the calculations on the form AT
ALL, and that you don't store the sums in any table at all.

The user has to enter the data on which the calculations are based in any
case. *Just store that data in your table*. If you do the calculations on the
form, do them just for the purpose of displaying the result, not for storing
it.

Base the report, not on a table of stored calculated values, but on a Query
which uses the raw data in the table as the basis of its calculations.

Yes. You're making it harder than it needs to be.

John W. Vinson [MVP]

 

[Guest]

Be patient with me John, I'm still learning!! Now I think I get it! Thanks
for your help, and knowing the job I've got before me, it won't be the last
time I ask for your help!

Owl Lover

 

[Guest]

Now I get it! And like I told John, I'm sure this won't be the last time I
ask all of you for help. Please be patient with me - I'm still learning!!
Thanks for your help.

Owl Lover

As a recordsource for your report, you use a query and add an alias column.
That is a calculated column that allows you to input or calculate each row
of data in the QBE grid (query design window) the column might look
something like:

Total: [Field1]+[Field2]+[Field3]+[Field4]+[Field5]+[Field6]

Now in your report, use your new Total field to dispaly the calculation. If
you build a group footer, you can display a Grand Total for the Total
column. so you can group by Months, Quarters, and Years and have a Report
Footer that totals everything in the query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I know this is going to sound stupid, but can you please elaborate on what
you just told me. How does the data that I will use to recalculate the
information in the report get into the appropriate table in the first
place?
I'm not sure I'm wording this right, but I'm really (obviously) confused.
In
other words, on my input form I have 6 fields that total 18, and I want to
use a calculation somewhere that sums up those 6 fields and puts that 18
into
the total field. If I'm understanding everyone correctly I have to enter
that formula every time in order to get the 18 (or whatever the total is),
but I can't save the 18 (or total) to my table. Is that correct? I'm
really
hoping I'm just not understanding this because if I am correct I have no
idea
how to accomplish the end result on the report.