Access or Excel?

[WhiteRat]

Hi All,

I've meddled with spreadsheets and databases for years, but mostly just for
fun and curiosity. I now have a fairly large project at work, and I'm
looking for advice regarding whether Access or Excel would be my best
option.

For starters, most of us at work, myself included, have Office 2007
installed, and have XP SP3. There are a few here still running 2003, so most
of the work we do is in "compatibility mode". The nature of the work that we
do is to collect biologic data (blood chemistries, hematology, body weights,
food intake etc.) over periods of time ranging from days to months. What we
want to be able to do with the data we collect is to show results in various
configurations. We might want to show all of the serum glucose values for a
particular subject, or group means for subjects over a period of time. Or we
might want to show food intake, body weight and serum glucose levels for
individual subjects or groups of subjects over time, listed sequentially, or
by gender, by age, or other sorts of parameters. We also would like to be
able to produce graphs for presentation of this data.

We currently enter our data into Excel, and pretty much just configure the
spreadsheet to capture all of our raw data as it comes in. But currently,
our summary tables etc. are quite simple, mostly just showing summaries for
groups of subjects for particular parameters and at an individual time
point. We want to be able to do much more with our data, particularly for
reporting. At this time I'm just asking for some more expert advice with
respect to whether we should be expanding what we already do with Excel, or
would we be better off starting up a database in Access. I know that there
is a large amount of overlap, and that we could probably use either one to
do the job, but I'm looking for relative strengths and weaknesses for either
choice.

Also, we have some smart folks here, none that I'm aware of that have a lot
of database experience, but a few who are quite proficient with Excel. I'm
sure if there were a strong reason to move to a full fledged database at
this time however, that we'd have a plenty who would be interested in
getting up to speed with Access.

Thanks for reading all this, and thanks in advance for any opinions/advice

David

 

[Keith Wilby]

Hi All,

I've meddled with spreadsheets and databases for years, but mostly just
for fun and curiosity. I now have a fairly large project at work, and I'm
looking for advice regarding whether Access or Excel would be my best
option.

For starters, most of us at work, myself included, have Office 2007
installed, and have XP SP3. There are a few here still running 2003, so
most of the work we do is in "compatibility mode". The nature of the work
that we do is to collect biologic data (blood chemistries, hematology,
body weights, food intake etc.) over periods of time ranging from days to
months. What we want to be able to do with the data we collect is to show
results in various configurations. We might want to show all of the serum
glucose values for a particular subject, or group means for subjects over
a period of time. Or we might want to show food intake, body weight and
serum glucose levels for individual subjects or groups of subjects over
time, listed sequentially, or by gender, by age, or other sorts of
parameters. We also would like to be able to produce graphs for
presentation of this data.

We currently enter our data into Excel, and pretty much just configure the
spreadsheet to capture all of our raw data as it comes in. But currently,
our summary tables etc. are quite simple, mostly just showing summaries
for groups of subjects for particular parameters and at an individual time
point. We want to be able to do much more with our data, particularly for
reporting. At this time I'm just asking for some more expert advice with
respect to whether we should be expanding what we already do with Excel,
or would we be better off starting up a database in Access. I know that
there is a large amount of overlap, and that we could probably use either
one to do the job, but I'm looking for relative strengths and weaknesses
for either choice.

Also, we have some smart folks here, none that I'm aware of that have a
lot of database experience, but a few who are quite proficient with Excel.
I'm sure if there were a strong reason to move to a full fledged database
at this time however, that we'd have a plenty who would be interested in
getting up to speed with Access.

Thanks for reading all this, and thanks in advance for any opinions/advice

David

It's important to stress that Excel is an application that will work for you
"out of the box". Access is a toolkit that will allow you to *build* an
application and will do nothing at all for you "out of the box". So, you
need to decide if you have the time/resource and what-have-you to develop an
application. If the answer is "no" then use Excel.

If it's "yes" then you need to start thinking about modelling your entities
and their attributes in a set of related tables and then start to build up a
user-interface. In short, Access isn't a "souped-up" Excel.

Keith.
www.keithwilby.co.uk

 

[Steve]

Hello David,

I think it all boils down to whether you need to continuously collect data
or whether you collect data for a particular study that has a definite
starting point and a definite ending point. If you need to continuously
collect data and then do an analysis periodically, Access is the tool of
choice. If you do studies with a definite start and end point, Excel is the
tool of choice.

Steve
(e-mail address removed)

 

[KenSheridan via AccessMonster.com]

