Databases in Microsoft Access 97

ET 605

Dr. Marcovitz

Contents

Introduction

Microsoft Access is a very powerful relational database tool. Hopefully, this document will guide you through some of the basics so you will be able to perform many of the simple tasks you might want to do with a database. With this document, a little help, and a little practice, you should be able to understand how to do many basic things. That will be 5% (at best) of what Access can do. If you want to be able to take full advantage of all the power of Access, you might spend some time with a good Access book, such as Sams' Teach Yourself Access in 14 Days by Paul Cassel.

Access is a professional-level tool used by people who maintain large, complex databases. Your school district, for example, probably uses a tool comparable to Access to keep track of student and personnel records. However, Access is also good at maintaining small databases, such as your class gradebook or your students' collection of rocks and minerals.

Starting a New Database

When you first open Access, you should tell Access you want to create a new database. It will ask you to name the database. Be sure to save the database in a reasonable place, possibly on your own disk by paying attention to the Save In box at the top of the screen. Access databases end with the extension .mdb, so you can include that extension when you name your file, or you can let access automatically add that to your file name for you. Since the database in the example in this paper will be a sample grades file, you can name your database grades.mdb.

Tables

Access stores data in tables. A simple database program will only allow you to have one table for all your information in the database. Access will allow you to create several tables and relate them to one another. This can be very powerful, but it is an advanced feature. In this paper, we will limit ourselves to one table.

When you open Access, you will see the following screen:

The tabs at the top allow you to look at any of the tables, queries, forms, reports, macros, and modules that you have created for this database. Nothing is showing in the window because we haven't created any of them yet. Be sure that the Table tab is selected and click the New button to create a new table. Choose Design View and click OK. You should be presented with the following screen:

This is the screen where you define your fields. Fields are the kinds of information you want to store. For example, if your database is students, you might want to store each student's name, birthday, and grades, so you would want to create the following fields: FirstName, LastName, Birthday, Grade1, Grade2, and Grade3.

Next you have to decide what the data type will be for each of these fields. The most common field types are: Text, Number, and Date/Time The FirstName and LastName fields will be of type text (you could put them together in one field as long as you put last name first). The birthday will be a date, so that should be of type Date/Time if you are going to use a complete date (including the year). If you are using just the month and day, the birthday should be of type Text. The three grades are numbers, so you should use type Number.

In the screen above, there is one column for the Field Names and one column for the Data Type (the other column is for you to enter a description of each field if you want to; you might want to describe what each of the grades represent, for example). If you click in the first row under Field Name, you will be able to type the first field name: FirstName. You can hit Enter or Tab or click in the box next to it to set the Data Type. When the Data Type area is active, you will see an arrow indicating that you can use the pull-down menu to select the Data Type. Click once on the arrow and select Text from the list. Hit Enter or Tab or click in the next box, and your screen should look like this:

The bottom half of the window will change to indicate Field Properties that are appropriate for a text field. For the most part, you can ignore these; they are for advanced users. The only property that interests us is the Field Size property. For text, this tells us how many characters (letters, numbers, spaces, punctuation marks), the field can hold. You want to be sure that this number is big enough to hold the largest thing you might put in that field. The field size is probably set to 50, which should be plenty of space to hold the longest first name, so leave it alone.

You can type a description if you want to and then get your cursor (by hitting Enter, Tab, or clicking) into the second row under Field Name. You are now ready to enter the rest of the field names and types. When you are done, your screen should look like this:

If you made a mistake in typing a name or setting a Data Type, simply click in the box where you made the mistake and fix your mistake.

There are two Views for Tables. That is, you can look at Tables in two different ways. You can look at tables in Design View, which is the way we have been looking at our table. This view, as the name suggests, is for designing the table; i.e., setting up the fields we want in the table. The second view is Datasheet View. This is the view you can use to look at and enter the data for the table. We will switch to Datasheet View to enter our students' names and grades. To switch to Datasheet View, select Datasheet View from the View menu.

When you do this, Access will ask you if you want to save the table. Click Yes to save the table and give it a name that makes sense to you, such as Grades. In Access, each element of the database has its own name, so every time you create a table or a query or a report, you will have to name it. Always pick a name that makes sense to you, so you will be able to refer back to it later.

Next, Access is going to complain that you haven't assigned a Primary Key. A primary key is important in large databases with multiple tables. The primary key is a field that contains something unique for each record in the table. For small, single-table databases, a primary key is not necessary. Click on No to tell Access that you do not want a primary key.

