Numbers in Access

[Alec]

Hi
Apologies if this is a dumb question, but I am a newbie to Access (I am
a spreadsheet man!).

What is the best way to set up Access tables to get good statistical
data management (like a spreadsheet but obviously with the integrity
provided by a dtb)?

I don't need flashy stuff, just good old sums and division etc etc.

How do I set up a table column so that it calculates from the numbers in
other columns?

Thanks for your help in advance!
Cheers
Alec

 

[fredg]

Hi
Apologies if this is a dumb question, but I am a newbie to Access (I am
a spreadsheet man!).

What is the best way to set up Access tables to get good statistical
data management (like a spreadsheet but obviously with the integrity
provided by a dtb)?

I don't need flashy stuff, just good old sums and division etc etc.

How do I set up a table column so that it calculates from the numbers in
other columns?

Thanks for your help in advance!
Cheers
Alec

You don't (and can't).
Access tables are used to store data, not perform mathematical (or any
other) data manipulation.
Store the various pieces of the data you will need, then using a
query, or directly in a form or report, do whatever calculating is
necessary.
For example, to compute an objects area, store the width and length of
the object in your table, then in a query use
Area:[ObjectLength] * [ObjectWidth]

The result need not be saved. When you need the result again,
re-calculate it.

 

[Alec]

Hi
Apologies if this is a dumb question, but I am a newbie to Access (I am
a spreadsheet man!).

What is the best way to set up Access tables to get good statistical
data management (like a spreadsheet but obviously with the integrity
provided by a dtb)?

I don't need flashy stuff, just good old sums and division etc etc.

How do I set up a table column so that it calculates from the numbers in
other columns?

Thanks for your help in advance!
Cheers
Alec

You don't (and can't).
Access tables are used to store data, not perform mathematical (or any
other) data manipulation.
Store the various pieces of the data you will need, then using a
query, or directly in a form or report, do whatever calculating is
necessary.
For example, to compute an objects area, store the width and length of
the object in your table, then in a query use
Area:[ObjectLength] * [ObjectWidth]

The result need not be saved. When you need the result again,
re-calculate it.

OK, so I should put all the base data into standard tables, and then do
whatever calculations needed in the query/report process. By the way,
what's the difference between queries and reports?

Is it possible to save calculations or calculation results to avoid
having to redo them all the time? eg can I create a query that results
in certain summary data, and then use the result in another calculation
in another query?

Is it possible to export data to excel, do the sums and then re-import
to another table? I assume it is possible, but it is advisable?

Thanks for your advice.
Cheers
Alec

 

[Douglas J. Steele]

Answers in-line.

Depending on the nature of the Statistical calculations you're trying to do,
you might be interested in Total Access Statistics, from FMS.

See http://www.fmsinc.com/products/statistics/index.html for details.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi
Apologies if this is a dumb question, but I am a newbie to Access (I am a
spreadsheet man!).

What is the best way to set up Access tables to get good statistical data
management (like a spreadsheet but obviously with the integrity provided
by a dtb)?

I don't need flashy stuff, just good old sums and division etc etc.

How do I set up a table column so that it calculates from the numbers in
other columns?

Thanks for your help in advance!
Cheers
Alec

You don't (and can't).
Access tables are used to store data, not perform mathematical (or any
other) data manipulation.
Store the various pieces of the data you will need, then using a
query, or directly in a form or report, do whatever calculating is
necessary.
For example, to compute an objects area, store the width and length of
the object in your table, then in a query use
Area:[ObjectLength] * [ObjectWidth]

The result need not be saved. When you need the result again,
re-calculate it.

OK, so I should put all the base data into standard tables, and then do
whatever calculations needed in the query/report process. By the way,
what's the difference between queries and reports?

Queries retrieve and (optionally) perform calculations on data. Reports
present the data in a printable format.

You've missed another option in there: Forms. Forms are what you use to
interact with the data (you should never work directly with tables: you
should always use a form)

Is it possible to save calculations or calculation results to avoid having
to redo them all the time? eg can I create a query that results in certain
summary data, and then use the result in another calculation in another
query?

It's possible, but definitely not recommended.

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."

Is it possible to export data to excel, do the sums and then re-import to
another table? I assume it is possible, but it is advisable?

Again, it's possible, but definitely not recommended. As already stated, you
shouldn't be storing calculations.

 

[Joseph Meehan]

Hi
Apologies if this is a dumb question, but I am a newbie to Access
(I am a spreadsheet man!).

What is the best way to set up Access tables to get good statistical
data management (like a spreadsheet but obviously with the integrity
provided by a dtb)?

I don't need flashy stuff, just good old sums and division etc etc.

How do I set up a table column so that it calculates from the
numbers in other columns?

Thanks for your help in advance!
Cheers
Alec

You don't (and can't).
Access tables are used to store data, not perform mathematical (or
any other) data manipulation.
Store the various pieces of the data you will need, then using a
query, or directly in a form or report, do whatever calculating is
necessary.
For example, to compute an objects area, store the width and length
of the object in your table, then in a query use
Area:[ObjectLength] * [ObjectWidth]

The result need not be saved. When you need the result again,
re-calculate it.

OK, so I should put all the base data into standard tables, and then
do whatever calculations needed in the query/report process. By the
way, what's the difference between queries and reports?

There are many differences, but reports are designed to DISPLAY or PRINT
results, often of a query.

Queries I would say are designed to organize and modify data.

Is it possible to save calculations or calculation results to avoid
having to redo them all the time? eg can I create a query that results
in certain summary data, and then use the result in another
calculation in another query?

Yes it is possible, but not generally a good idea. Access can
re-compute it faster than it can retrieve it. You can use one query as the
data source for another.

The only time you might want to save the result is if you need the
results as of NOW and there is the chance that some of the data would
change. Normally you want to recalculate the results if the data were to
change.

Is it possible to export data to excel, do the sums and then re-import
to another table? I assume it is possible, but it is advisable?

It is possible, but I can't think of a good reason to do it.

BTW Excel also re-calculates most of the results every time you run it.
Assuming a cell is is displaying the result of a formula Excel is
re-computing that number every time you display it.