Microsoft Access 2010 Fundamentals

Are you overwhelmed by the large quantities of data that need to be tracked in your organization? Perhaps you're currently using a paper filing system, text documents or a spreadsheet to keep track of your critical information. If you're searching for a more flexible data management system, a database might be just the salvation you're looking for and Microsoft Access 2010.

What is a Database?

At the most basic level, a database is simply an organized collection of data. A database management system (DBMS) such as Microsoft Access, Oracle or SQL Server provides you with the software tools you need to organize that data in a flexible manner. It includes facilities to add, modify or delete data from the database, ask questions (or queries) about the data stored in the database and produce reports summarizing selected contents.

Microsoft Access 2010

Microsoft Access 2010 provides users with one of the simplest and most flexible DBMS solutions on the market today. Regular users of Microsoft products will enjoy the familiar Windows look and feel as well as the tight integration with other Microsoft Office family products. For more on the Access 2010 interface, read our Access 2010 User Interface Tour.

Let's first examine three of the major components of Access that most database users will encounter - tables, queries, and forms. If you don’t already have an Access database, you may want to read about Creating an Access 2010 Database from Scratch or Creating an Access 2010 Database from a Template.

Microsoft Access Tables

Tables comprise the fundamental building blocks of any database. If you're familiar with spreadsheets, you'll find database tables extremely similar.

A common database table might contain employee information, including characteristics like name, date of birth and title. It might be structured as follows:

• Employee ID
• Last Name
• First Name
• Title
• Salutation
• Birth Date

Examine the construction of the table and you'll find that each column of the table corresponds to a specific employee characteristic (or attribute in database terms). Each row corresponds to one particular employee and contains his or her information. That's all there is to it! If it helps, think of each one of these tables as a spreadsheet-style listing of information. For more information, read Adding Tables to an Access 2010 Database

Retrieving Information from an Access Database

Obviously, a database that only stores information would be useless -- we need methods to retrieve information as well. If you simply want to recall the information stored in a table, Microsoft Access allows you to open the table and scroll through the records contained within it. However, the real power of a database lies in its capabilities to answer more complex requests, or queries. Access queries provide the capability to combine data from multiple tables and place specific conditions on the data retrieved.

Imagine that your organization requires a simple method to create a list of those products that are currently selling above their average price. If you simply retrieved the product information table, fulfilling this task would require a large amount of sorting through data and performing calculations by hand. However, the power of a query allows you to simply request that Access only return those records that meet the above average pricing condition. Additionally, you can instruct the database to only list the name and unit price of the item.

For more information on the power of database queries in Access, read Creating a Simple Query in Microsoft Access 2010.

Inserting Information into an Access Database

So far, you've learned the concepts behind organizing the information in a database and retrieving information from a database. We still need mechanisms to place information into the tables in the first place! Microsoft Access provides two primary mechanisms to achieve this goal. The first method is to simply bring up the table in a window by double-clicking on it and adding information to the bottom of it, just as one would add information to a spreadsheet.

Access also provides a user-friendly forms interface that allows users to enter information in a graphical form and have that information transparently passed to the database. This method is less intimidating for the data entry operator but requires a little more work on the part of the database administrator. For more information, read Creating Forms in Access 2010

Microsoft Access Reports

Reports provide the capability to quickly produce attractively formatted summaries of the data contained in one or more tables and/or queries. Through the use of shortcut tricks and templates, database users can create reports in literally a matter of minutes.

Suppose that you want to produce a catalog to share product information with current and prospective clients. In previous sections, we learned that this sort of information could be retrieved from our database through the judicious use of queries. However, recall that this information was presented in a tabular form -- not exactly the most attractive marketing material! Reports allow the inclusion of graphics, attractive formatting and pagination. For more information, see Creating Reports in Access 2010.