No, you should be in Datasheet View. This is a table with rows for your fields and columns for your records. It is the easiest place for the Access beginner to enter data (when you get to be an advanced Access user, you will probably create Forms for entering your data). Your screen should now look like this:

Notice that all of your field names are across the top of the window as headers for each column. Notice that at the bottom of the screen is a small control panel labeled "Record." This control panel allows you to move from record to record and tells you, in the box, which record you are currently editing. Finally, notice that the fields Grade1, Grade2, and Grade3 already have values of 0. That is because Access automatically sets a default value of 0 for numbers; the default value is the number that will show up when no number has been entered. This can be convenient or it can be a pain.

You might not want to do this for a gradebook because you might not want to assign your students a grade of 0 before they have turned in an assignment. If you want to eliminate the default value, go back to Design View (by choosing it from the View menu), click on Grade1 and delete the 0 from the Default Value property (in the property list at the bottom of the window). Repeat for Grade2 and Grade3, and switch back to Datasheet View.

You can now start entering your data. Click in the first row under First Name and type John. Hit Enter or Tab or click in the next box and type Smith. Hit Enter or Tab or click in the next box and type 6/7/87. Type 100, 98, and 89 for the grades. When you hit Enter or Tab at the end of the first record, Access will automatically take you to the second record. Keep typing the data until your table looks like this:

If you made a mistake, you can always click in the box where you made the mistake and correct it.

You might want to adjust the widths of some of the fields. Notice that Jackie Kennedy-Onassis' last name does not fit in the last name column and several of the other columns are much wider than they need to be. You can fix this by moving the mouse cursor after the name of the field you want to make wider or narrower. When the cursor is right between the field name and the next field name, it will change to a vertical bar with arrows pointing to the right and the left. When the cursor takes this shape, hold the down the left mouse button and drag the line to the left to make the field narrower or the right to make it wider. Alternatively, you can double-click the left mouse button and Access will make it just the right width to fit all your data.

You have now entered your data. You can always come back to add more data or to change data. For now, we will leave the table. Click on the close box in the upper right hand corner of the window (that's the X). When Access asks you if you want to save your changes, click on Yes. Access automatically saves the new data as you enter it. You are actually saving the changes you made to column widths.

You should now be back in the main view of your database, where you can choose to look at the Tables, Forms, Queries, etc.:

Notice that if you click the Tables tab, you will see the Grades table that we just created it. If you ever want to make changes to the Grades table (including adding more students, changing the information for some students, or changing some of the fields), you can do that by clicking on the Grades table and clicking on the Open button. Remember that if you want to change the data, you need to be in Datasheet View, and if you want to change the field names, types, or properties, you need to be in Design View.

Queries

Now, you have entered some data into the database, but you haven't done anything that wouldn't have been just as easy in a word processor. The power of the database comes through when you start using it to ask questions (queries). The first kind of question we want to ask is about each record in our database. We want to be able to calculate the average grade of each of our students. We will also want to sort our database and pick out certain records from our database.

If you are in Design View or Datasheet View for the table, click on the close box to get back to the screen shown above. Click on the Query tab. You should see a blank window because we have not yet created any queries. Click on New to create a new query. Click on Design View and OK to begin creating your query in Design View. You will notice that queries have Design View and Datasheet View, just like tables, and Datasheet View looks almost identical in each.

Next, we need to tell Access that we want to get our data from the Grades table that we just created. A Show Table window pops up and lists the Grades table. Click on the Grades table to select it and then click the Add button. Close the Show Table window by clicking on the Close box. Your screen should look like this:

At the top of the window you see a scroll box with all the fields from the Grades table listed. At the bottom of the screen you see a table. Drag each of the fields from the list of fields at the top to a separate column in the table at the bottom. You can use the scroll bar at the bottom to scroll over and see more columns when you use up all the columns that you can see. When you are finished, your screen should look like this:

Each column has the field name and the table from which that field comes. In our case, all the fields come from the Grades table.

You have now set up your first query. In the Query menu, choose Run. Your query should look just like the Datasheet View of the table. That's because the question you asked was simply to show all the fields in the Grades table. Select Design View from the View menu to take you back to the query so we can ask a more interesting question.

Calculations

Access has several ways to perform calculations. When you get more advanced, you might want to perform some calculations in Reports, but we are going to start by performing calculations in a Query. The advantage of performing calculations in a Query is that you can easily base other Queries on your calculation, and if you want fancy output from a Report, you can base a Report on the Query as well.

In Design View of the query, scroll to the right until you can see the first empty column. Click the right mouse button in the Field box and select Zoom from the menu. This will bring up a larger window to make it easier to type our long expression. In the larger window, type the following

FinalGrade:(Grade1+Grade2+Grade3)/3

FinalGrade is going to be the name of the column (it acts like a field name). What comes after the colon is a formula to calculate the average of the three grades. This formula could be any mathematical expression, including ones that use some powerful mathematical functions that come with Access. For now, we will use this basic formula for calculating the average. Your screen should look like this:

Make sure you type your field names (Grade1, Grade2, and Grade3) exactly as you typed them when you created the fields. Access is picky about misspellings. Also, be sure your field names are in square brackets because that tells Access that they are field names. If you got the formula right, click OK. Now run your query again by picking Run from the Query menu.

Your query should look the same as the first time you ran it, except that you have an extra column. The column is titled FinalGrade, and it contains the average of the three other grades for each of your students. Your query should look like this:

There is one more thing that we want to do with this query. We want to limit the number of decimal places that are shown in the FinalGrade. Go back into Design View (by choosing Design View from the View menu). Click the right mouse button on the FinalGrade column and choose Properties from the menu that pops up. Click in the box marked Format, and your screen should look like this:

Click on the arrow that shows up in the format box and select Fixed from the menu. Click in the Decimal Places box and enter the number 1 to limit what is shown to 1 digit after the decimal point. Your screen should look like this:

Click on the close box of the window and run your query again. It should look exactly the same as the last time except that all the FinalGrades are showing one digit after the decimal point.

Click in the close box of the window and click Yes when asked if you want to save the query. Give the query a name that makes sense, such as Final Grade. You should now see FinalGrade listed as a Query in the database. If you want to check to see that your table is still there, click on the Table tab, and you will see Grades listed there.

Sorting

When you think about the records in your database, you should not think of them as existing in any particular order. Right now, they are in the order in which you typed them, but that might not be the most useful order. Sorting allows you to put your records in any order you choose. For a database of ten students, this might not be particularly useful, but imagine you are a high school teacher with 150 students. Suddenly, automatic sorting becomes a lot more useful. Imagine you are your school's registrar with information from 1000 students. Now sorting (and selecting which we'll explore next) becomes very important.

