Lookup & Calculations help please!

[gjoneshtfc]

Hello

I am new to databases and am trying to set up an invoicing system for
my company. I have a form set up which looks up an item list from a
table and enters the unit price associated from the same table. I also
lookup the customer details and staff details. All these lookups worked
great until i attempted to do some calculations!

I put a calculation in the total field in my form to multiply the unit
cost by the number of units. Since then i am unable to use the drop
down item list unless i leave the record and come back to it - after
which it works fine. Has anybody got any ideas as to why this doesnt
work?

Also, I have just realised that the calcualtions i do in a form do not
get stored in the table. This means my reports that i am going to
create next will not work. Can anybody tell me how to make the results
of the calculations get stored in the table please (ie: the
calculations update the table)?

Thank you for all your help - it is much appreciated.
Gareth Jones

 

[Jeff Boyce]

Gareth

Access tables store data (and rarely need to store calculations). Forms
(and reports) display data.

You can do the calculation(s) as part of a query, then use that query to
display data in a form. You could use that (or another) query to gather the
data together for a report as well.

 

[gjoneshtfc]

Hi Jeff,

Thanks for the reply. Will the result of the calculation done in the
query be stored in the table then or just displayed? Maybe its me but
it seems illogical not to be able to do calculations and store them in
a table?

Thanks again,
Gareth

 

[Dean]

The results will just be displayed. You usually don't want to store the
results because then you have to replace them when the inputs change, unless
you need to keep an audit trail and that is much more complicated. The same
concept is used in Excel, it shows results, but in each cell there is really
a formula, if you change the display then the formula is shown.

 

[gjoneshtfc]

Thanks Dean,

Here is my scenario: I want to update the number of units for each item
so that it recalculates the total but i want this stored in the table.
Are you saying that the only way to get the total in the table is to
manually do the calculation and type it in myself without a formula?
I'm not against doing this because its how i currently do my invoices
but it does seem strange that calculation results cannot be stored.
Again, this might just be me as i am a very new user to access and so
probably don't understand its main existence!

Gareth

 

[Dean]

Why do you want the results stored in the table?

 

[Douglas J Steele]

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

 

[Dean]

You should take a look at the sample databases that come with Access. There
are a lot of good ideas and demonstrations of how to this.
Create a new database from the templates that came with Access and then
explore the results that the wizards produce. You will learn alot and may
even find some examples that you can use directly. The Order Entry is
probably a good place for you to start.

 

[Guest]

Gareth,

In paraphrasing a friend who stayed in posh resorts as a guest of friends,
"Who needs ownership when you have ACCESS?"

Calculated data is the same way--if you *define* a calculated field in a
query, you have access to this value anytime you run the query, or anytime
you display a form or print a report based on the query. You would need VBA
code, in fact, to store a calculated value, since a form control's
ControlSource can *either* be the name of a field *or* an expression, but not
both. If the elements of the expression are then changed outside the sphere
of the form where the code stores the calculation, OR if the programmer did
not consider all the possible circumstances under which this might change,
then the expression IS WRONG.

The calculated field in the query will always use correct data, and only
offers advantages.

Sprinks

 

[John Vinson]

Hi Jeff,

Thanks for the reply. Will the result of the calculation done in the
query be stored in the table then or just displayed? Maybe its me but
it seems illogical not to be able to do calculations and store them in
a table?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]

 

[gjoneshtfc]

Hello all!

Thanks for all the replies. I have just been teaching so have only just
got them!

In answer to the question why i want to store them: I want them to be
viewable in the invoices table itself as i want to print the table out
in the future. A friend of mine is also doing a database and she
requires some calculation results to be stored in the table so that she
can create a new form which totals up each total (ie: each record has a
total, and she needs a total of the totals in a new form) so i am
actually writing this question for us both.

From everybodies replies so far i get the impression that the only

thing i can do is create a query and go from there as i assume the
report i create from the query will contain the calculated values. I
looked at the examples in access previously but as a complete newby got
very confused! Will a query work for my friend too?

I have not setup a query before so any pointers will be greatly
appreciated (in laymans terms please!)

Thanks and kind regards,
Gareth Jones

 

[gjoneshtfc]

Hello again,

I have just spoke to my friend again who has made their problem a bit
clearer:

She needs to do a sum calculation on a field that is a calculation! In
easier terms:

She has a field in each record that is a "total" calculation of things
in that record. She then needs to sum the "total" from each record to
create the "overall total". However, when she sums the "totals" she is
looking in the table - and there is no data in the table so the overall
total is zero.

If anyone has any ideas please share them!

Thanks again, Gareth

 

[gjoneshtfc]

Hello Sprinks,

I have attempted to create a query but now i am confused as to where to
put the formula? And what type of query should i have? I currently have
the default "select query" type.

Thanks for your help!
Gareth

 

[John Vinson]

Hello again,

I have just spoke to my friend again who has made their problem a bit
clearer:

She needs to do a sum calculation on a field that is a calculation! In
easier terms:

She has a field in each record that is a "total" calculation of things
in that record. She then needs to sum the "total" from each record to
create the "overall total". However, when she sums the "totals" she is
looking in the table - and there is no data in the table so the overall
total is zero.

If anyone has any ideas please share them!

You can do a Sum *on a calculated field in a Query*. If need be, you
can base another query on a query - just treat the first query as if
it were a table.

To see record totals and overall totals together, don't use a query
datasheet though. It's too limited. Use a Form or Report (which is
surely the ultimate goal anyway). You can use the Report's Sorting and
Grouping dialog to provide multiple levels of subtotals. This report
can be based on a table or on a query. On a Form, you can put a
textbox on the form footer with a control source

=Sum([fieldname])

to sum the values in [fieldname] - and that can be a "real" or a
calculated field.

If you're making the common assumption that data must be in a Table
field in order to print it on a report, or to do additional totals,
bear in mind that this is an incorrect assumption!

John W. Vinson[MVP]

 

[Jeff Boyce]

I, too, have been away at work, just getting back now.

The consensus in this thread (and in the tablesdbdesign newsgroup) is that
you DON'T store calculated values. Do the calculation(s) in queries, and
use the queries for forms and reports.