considering learning Access

[PinkFloyd201]

I've had MS Office 2003 since it came out. I'm an advanced Excel user,
and it has pretty much done whatever I've needed it to do. But I've
never touched Access. However I wish to expand my skill-set and was
wondering if its worth my time to learn it. I work for a financial
services firm where we tend to be on Excel all day long. I read a few
of the Access versus Excel topics in these newsgroups. Based on what
I've read, I figured it can't hurt to learn a new tool. But I recently
stumbled across a webpage by a guy who does Excel consulting work who
says that you don't need Access as a database, and Excel is still the
way to go.

Here is the link: http://www.excel-vba.com/article-excel-access.htm

I am not affilated with the owner of the site, nor have I done any
business with him. Just wondering if his arguments (and claims) make
any sense to the hard-core Access users who might be reading this.

 

[Rick Brandt]

I've had MS Office 2003 since it came out. I'm an advanced Excel
user, and it has pretty much done whatever I've needed it to do. But
I've never touched Access. However I wish to expand my skill-set and
was wondering if its worth my time to learn it. I work for a
financial services firm where we tend to be on Excel all day long. I
read a few of the Access versus Excel topics in these newsgroups.
Based on what I've read, I figured it can't hurt to learn a new tool.
But I recently stumbled across a webpage by a guy who does Excel
consulting work who says that you don't need Access as a database,
and Excel is still the way to go.

Here is the link: http://www.excel-vba.com/article-excel-access.htm

I am not affilated with the owner of the site, nor have I done any
business with him. Just wondering if his arguments (and claims) make
any sense to the hard-core Access users who might be reading this.

I don't need to look at the link. You need to use a database product when what
you need is a database. A database means "structure" and "rules" that impose
requirements and restrictions on the data within. A spreadsheet cannot provide
these things.

If what you need is a spreadsheet then you should Excel. A spreadsheet means a
grid structure that can perform calculations on cells based on their relative
positions in the grid. A database cannot do these things because a table is not
the same as a grid.

If what you need is a simple list then you can use Notepad or Word or Excel, but
the latter will provide some basic tools for filtering and sorting that can be
handy. This does NOT mean that you have a database. It is still just a list.

 

[HeyNow100]

Thanks for the quick reply. What kind of calculations can Access
handle? At work we have a huge Excel file that has the daily price
(net asset value) of our mutual funds going back many years (one tab
per fund), along with another tab with monthly/quarterly dividends that
have been paid out of the fund. The front tab is the one most users
will look at, and its set up so that if you put in two dates it will
calculate the total return (holding period and annualized), assuming
reinvested income. This file is huge and is slow. The only way to
speed is up is to temporarily set the Excel calculation to manual
rather than automoatic.

If we were to convert the data to an Access database, would it work
faster? The front page might be a bit more user friendly if were set
up as a form. I think the calculations are based of of simple + and *
operations. (I didn't create it but I don't think its using any
special Excel worksheet functions.) Is there a way to have a simple
Excel end-user sheet pull info out of Access? I should really buy an
Access book....

 

[Rick Brandt]

Thanks for the quick reply. What kind of calculations can Access
handle?

The appropriate question is not "what kind of calculations?" but rather "what
*data* can a database perform calculations on?

Access VBA has many built in functions and you can use those to also create your
own custom functions so there is no mathematical calculation that you cannot
perform. What is different is that databases are designed to perform aggregate
calculations on columns of data. If you have a large grid where you need the
sum, average, max, min, count, etc.. on a single column then a database product
could also perform that calculation. You can also perform calculations in
database queries using any of the fields in each row. For instance, a
calculated field [TaxAmount] could be derived with the expression
"[InterestRate] * [InvoiceAmount].

If you need to perform row by row "running" calculations then database products
are not so good at this though they can be done in reports or with looping code
routines. The base language for databases though (SQL) is not built for such
things. Also, calculations like "take the value in a grid cell and multiply it
by the value in a cell that is two columns to the left and three rows up" simply
has no parallel in a database.

At work we have a huge Excel file that has the daily price
(net asset value) of our mutual funds going back many years (one tab
per fund), along with another tab with monthly/quarterly dividends
that have been paid out of the fund. The front tab is the one most
users will look at, and its set up so that if you put in two dates it
will calculate the total return (holding period and annualized),
assuming reinvested income. This file is huge and is slow. The only
way to speed is up is to temporarily set the Excel calculation to
manual rather than automoatic.

If we were to convert the data to an Access database, would it work
faster?