Access provides many ways to sort a document. The simplest way to sort is in Datasheet View. Simply click on the field on which you want to base the sort and go to the Records menu, choose Sort, and choose either Sort Ascending or Sort Descending. That is, if you want your records in alphabetical order, go to Datasheet View of your table or a query, click on the LastName field and choose your sort from the menu. Sort Ascending will sort from lowest to highest (or A to Z, which is regular alphabetical order), and Sort Descending will sort from highest to lowest (or Z to A, which is reverse alphabetical order).

This works very well for a simple sort, based on one field. This does not work as well if you need a more complex sort based on more than one field. Unfortunately, for an alphabetical sort, you might need a more complex sort. Notice in the data we are using, there is an entry for John Smith and an entry for Timothy Smith. If we just sort on LastName, we do not know which will come first-they are both Smith. To break the Smith tie, we need to use FirstName as a secondary sort order. That is, we want to sort everything based on last name, but if there are last names that are the same, we want those records to be sorted based on first name. John Smith should always come before Timothy Smith.

To do this, we will need a Query. Go to your main database window, click on the Query tab and click on the New button to get a new Query. Select Design View and click OK. Now, you will be asked on which tables or queries to base this new query. Since we want to see all of our data, including our calculated grades, we can base this query on the FinalGrades Query that we created to calculate the final grades. In the Show Table dialog, click on the Queries tab, click on FinalGrades, and click on Add. Now close the Show Table dialog. So far, this is the same as what we did to create the FinalGrades Query, except that we are using the FinalGrades Query instead of the Grades Table. Your screen should look like this.

Just as we did to create the Final Grades Query, you want to drag each of the fields listed in the top half of the screen to a column in the bottom half of the screen. Note that you don't need to include all of the fields. For example, if you just wanted to include the students' names and final grades, you could just drag those fields to the table at the bottom. When you switch to Datasheet View, only the fields you add to the table will show up for this Query.

Notice that the third row of the table at the bottom is labeled Sort. If you click in the sort row for any field, you will get a pull down menu. From that menu, you can choose Ascending, Descending, or not sorted:

Select Ascending for the LastName field. Run the query by choosing Run from the Query menu. You should now see all the records sorted by last name:

Unfortunately, we still have a problem with the two Smiths. Fortunately, a Query allows you to add a second sort criterion. We will add FirstName as the second sort criteria. Return to Design View (select Design View from the View menu) and click in the Sort row for FirstName. Set that to Ascending and run the Query.

