Formulas - Before or After importing data to Access?

[learningaccess]

Hi - I'm playing around trying to determine the best way to design a database
before I actully imput all this data again. (Didn't do it the best way first
time around. Some data was formatted incorrectly for calcs.)

The first way I tried expressions in Access which was a flop due to
formatting. Then I tried doing the formulas first in Excel and having these
as columns added to my Access Database. This way I found I had to delete a
lot of data with a #Div/0! result to avoid Access errors. The data was
useless anyway as some numbers divisible by zero are irrelevent for my
purpose. But it may have been useful to have the rest of the data in the
row available in Access for reference even thought the calculation did not
make sense.

So, in your opinion, is it best to do calcs in Excel first and delete all
undefined results befroe importing to Access? Or, is it better to import all
data into Access and create expressions even though some will have a zero in
the denominator?

Also, does the YEARFRAC function work in Access for getting the number of
days between two dates?

Please let me know what is the best approach and your reason for thinking so.

Chris

 

[jahoobob via AccessMonster.com]

IMNSHO you should import the raw data into Access and let it do the
calculations in queries, forms, or reports. Notice I didn't mention tables
for calculations
The function in Access that find the difference between tow dates is DateDiff.

 

[John W. Vinson]

So, in your opinion, is it best to do calcs in Excel first and delete all
undefined results befroe importing to Access? Or, is it better to import all
data into Access and create expressions even though some will have a zero in
the denominator?

Also, does the YEARFRAC function work in Access for getting the number of
days between two dates?

No. DateDiff() does, however.

Please let me know what is the best approach and your reason for thinking so.

Treat Excel as a spreadsheet... and Access as a relational database. You can
drive nails with a crescent wrench, but that doesn't make it a hammer!

Don't store ANY calculated values in a table (whether calculated in Excel or
in Access), unless you have a *very* good, demonstrated reason why you must.

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 in the control source of a Form or a Report
textbox.


John W. Vinson [MVP]

 

[chrissyb]

Thanks... Is it a good idea to filter the #Div/0! values and delete them
from the query so I won't have a problem getting max, min, avg, etc or calcs
that involve zero in the denominator?

 

[John W. Vinson]

Thanks... Is it a good idea to filter the #Div/0! values and delete them
from the query so I won't have a problem getting max, min, avg, etc or calcs
that involve zero in the denominator?

I have no idea, since I don't know anything about your data.

Again:

If you're talking about storing the result of a division in your table...
DON'T store the result of the division in your table.

Instead, store the numerator; store the denominator; and do the calculation
WHEN NEEDED in a Query or in the control source of a textbox on a form or
report. Deal with any errors *at that point* (for example, by including a
criterion requiring that the denominator be nonzero).

John W. Vinson [MVP]