There is no way to know that based on the information provided. In general you
still have the same computer doing the calculation. As long as the expressions
and algorithms used to produce the result are the same then I would expect
similar execution times.

The front page might be a bit more user friendly if were set
up as a form. I think the calculations are based of of simple + and *
operations. (I didn't create it but I don't think its using any
special Excel worksheet functions.) Is there a way to have a simple
Excel end-user sheet pull info out of Access? I should really buy an
Access book....

The "Get External Data" options in Excel can be used to import or link to data
in any database that you have an appropriate driver for. Access can certainly
be utilzed this way.

 

[John Vinson]

I've had MS Office 2003 since it came out. I'm an advanced Excel user,
and it has pretty much done whatever I've needed it to do. But I've
never touched Access. However I wish to expand my skill-set and was
wondering if its worth my time to learn it. I work for a financial
services firm where we tend to be on Excel all day long. I read a few
of the Access versus Excel topics in these newsgroups. Based on what
I've read, I figured it can't hurt to learn a new tool. But I recently
stumbled across a webpage by a guy who does Excel consulting work who
says that you don't need Access as a database, and Excel is still the
way to go.

Here is the link: http://www.excel-vba.com/article-excel-access.htm

I am not affilated with the owner of the site, nor have I done any
business with him. Just wondering if his arguments (and claims) make
any sense to the hard-core Access users who might be reading this.

You can drive nails with a crescent wrench - but that doesn't make it
a hammer.

Access is emphatically NOT a "bigger faster Excel". They are
*different* tools for *different* functions. If what you need is
extensive calculations, what-if operations, and the like, Excel is the
tool of choice; if what you need is to pull together disparate data of
many kinds, in logical relationships, go with Access. Quoting Peter's
article:

Microsoft Access is a database application. Microsoft Excel is a data
analysis and reporting application.

and use the tool that's right for the job - or, better perhaps, use
the two programs in conjunction (it's fairly common for example to
store data in Access and link to Excel to generage graphs).

John W. Vinson[MVP]

 

[Keith Wilby]

Here is the link: http://www.excel-vba.com/article-excel-access.htm

The author of that article is a buffoon. Nowhere in the article do the
words "table", "relationship", "relational", or "integrity" appear. How can
you argue about a database design without using some or all of them?

Regards,
Keith.
www.keithwilby.com

 

[Ed Warren]

One of my clients is an investment manager. We just completed a similar
conversion. It does make the calculations faster, for one you only have to
'calculate' the values you need
the values for the selected dates, selected mutual funds, dividends etc.

Whereas Excel does ALL the calculations in the spreadsheet. That said,
implementing such a beast in Access is best done using visual basic for
application (VBA) code.

I would recommend if you make the decision to switch over to a database
driven approach you consider using SQLServer\Express (2005) as the data
repository (the data 'back-end') and use MSAccess as the (front-end) (forms,
reports, code).

It is also possible, and fairly easy to use a combination, store the raw
data in a database, then export it interactively into the spreadsheet.

Note however, none of the above is for the 'faint of heart' MsAccess has a
steep learning curve, in addition there is the learning curve just to
understand how a relational database works, but then it is a new skill and
one becoming more valuable each day as more and more computer applications
are becoming 'data driven'

Best of luck

Ed Warren

 

[BruceM]

I read the article. The guy spoke about a bank or somebody spending two
years trying to develop an Access database, then called him in, and he
implemented an Excel solution in three months. It seems not to have
occurred to the guy that the two years may have been a problem with the
Access developer, not with Access. That it was becoming "user unfriendly"
means they chose the wrong person to develop the application. There are
plenty of people who manage to pass themselves off as experts without
knowing very much. A slick veneer can look impressive to somebody (such as
the person who decides to hire consultants) who doesn't understand that the
foundation is a crumbling mess. Early in my Access learning (and I am still
not very advanced) my company was presented with a database that looked
pretty good until the year ended. Suddenly the twelve queries and twelve
reports developed all manner of problems.
As Rick said, Excel is for one need, and Access for another. There is some
overlap, but the programs are not interchangeable. It depends entirely on
your needs. The author either didn't want to learn Access or was unable to,
so he slammed it instead. In your house you don't choose between a
refrigerator and an air-conditioner (assuming you can afford both). They
both have condensors and are designed for cooling air, but are not
interchangeable because of some similarities.
The author shows a fundamental misunderstanding early in the article when he
refers to Excel and Access as applications. Access should be thought of
more as an application development tool. If you open a new Excel
spreadsheet you can start typing. If you start a new Access application you
can do nothing until you have developed data storage and a form or some
other interface for handling the data.

 

