Access

What is a database?

This course will cover creating a Microsoft Access database from scratch. At the end of this course, you'll be familiar with the four basic parts of Access and be able to create, modify, and use an Access database.

But before we can talk about building a database, you need to know what a database is.

A database is a collection of information organized by common types of data.

For example, a phone book is a database. It's a collection of information, such as names, addresses, and phone numbers, and is normally organized by some method, such as last names, and then first names.

Each one of these pieces of data is called a field. Even if a particular field is left blank -- for example, Cher has no last name -- the field is there and available to be used if so desired. The entire collection of fields is called a record, so all of Cher's info is considered Cher's record. The collection of records is called a table and that is the basis of all computerized databases. In fact, if you're familiar with Microsoft Excel, you'll be comfortable with database tables. The rows equate to records and the columns to fields. Unlike Excel, each record in Access always stays together; the fields can't be treated as separate items like Excel cells.

Why would you want one?

There are many reasons you might need to use a database. Most business people need to track contact information of some type. Perhaps you need to track clients, prospects, or employee information, such as hire date, qualifications, department, and contact information, including phone, address, and e-mail.

Overview of database structure

In programs like Microsoft Word or Excel you'll see only one type of object - in Word you see pages and in Excel you see spreadsheets. Computerized databases have several basic structures in common. Access, for example, has these objects:

	Tables: Where you actually store the information in a database. Tables are the heart of the database. All other objects act upon the table and the information contained within.
	Forms: Puts a face on your database to make it easier to use. You can create a layout that makes sense as well as create different forms for different uses, each one showing only select information. In addition to data entry and lookup, forms can be used as menu systems to navigate your database.
	Queries: Used to pull only specific data from any table or combination of tables. It can then be used in a form or report. In addition to pulling data, there are special action queries to update and create new tables, or delete data.
	Reports: A layout with certain data selected so every time the report is run, it pulls the most current data from the tables.
	Pages: Refers to a Web page. Using this technique, you can create pages to run on an internal network or on a Web site to retrieve and display data -- similar to forms.
	Macros: Automated list of directions, often tied to buttons or another type of action within the database.
	Modules: Programming pieces of code that can be called and used throughout the database. Access uses a popular programming language called VBA (Visual Basic for Applications). It's very similar to Visual Basic with a few differences. (We don't cover modules, but do cover some small pieces of programming within certain parts of our database.)

In this course, we focus on tables, forms, queries, and reports. We don't cover the more advanced items, but it's helpful to know what they are and that they're available for use.

Advantages of databases

Databases offer several advantages to other methods of keeping data. Traditional ways of keeping data can include word processing documents, paper cards, or spreadsheets.

Some advantages of using a database as compared to these methods include:

	Sorting: You can sort a database by any field with a click of a button, such as First Name, Last Name, State, City, or other criteria, as compared with the time consuming manual sort required by paper lists or the careful steps that need to be taken in Word or Excel. You have to be careful when sorting in Excel for it is very easy to select a column, sort according to that column and then save your changes - this is often a terrible mistake for it sorts ONLY that column and therefore the sorted cells may no longer match the data in the rest in the neighbouring columns. A database program will always keep each record (row) intact when sorting.
	Share information: Multiple people can use the same database of information. For example, an insurance office keeps all their client information in an Access database and all employees can access the database at the same time. Only one person can use a Word file or paper items at one time, unless you make copies. However, when you do that, updates made to one copy are not automatically sent to other copies. Excel can actually be multiuser, but it locks a whole sheet (table) of information when someone is updating it.
	Central updating: When one person updates information, all the other people have immediate access to it, giving them up-to-the-minute accurate information -- there's nothing worse than calling a company to update your address or phone number and the information is not passed on to all of the departments.
	Flexibility: Pulling information in many ways. If you have a table of clients and a table of your salespeople, you can create lists that show clients and whom their salesperson is or a list of salespeople with their assigned clients underneath and any other pertinent information. Queries and reports allow almost unlimited combinations of data and each time the query or report is run, it pulls from the underlying table to get the most current data and lay it out as you have specified. Paper, Word, and Excel documents tend to be static.
	Communication: Linking to other data sources. This can include other databases in your office, at remote sites, or on the Web. These databases can be Access or any other ODBC (Open Data Base Connectivity)-enabled database, such as MAS90 (accounting software), mySQL, SQL (Structured Query Language) Server, or others. You can also import and export from other software. Word and Excel have some capabilities in this area, paper does not.

Possible database uses

There are endless ways to use a database. For a teenager, the most interesting uses could include and address book or a database of information on your CD or DVD collection. It's true that you could easily keep this information on paper or in an Excel file - the reason for a teenager to use a real database program for this would probably be to become acquainted with a type of software that (s)he is likely to meet again in a professional context later on.

Popular databases

For databases, as for other kinds of software, we can say that there is a spectrum of alternatives going from easy-to-learn but rather limited programs to very sophisticated but hard to learn programs. Listing some of the best-known database programs in order of their sophistication, with the simplest first, we have:

	FileMaker Pro is easier to use and works on Macs as well as PCs but it has severe limitations such as not allowing multiple tables per file
	Microsoft Access has more features than FileMaker but is still fairly easy to use. It only works on PCs though, not in Macs.
	mySQL is very similar to Access, but runs best on a Unix platform, as opposed to a Windows platform.
	Microsoft SQL Server has more features than the databases mentioned above.
	Oracle is also a much more sophisticated database, but not one you'll see in a small office.
	Postgres is a more advanced database that runs best on a Unix platform.

Moving On

This course continues with a work on a sample database on the theme of banking. This tutorial was inspired by a free tutorial from the City University of New York. Follow the link at the top or bottom of this page to continue.