formulas in Access?

[Guest]

Hi,
right now i work on data using exculsively excel to preform my calculations.
Right now i have gotten to the point that i have many many huge files with
many many formulas. I am considering swithcing to access. I was wondering can
i use similar formulas and calculations used in exel in access?

thanx for the help

 

[Joseph Meehan]

Hi,
right now i work on data using exculsively excel to preform my
calculations. Right now i have gotten to the point that i have many
many huge files with many many formulas. I am considering swithcing
to access. I was wondering can i use similar formulas and
calculations used in exel in access?

thanx for the help

Similar and sometimes identical, but the approach is different. There
is a big learning curve going into Access. Not every kind of project is a
good candidate for Access, but from the little you have indicated it sounds
like your applications are.

You will find your "many huge files" will become small in Access. In
Excel you talk about thousands of records, in Access we can have millions.

 

[Albert D. Kallal]

ms-access is much better at sorting and grouping data.

And, of course a file of 75,000 records is small.

However, it is a tool to store data, and also to extract data. It is not
particularly good at doing calculations.

The best solution is to store the data in ms-access. You then build some
kind of interface that lets you slice, and dice, and extract the data you
need. If you need calculations on that data, then often the best solution is
to send summary data to Excel, and then do further processing in Excel.

It should be said however, that for things like groupings, and totals,
ms-access is better regardless of the size of the file.

It also depends on how many formulas, and how the formulas references the
data. The concepts, and much of how you naturally use cells does not apply
to ms-access. So, ms-access is a data storage tool, and is very good at
extracting and grouping data. For lots of calculations on those results,
sending it to Excel might be better.

On the other hand, often systems that requite a good deal of calculations
are much BETTER in ms-access. For example, you can put together a smoking
nice payroll system in ms-access, and the same in Excel a horrible
unmanageable mess. And, payroll has a lot of complex calculations. So,
another very important issue to keep in mind is if he END USER needs to
change the calculations and formulas. If the end user needs to change these
formulas..then Excel is far better. If the data can be organized correctly,
and the calculations don't change over time, then ms-access is better (the
payroll example is likely the best example of this concept where you have a
good deal of data...and then you do data processing on the data).

 

[John Vinson]

Hi,
right now i work on data using exculsively excel to preform my calculations.
Right now i have gotten to the point that i have many many huge files with
many many formulas. I am considering swithcing to access. I was wondering can
i use similar formulas and calculations used in exel in access?

thanx for the help

Well, Excel is a spreadsheet, an excellent one. Access is a relational
database.

THEY ARE DIFFERENT!

Despite the appearance of a table datasheet being very similar to a
spreadsheet, they are *not* the same. Using spreadsheet thinking in
Access will get you into some VERY bad designs very rapidly.

Yes, you can do calculations - not in Tables, which are purely and
simply for data storage, but in Queries instead. In a spreadsheet you
routinely mix data storage with data manipulation with data display;
in Access these are three quite separate functionalities (done in
Tables, Queries, and Forms respectively).

If you're going to move to Access, by all means do so - but DON'T just
dump your spreadsheets into tables and try to use Access as a
spreadsheet. "You can drive nails with a crescent wrench but that
doesn't make it a hammer!"

Instead, take the time to learn about relational database design,
Tables, Normalization, Relationships, query design, and all of the
powerful features of Access; and prepare to change the way you think
about and interact with the information. It'll take some time and some
effort but will be very well worth it in the end.

John W. Vinson[MVP]