Access Vs. Excel

[Cerberus]

I am making a configurator that will tell us how much material, time and
total cost it is for all the options for every one of our company's trailers.
We have an untold amount of combinations and this will be a major task.

The question I have since I have never really used Access is, what are the
advantages of using Access for this project over Excel. I have made a very
large Excel spreadsheet and have shown some Excel "experts" my work, and
almost every one of them said they thought Access would be better but none of
them gave a reason why.

Can Access handle equations that are equivalent to Excel's =If(or(and))?

Can one cell accept more than 7 variables? In some cases we have more than
12 options for a single feature.

 

[gator]

A Form can be used to input all the variables of a specific job and some
properties/code, such as DLOOKUP, can be used in the Form to grab values from
Tables that are set up by you to specify the cost per hour, cost per foot,
etc...and what's good about Access is that the Form can be customized to the
user's wants and needs....I choose Access everytime there is a repetitive
process for creating and using information.

 

[Steve Schapel]

Cerberus,

almost every one of them said they thought Access would be better but none
of
them gave a reason why.

I imagine the reason is that Excel is a spreadsheet program and Access is a
relational database program. A spreadsheet is not a relational database.
From your description, it would appear that your purpose is not a
spreadsheet purpose. It appears that you are trying to manage relational
data. Therefore use the tool designed for that job.

The down side, of course, is that you will have to learn how to use Access!
Which takes a bit of time and effort.

 

[Clif McIrvin]

Access requires a completely different way of thinking about how you
define and solve your problems than Excel does.

"If the only tool you have is a hammer, every problem tends to look like
a nail."

In the long run, Access will give you far more flexibility for putting
together variable combinations than will Excel; not to mention it will
be orders of magnitude easier for office staff to maintain the
vairiables that change over time in a properli designed database.

Since you're not familiar with Access, here's some introductory
information that I wish I'd have found *before* beginning development on
my application:

(Thanks to John W. Vinson [MVP] for this info
"Access has a steeper learning curve than (say) Word or Excel; not
least, in order to make productive use of the program you have to
understand the theoretical basis of database design - a concept called
'Normalization'. It is very logical and not at all difficult once you
get the concepts down."

Here are some tutorials and other resources that you should find
helpful:

A free tutorial written by Crystal (MS Access MVP):
http://www.accessmvp.com/Strive4Peace/Index.htm
also at http://allenbrowne.com/casu-22.htmlMVP

Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Here's a primer with 23 well defined, well written, clearly named
chapters:
http://www.functionx.com/vbaccess/index.htm

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

and Access MVP Larry Linson has additional Access resources in a list
at:
http://sp.ntpcug.org/accesssig/default.aspx

 

[Beetle]

Access would likely be a much better application for your needs based
on what you describe. It is better at handling large amounts of data and
it has very good tools/funcionality that allow you to manipulate and view
the data in whichever manner you wish, allowing you to work with only
the data you need, when you need it.

However, you need to be aware that Access stores data in a completely
different manner than Excel. Access uses the relational model, where
separate entities of data are stored in separate, related, tables. Access
also has, by far, the steepest learning curve of the Office applications. If
are able/willing to invest the time and effort needed to learn the
fundamentals, then you can eventually end up with an application that
will be much more efficient than Excel for your circumstances. On the
other hand, if you are in a hurry to get a working app, and you take shortcuts
or rush through the creation process, you will likely end up frustrated and
with an application that doesn't work. Access is not a spreadsheet on
steroids, and trying to treat it as such will result in failure.

Can Access handle equations that are equivalent to Excel's =If(or(and))?

Yes, but it does so in a different manner, with different syntax.

Can one cell accept more than 7 variables?

This question doesn't apply to Access because Access doesn't have cells,
it has fields, and you don't do calculations or use variables directly in
the fields. You use queries and other methods (like VBA functions) for
this. Fields are for storing raw data only.

Here are some resources where you can learn more about the
fundamentals of Access.

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Tony Toews' website:
http://www.granite.ab.ca/access

 

[Jeff Boyce]

To address the question about multiple values in a "cell", first, as
mentioned else-thread, Access doesn't have "cells".

Good database design argues against the idea of putting more than one value
in a single "field" - one-fact, one-field.

If you are saying that you could have one-or-more [options] per [feature],
congratulations! You've just discovered relational tables! (you'd probably
use three tables to store this information: one for options, one for
features and a third to hold the valid combinations of options-on-features).

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[david]

Access is good at selecting data.
Access is good at working with Groups of data.
Access is good at selecting groups of data.

You want to select material, time, and total cost
for each option for each trailer:

That sounds like selecting a group of data, so it
sounds like something Access will be well suited
for.

The total amount of effort to create and maintain this
application will be less than if you did it in Excel. In
Excel there will be a lot of duplicated effort, and lots
of places to get it wrong.

But the data input and display will not be as easy as
Excel.

You don't want to put 7 variables into a 'cell' in Access.
You can do that, but you get something that has all the
problems of Excel, and none of the advantages. The worst
of both worlds.

What you want is to put 7 variables into a table, each has
it's own cell, but all the cells are linked back to the configuration.
You then just select the linked options and calculate the total
cost: Access is really good at selecting groups of options.
Actually, what you put in the table is links to each of the linked
options... it's a bit different from Excel.

Start by putting each of the options into a table, then each of
the trailers into a table, then create a configuration table linked
to the trailers and options tables.

Access can do equations like =If(or(and)). It's just a question
of where you want to put the equation. In Excel, it always goes
in a cell: in Access, that is the second last place you want to put
it, if you can't get it into the database design or the select query.

(david)

 

[Cerberus]

Thank you all for your input on this. It looks like I will be learning a lot
about Access.

Is there any book you would recommend, or should the links provided in some
responses the best place to learn.

 

[Clif McIrvin]

Start with the links ... you will find references to books in some of
those.