David:

From your description I would have little doubt that a relational database
application would be more appropriate to your requirements than a workbook.
Bear in mind that the Excel can make use of Access data quite easily for the
sort of analyses in which it has stronger functionality than Access, whereas,
while Access can make use of Excel data, the format of the latter does not
lend itself so easily to the sort of processing which a database application
can do with a set of logically related tables.

I would strongly concur with what Keith has said with regard to Access being
a development environment rather than an 'out of the box' application, and as
such the learning curve is not to be underestimated. For people like
yourselves, who are working in a scientific environment, there should be no
great difficulty in getting to grips with it.

Space does not allow me to go into great detail here, and there is no
shortage of good literature, both in print and online, but the key thing to
take on board is that a relational database represents a logical model of a
part of the real world in terms of its entity types, which might be physical
or abstract, and the relationship type between them. Relationship types are
in fact just a special king of abstract entity type. In a relational
database each entity type is modelled by a table, and each attribute type of
the entity type is modelled by a columns (aka field) in the table. Each
entity of the entity type is modelled by a row (aka record) in the table. A
relationship might be modelled simply by having a 'foreign key' column, or
columns, in one table (the referencing table) reference a 'primary key'
column, or columns, in another table (the referenced table); Such a
relationship type is one-to-many or one-to-one, the former being the most
common, the latter being used to model a situation where one entity type is a
sub-type of another entity type; biologists are a sub-type of scientists for
instance, i.e. every biologist is a scientist, but not every scientist is a
biologist.

An important characteristic of a correctly designed relational database is
that it protects the integrity of the data by eliminating 'redundancy.
Redundancy is where the database tells us the same 'fact' more than once, e.g.
a table of locations which included columns City and State, would contain
redundancy because for every location in San Francisco say, we'd be told
multiple times that San Francisco is in California. The real issue here is
that this leaves the database open to inconsistent data as there would be
nothing to stop San Francisco being put in different states in different rows
in the table.

Redundancies are eliminated by a process known as 'normalization', which
involves 'decomposing' tables such as our hypothetical Locations table into a
set of related tables, each of which contains no redundancies, in this case
into Locations, Cities and States tables. There are plenty of sources of
information on normalization, but the Wikipedia article on 'database
normalization' is actually pretty good in that it explains each 'normal form'
in easily understandable language without dumbing down the underlying formal
basis, which some online sites do tend to do:

http://en.wikipedia.org/wiki/Database_normalization

The basic principles of modelling are common to all relational database
products, but they differ in how applications are built. Access, as part of
the MS Office suite, is well-suited to small-to-medium workgroup environments,
though can also be used as a front in a client-server environment. It
provides an easy means of building forms and reports to interface with the
data, though beware of some of the built in 'wizards', particularly the
'lookup fields' wizard, and in 2007, the use of multi-valued fields.

Where, in the context of your work, you'll probably see the greatest
advantages in Access over Excel is in querying the database. Queries are at
the heart of any relational database application as they provide the means of
retrieving information from the raw data. Access includes its own visual
query designer, though for more advanced tasks writing queries in the
underlying SQL (structured query language) might be necessary.

One thing I would advise if you do decide to use Access is to spend plenty of
time with pencil and paper diagrammatically drawing out the model in terms of
the tables and relationships required. Look for examples of redundancy, and
ask hypothetical questions of the model to see if it will give you the
answers you need. Time spent like this at the outset will save a lot of
headaches later on if you discover flaws in the model after you've set up the
database on the system.

Ken Sheridan
Stafford, England

PS: I like the 'white rat' moniker. Reminds me of my favourite lawyer joke:

Why do laboratory scientists never use lawyers for experiments? Because
there are some things rats won't do for reward.

 

[Fred]

Keith gave important points relating to the expertise side of using Access
vs. Excel. In short, database development takes more expertise and areas of
expertise than spreadsheet development.

In short, databases are a much more powerful and versatile way to handle
information.

The one area where spreadsheets are more powerful are in doing real-time
mathematics, which is mathematical relationships between cells.

The most common application attribute that would make Excel unsuitable (and
which Access handles easily) is a need to record and manage data or data
entities which has a "many to one" nature. I suspect that this might arise
in your application in which case you will be faced with the choice of
handling it badly in Excell vs. a bigger learning curve (or needing to get
outside help) of going the database route.

 

[WhiteRat]

Thank you all for your input. These are the sorts of considerations I was
hoping to have pointed out, and they will help us in our decision-making
process.