Matt said:
I am setting up a database and have a few questions. I am setting up a road
database for the Village that I work in to track the maitenance of the roads,
as well that the time lines for reconstruction.
1. First off I am not sure if I should be using Access or Excel for a
database like this. Currently we have information stored in a single Excel
worksheet fine, but it is hard to navigate the ~1,000 records and 50+ columns.
The first thing to know is that MS Excel and MS Access are entirely
different pieces of software.
It is possible to see an MS Excel spreadsheet and an MS Access
datasheet, side-by-side, and think, "Hey, they look alike." They are
not.
A spreadsheet (MS Excel) offers a way to store data in cells formatted
in records and fields. These cells can be programmed to do many
things, calculate data in themselves or from other cells, alter
formatting, etc.
A relational database (MS Access) offers a way to store data in
columns and rows. There are no cells. The data in those columns and
rows just sits there.
The power of a relational database is that you have a special computer
language called SQL that you use to ask questions. The database
manager (a part of MS Access or any relational database) takes your
question written in SQL, and decides how to go about answering it for
you. Once it has grabbed all the information you want, it tosses it
out onto a datasheet for you to see (the thing that looks like a
spreadsheet, but isn't). Datasheets are nothing more that primitive
display systems, they cannot be programmed or formatted. The only
time you want to look at them is during development to see if you are
writting your SQL statements correctly. For all finished "real"
applications, all data is displayed on "forms" that you design, and
datasheets are never seen.
SQL is very, very flexible and powerful, and the most basic uses of it
are very easy to learn (although the complicated parts can be very
complicated).
2. I was wondering if it is possible to setup calcuations in Access as you
can with Excel. For example, a road has a 100 year life span. Can Access be
setup to automatically countdown from that each year?
You can, but it isn't the same.
You use SQL to ask the database questions. You use forms (that you
design) in MS Access to display that data. You can design the forms,
and write Visual Basic for Applications programming code in
association with the forms to replicate almost any calculation a
spreadsheet can do. That is going to be quite a bit of work. It may
be worth it, or it may not be worth it.
What you need to do is try and estimate how many MS Access tables and
forms you will need to replicate your spreadsheet's functionality.
To estimate the number of tables, you need to understand
"normalization", or the rules of building tables in databases.
To estimate the number of forms in MS Access, you need to figure out
how many different types of functions/interactions are carried out on
your spreadsheet, and form that try and figure out how many forms
you'll need (this requires that you have some idea of how to design
forms).
Once you know how many tables and forms you need, you can begin, in a
very rough way, to get an idea of the amount of work that will be
required to design, test, deploy, and maintain an application.
If you know nothing of normalization and forms design, you have to
factor in that there will be an education work-up time added to this.
I'm skipping a *lot* here. I have to. The subject you are asking
questions about:
Project Management
Systems Analysis
Software Estimation
Data Modeling
Normalization
User Interface Design
Whole libaries have been writen about these subjects.
The more narrow your question, the more specific an answer can be
given.
You're welcome.
Sincerely,
Chris O.