[Krizhek]

After reading the article I felt... less. The article has some interesting
arguments but like other comment made on this post already there is just so
much more control that you can gain from using Access (BTW he did a great
spelling of 'Access' in the article).

Being one that has moved from Excel to Access there are some thought
patterns that you have to change when doing the move. But once you gain just
a basic understanding your much better off then with just using Excel alone.

The author mentioned that the customer can change the information, if this
is true then he must of forgotten to mention the myriad amount of times he
had to fix excel spreadsheets because someone who didn't know much about
excel messed up a function and killed the whole project but (which he may of
developed a way to get around this issue, but I just opens up another door).

And I am trying to understand his intense love of sumproduct...

Personally I like Access because I have:
1. Control over how data in imputed and what data is imputed
2. A multi-user solution
3. Access provides the groundwork building a application
4. Information in ONE location no more linking between multiple excel files
or sheets.
5. Happier managers

BTW... I am sorry you had to read these comments but you asked for it

I hope it helps

 

[BruceM]

I noticed that the author did not regard as a potentially serious limitation
the fact that Excel is limited to one user at a time. You really have to
hope people close the program the minute they're through with it.

I use programs where they are appropriate. I am much happier with our
training records database now that I do not have to copy a record of some
particular training onto each of several (up to 70) worksheets for
individual employees. For other purposes, Excel works best. That's all
there is to it, IMHO.

 

[mcnewsxp]

if you work for people that use excel for a database you will have hard time
convincing them to even look at other alternatives. it is a mold that is
nearly impossible to break. epidemiological people are the same way. give
them a 10 mile wide spreadsheet and they are happy. with financial people
it's a bit different because of the macros they use. the thing is they
learned excel as a financial tool thinking it was the only tool to use for
the job. they don't look at it from a data processing point of view.

 

[Hanr3]

Interesting thread to say the least. I use Excel to crunch numbers, full of
if/then statements, Look ups, etc. But I was looking at Access as a tool to
withdrawn that info for some very user friendly reports. My reports need to
draw data from 24 or more Excel workbooks. Not impossible in Excel and is my
current method, just time consuming to open each and every workbook. However
I was hoping there was a better way, Access?

 

[John Vinson]

My reports need to
draw data from 24 or more Excel workbooks. Not impossible in Excel and is my
current method, just time consuming to open each and every workbook. However
I was hoping there was a better way, Access?

If the data in the 24 workbooks can be migrated into a *PROPERLY
NORMALIZED* set of Access tables (likely to be the case, with some
work), then the answer would be Certainly. If that's not something you
want to do, the answer would be A Conditional Maybe.

John W. Vinson[MVP]

 

[mcnews]

short answer - access will make that task much easier.
you will need to find a hook or key (common data item) to tie the
workbooks together.
they might even import to a single table that you deliniate with a date
field or something.
access can handle your business rules (if/then & lookups) much more
efficiently than excel.

 

[dbahooker]

if you're an excel dork then learn access for sure.

but dont spend more than a month in access; it is a crappy database

learn SQL Server; especially Olap.

Excel is a disease

 

[dbahooker]

a database doesn't mean structure and rules; i disagree.

a database means that you can DO THINGS with your data.
I could store everything as text-- hence no rules-- and i could still
DO THINGS with my database.

If you have excel and you have a whole bunch of data; you have nothing.
if you have a database and a whole bunch of data you can do things with
this.

Excel is a dead end street; a sinking ship; a leper that studies in his
ivory tower for 90 years and then dies without sharing his knowledge
with the world.

a Database is a foundation-- that you can build upon-- and share with
others.

 

[dbahooker]

like i said; use olap.

Rick you're a fucking idiot; do you know that?

'oh a database can't be used to sum information'

go screw yourself Rick.
grow some balls and learn SQL Server kid.

 

[dbahooker]

You can drive nails with a hammer; but if you have your choice between
a spot-weld and a thumbtack?

Access is more powerful than Excel.
Excel is for babies.

Access is for older babies.

Learn a real fucking database kid.
Microsoft Access ISNT a database application.

It is a reporting platform-- data entry platform.

the idea of holding data in a mdb file is laughable at best.

screw excel if you want real graphs off of terabytes of data-- use Olap
and Office Web Components

 

[Terry Kreft]

From this and other posts I suspect you're just a troll, but there is the
possibilty that you're actually incompetent and prefer to blame the tool.

 

[Terry Kreft]

From this and other posts I suspect you're just a troll, but there is the
possibilty that you're actually incompetent and prefer to blame the tool.