Microsoft Access is a popular database program that is part of the Microsoft Office suite of products. This ITS training document deals with Access 2010, the latest version of Access.
Upon completion of this 1-on-1 training, you will be able to:
Access databases are wonderful for storing, viewing, and retrieving information. In order to make your database easier for you to use now and in the future, spend a little time up front thinking about what you want to store and how you want to display the information. In this example, we are going to create a simple database to store contact information. It will contain first name, last name, street, city, state, zip, phone number, and email address for each contact person. We are splitting first name from last name so that we can sort by either first or last name and can easily use just the first name (or just the last name) whenever we desire.
1. First, open Microsoft Access by clicking Start > All Programs > Applications > Microsoft Office Access 2010.
Note: Access may be located in a different place in your Start menu.
When you open Access, you will see the screen below from which you can either create a new database or open an existing database.
2. Let’s create a new database without using a template. Click on the Blank Database icon in the top middle of the screen. Notice that the right hand side of the screen changes and is now asking for a File Name. A default file name, “Database2.accdb” in this case, is entered for you. Change that to “Contacts.accdb” since we will be creating a database to store names and addresses of people. Once your screen appears as shown below, click the Create button.
3. Your database has now been created and is ready for use. Access expects that the first thing you will want to do with your database is create a table, and so it displays a window that is waiting for the table to be defined. See the screen below.
Now that we have a database, the next step is to create a table to hold the contact information. Since we have already decided on the fields we want in the database, we can begin entering them, one by one, into Access.
1. ID Field – Access enters an ID field for you. Each table in Access should have what is called a “primary key”, or a field that is unique for each record. This helps the database to perform better and prevents duplicate records. In our case, we could have two contacts with the same first name, or the same last name, or the same value in any of the fields we are using so we don’t have a unique field. We’ll use the default ID field that Access provides.
2. Other Fields – Enter the field names by clicking on the box that reads Click to Add, then selecting Text, entering the field name and then pressing the Enter key. Use an uppercase letter for the first letter in each word part and no separation between parts. For example, use “FirstName” rather than “first name”. If you really want the separation, use an underscore (_) rather than a space because it will make life easier for you down the road. The fields to enter are: FirstName, LastName, Street, City, State, Zip, Phone, and Email. To change the text, you can right click on the downward arrow and select Rename, or you can double click on the field. When you are finished, your screen will appear as shown below:
3. Save – Now that all of the fields have been entered, save the table by clicking the File tab and then selecting Save. You can also click on the Save icon on the quick toolbar. You will be prompted to enter a name for the table. The default name of “Table1” will be suggested, but let’s change it to “ContactInformation” so that we know what type of information is stored in the table. Use the same formatting rules as for fields. Click OK.
4. Design View – To view the table in design mode rather than datasheet mode which we are currently using, click on the View button in the top left corner or the same icon found in the very bottom right corner of the screen. Go ahead and click on the other icons found in the bottom right corner to see the PivotTable and PivotChart views as well.
5. Datatypes – From design view you can see all of the fields more easily and can change the datatype as well. It’s best to change text fields to be of the appropriate datatype (e.g. Date/Time for dates) and to be only as big as needed (the default is 255 characters in length). For the purposes of this exercise, let’s leave them as text.
Before we create and print a report, let’s add some data to our database so that we have something interesting to view on the report. If you want, you can enter the same data that is shown following step 6 of this section.
1. Go to the Datasheet View by clicking on the icon.
2. Since the ID field is a number that is automatically incremented for us, we don’t want to enter anything into that field.
3. To prepare to enter a first name, tab to the next field, or place your cursor in the FirstName field and click.
4. Type a name into the FirstName field. Notice that when you type the first character, a few things happen:
1. A new blank record is created below the one you are typing into,
2. the asterisk is now on that line and the line on which you are typing has a pencil icon to indicate that editing is in progress,
3. and the ID field changes from to .
5. When you are done typing the first name, tab to the next field and enter the last name. When you tab off of the last field (Email), you are taken to a new row in which you can enter information for a second contact. Also notice that the pencil now moves to this row and that row one has a blank where it previously had the asterisk. This means the first record is now saved in the table. While the pencil is still on the row, you can undo the entry and it will not be saved in the table. This can be done by pressing the undo icon or pressing the ESC key.
6. Continue until you have a few records in the table. When you are finished, your screen will appear as shown below.
It’s fast and easy to create a simple report for your data using the Create tab.
1. To create a basic report of your information, click on the Report icon. You can also select Labels, Blank Report, or Report Wizard for additional reporting options, but for the purposes of this training, the basic report is fine and will provide you the following report as an additional tab next to your table.
2. You’ll notice that the information doesn’t all appear on the page and that the Format tab now displays on the ribbon.
3. To clean up the report to appear as shown below and fit on one page, do the following:
1. Under the Home tab, in the views section, click on the bottom half of the view icon, and select Layout View. Or right click on the ContactInformation text and select Layout View. This will enable you to edit the report.
2. Double click on the ContactInformation text and notice that you can insert a space between the words or change the report title however you like.
3. Click on the column header ID, right click, and select Delete Column to remove that information from displaying on the report.
4. Click on the FirstName column header, move your mouse to the right edge of the box that appears until it is a double-arrow, click and drag the box so it is narrower. Repeat this for the other fields so that the information fits on one page.
4. To save the report, click the File tab and then selecting Save. You can instead click on the Save icon on the quick toolbar. You will be prompted to enter a name for the report. The default name of “ContactInformation” will be suggested since that is the name of the table on which the report is based. That name is fine so click OK.
You can print tables, reports, and other information from within Access.
There are two commands that you will want to run regularly to keep your database working well and backed up. Both of these commands are found under the File tab.
Access 2007 and Access 2010 use the same file format. However, there are enhancements in Access 2010 that are not available in Access 2007. For detailed information on the differences and the changes that can be made so an Access 2010 database can be used in Access 2007, see Backward Compatibility between Access 2010 and Access 2007.
If you need to share a database with someone who does not have Access 2007/2010, you will need to save your database in the older file format. To save your database as an Access 2000 database, click Save & Publish under the File tab. Under File Types select Save Database As, then select Access 2000 Database, and click Save As. A window will appear asking for the location and name you would like to use. If you aren’t certain whether you should copy the database or change the existing database to an Access 2000 database, contact either the person who you are working with or the Technology Help Desk.
Similarly, if you open a database that is from an older version of Access, such as Access 2000, you may receive a message. If you do, and you aren’t certain how you should respond, click Cancel and then contact either the person who created the older database or the Technology Help Desk.
Although there is an add-on for older versions of Word, Excel, and PowerPoint to allow users to open Office 2010 documents, there is no such add-on available for older versions of Access.
Access and other 2010 Office programs give you the option to customize the Ribbon in order to have quick access to commands you use most often, such as save, or cut. Now let’s say you want to have the Quick print command right under your eyes for easy access. To do this:
You can always go back later and delete your created tab by selecting it from the Customize the Ribbon box, and click on Remove. Note: your created groups or tabs will have (custom) next to them, although the word custom does not appear on the ribbon.
Access 2010 has a built-in help system. Hit the F1 key, or click the question mark on the upper right part of the window to access it.
Since Access 2007 and Access 2010 are very similar, Access 2007 information is still useful.