Uh oh! Access has sorted based on the first name. That is because whatever appears first in Design View is the first criterion to use in the sort. What we need to do is go back to Design View and put the LastName field before the FirstName field. To do this, select Design View from the View menu and click on the LastName column to highlight it. Drag this column to the left of the FirstName column and let go. LastName should now appear before FirstName in the table. Run the Query again. Now, all your records should be in the correct order.

Click on the close box of the query, and click Yes when asked if you want to save changes. Give this query a reasonable name, such as AlphaSort. You should now see two queries in your main database window: AlphaSort and FinalGrades. Both of these queries have the same data, but AlphaSort has the data sorted alphabetically.

You can define several different queries for several different sorts because sometimes you will want to look at your records in one order, and sometimes you will want to look at them in another order. For example you might want to look at your records in alphabetical order when taking attendance, and you might want to look at your records in order of FinalGrade from best to worst to decide who should get the Outstanding Student award for your class, and you might want to look at your records in order of Final Grade from worst to bets when deciding who should get a deficiency report, and you might want to look at your records in order of birthday when deciding who gets to cut the class birthday cake, and Ö

Selecting

Sometimes you will only want to see certain records. For example, you might want to pick out the students who are doing poorly to set up a conference with their parents. With only 10 students, this is easy without a database. With 150 students, this is more difficult. If you are a school registrar trying to pick out the students who are listed as seniors but do not have enough credits to graduate, this could be very tedious without using the power of the database to make a selection.

In Access, selections are also done with a Query. Get a new Query in Design View and use all the fields in the FinalGrades Query (just like what we did originally to set up our Sort). Notice that under each field, there is the Sort row (which we used to set up sort criteria). Under the Sort row is the Show row (ignore that). Under the Show row is the Criteria row. This is where we set up our selection criteria.

In the selection row, you can enter formulas to limit which records the Query will show. These formulas can be fairly simple or very complex. You can try entering a few expressions in the criteria for different fields. Here are some examples to try (when you try one selection, be sure to delete the criteria you set for previous selections):

Note that after you enter your criteria, Access might alter them slightly. That is because Access is very picky, but it is also fairly smart. It wants your selection criteria to be in an exact format (with quotes and spaces and special characters in exactly the right places), but it can usually figure out what you mean, so after you enter an expression, it alters it to what it really wants.

Let's walk through setting up one simple selection. If you tried some of the examples above, delete your selection criteria by dragging the mouse across the criteria and hitting the Delete key. We are going to select only those students whose final grade is less than or equal to 75. If you are not in Design View, select Design View from the View menu. If your window is not wide enough, you might need to scroll to the right to see the FinalGrade field. In the Criteria row for the FinalGrade field, type <=75 as shown below

Choose Run from the Query menu, and you should see two records: William Gates and Charles Kim because they are the only ones with grades less than or equal to 75.

Click on the close box for your Query, say Yes to saving it, and give it a name that makes sense, such as DoingPoorly. You should now see three queries in your main database window: FinalGrades, AlphaSort, and DoingPoorly.

Using queries to make selections can be very powerful because you can pick out records based on any criteria you choose. For simple searches, you could perform a sort and look at the appropriate place on the list, but for some searches (especially complex searches with two or more criteria), that will not work.

Relational Databases

Microsoft Access is a very powerful relational database tool. In the example in this paper, we have used a single table to store all of our data. For complex data, Access allows you to create several tables and use forms, queries, and reports to relate them. This can be very powerful, but it can be very complicated and is beyond the scope of this paper. If you find that your needs are not met by the skills we have covered in this paper, you might want to find a good book to learn some of the more advance features of Access.

The method we have used to create a gradebook, with one table for all our student information and grades will probably suit your needs. However, we could also create a table for student personal information, another table for attendance, another table for student assignments in which each student would have an entry for each assignment, another table that lists the assignments, etc. Each table would be related to the others and verified with the others. Output would be made through queries and/or reports that show just the information you want to show for a particular purpose. This would be very powerful and very useful, but it would be very difficult to set up and probably not worth the effort for a small number of students. However, the more complex your grading, attendance, and reporting requirements are, the more worthwhile such a complex system would be, and Access has the power to do all of that.

Conclusion

Databases are very powerful tools for organizing lots of information. The example used in this paper shows you how to make a simple gradebook (your real gradebook would be more complicated), but you could use databases for any information. If your students are collecting information about insects or rocks, they can put that information into a database and easily find out which insects have wings or which ones have more than 6 legs. For organizing large quantities of information, nothing beats a database.


Return to ET605 Home Page.


This page was prepared by Dr. David M. Marcovitz.

Last Updated: October 7, 1998