What is the difference between a database and a spreadsheet?

From a neophyte’s perspective (read: mine), they appear to be basically the same. What additional features would a database-generating software such as Access have over Excel to make me go through the trouble of learning it?

499 posts to 1000! Inching ever closer to the Millennium!

A database is an archive of stored information.

A spread sheet is a specific layout format for the review of entered information. Spread sheets also permit the formulaic interaction of various segments therein. These are not features of the traditional database.

More experienced people will be along shortly.

They’re two very different animals. A database is a collection of records, each with certain amounts of information. For instance, I have a database of addresses and phone numbers of my friends and family. Each record has the person’s name, address, and up to 3 phone numbers, email address, as well as their relationship to me. I can look through the database, looking at each person indidually. They’re name at top, with all the information. I can also search the database to give me just the records of family members. Now…I can tell the database program to put the records in the database into a spreadsheet, so all the information is present on one sheet, but that’s just an output option.

A spreadsheet is a grid of columns and rows of data, which can be laid out in various ways. You can use a spreadsheet as a database, but you lose a lot of the searchability that databases offer. Also, spreadsheets are very useful for calculations, invoices, manipulating large amounts of numerical data…things a database can’t do.

Jman

Where Access has it over excel from the programmer’s sense is that Excel only really permits information to be stored and retrieved in 2 dimensions…rows and columns. The later versions of Excel allow for a quasi-third dimension by using worksheets.

Access is what is called a ‘relational database’. That is you can create many different databases and store them in one master database. A relationship may be set between these potentially different dimensional databases that links all of the information together.

For example, there may be separate databases at your company which tracks vacation accruals, salary information, review information, and general personal information. It may be unwieldy to maintain all of this information in one database. Furthermore, different people may be responsible for maintaining the different types of information (i.e. accounting vs. HR). However, in Access, all of these databases can be ‘related’ by linking…say…your employee number together so master merged reports could be created.

Additionally, Access allows for the creation of pretty data entry forms as well as pretty report generation tools. Excel really does none of this efficiently. However, Excel (to wit spreadsheets in general), which was borne out of an accounting function is far stronger in the ability to perform complex mathematical functions. Each program has overlapping functions but do have distinctly different purposes.

I’m not sure if this description would make you want to learn Access…but I consider learning Access like driving a stick shift…you could get through life without learning it but it is a good life skill to have.

Well, Access is not a particularly great skill to have, but SQL is. Access, as an RDBMS, blows goats. Learning basic SQL will allow you to effectively use most any relational database product. The key of relational database design is primary and foreign keys. To design complicated databases, you also need to learn about normal forms and atomization and functional dependencies..

Here’s an EXTREMELY brief rundown, which I am offering as a reference for this and future threads since we’ve been getting a lot of this type of question recently.

SQL: SQL (which is NOT pronounced “sequel”) is the Standard Query Language. It is an ANSI standard for sending queries to database servers. Every database product supports SQL, in addition to other nonstandard SQL extensions. Microsoft products also support their proprietary query language, ODBC.

Primary and foreign keys. A primary key is a data field whose value is guaranteed to be unique for every record. In an employee database, you might make this, say the last name of the employee. But, there might come a time when two employees have the same last name. Better to just create an employee ID number for each person. A foreign key is a data field whose value refers to the primary key of another table. For instance, suppose we have two tables, an employee list, and a list of departments and information about each department. Each employee has an ID, and each department has an ID. You might relate the employee table to the department table by putting the department ID of an employee’s department in the employee database. For example:

Employee DB:


ID   Last   First    Office    Favorite Color      Dept
1    Jones  Bob      23-D      Blue                6

Looking at this entry, we see that Mr. Jones belongs to department six. We can then look at the department table to get all the information about department six.

There are ways to do more complicated relationships, such as one-to-many relationships, but I won’t go into those here, unless you want me to.

Normal forms: The normal forms are standards for dealing with the functional dependencies in your data. I’m not going to go into the details here unless you want me to.

Atomization: This is the process of defining the discrete points in your data set. Having one field for the model and color of a car would lead to redundancy, since one model of car can come in several colors.

Functional Dependencies: These are the dependencies you have to figure out in order to design your database correctly. For example, in our employee database, the department ID is functionally dependent on the employee ID, because there must be one department for every employee. The inverse is not true, because several employees can belong to one department.

This stuff, if your name happens to be friedo, is extremely fascinating. Yep, I figure out functional dependencies between six progressively related tables for fun on Sunday mornings. :slight_smile:

For a fantastic introduction to relational database theory (and vastly more detail on the things I’ve mentioned above) read the first five chapters of this book.

Oops. That link doesn’t work. Try this!

It’s “structured” query language, innit?
I’ll agree, “sequel” sounds kind of stupid, though.

quasar, if you are interested in databases, Access isn’t a bad start. It isn’t the be-all-and-end-all database solution, but it may make a semi-technical person’s job easier. Especially given that most companies aren’t going to give non-techies access to other relational database software. I would HIGHLY recommend “Access 97 Developers Handbook,” from Sybex. It gives a decent introduction to database theory and will likely answer any questions you may have regarding Access. You’ll also learn plenty of SQL, which you can apply to other database systems.

For what it’s worth.

You’re right, it’s Structured Query Language. Oops. And yes, Access can be useful, just don’t try to build space shuttles with it.

Think of it like this… in EVERY PLACE I’ve ever worked, people have used Excel for database tracking when they should have been using Access, about 90% of the time.

It takes a bit of a learning curve to get Access to be “as easy” as Excel, but once you bite-the-bullet, it’s truly worth it.

I wouldn’t recommend running an enterprise on Access, but it’s really, really, quite capable of managing an extraordinary amount of data.

(I REALLY wish I had Access for the Mac OS).