INTRODUCTION TO DATABASES USING MICROSOFT ACCESS
ILLUSTRATION GUIDE
OVERVIEW
The guide illustrates the step-by-step basics of creating and using a Microsoft Access database. The guide will walk you through such areas as creating a database, creating a table and table fields, populating a table with data, modifying data in tables, and creating queries. The guide will also present some basic database terminology you’ll need to become familiar with.
Examples used in this guide are geared towards union organizers. The guide will follow the creation and use of a sample database that could be used during an organizing campaign. The guide applies to users of Microsoft Office 2000.
TABLE OF CONTENTS
Pages
Basic Database Terms……….. 3 – 5 Creating a Database……….. 6 – 8 Creating a Table and Table Fields……….. 9 – 14 Populating a Table with Data……… 15 – 16 Modifying Data in a Table………. 17 Creating Queries………. 18 – 29 Contact Information………. 30
BASIC DATABASE TERMS
Database – A collection of interrelated data.
• Worker’s Personal Information – name, address, phone number, e-mail address, etc.
• Worker’s Job Information – trade/skill, primary occupation, shift, department, supervisor, manager, years of employment, years as apprentice, etc.
• Organizing Interaction with Worker – first date of contact, union support level, number of union meetings worker has attended, etc.
• Managers – relationship with workers, personal information, job information, their interaction with workers during an organizing campaign.
Table – A collection of related data.
• Example using the information from above: A table could be created for worker data and another table could be created for manager data.
• Table data is arranged into rows and columns.
• Each column represents a field.
• Each row represents a record.
Field – A single piece of data
• Examples: first name, last name, address, city, state, zip code, phone number, e-mail address
Record – A group of fields combined to make one complete set of information.
Fields
Record
Primary Key – A field that uniquely identifies a record.
• Examples: Worker ID, Worker Number, Order Number
• A primary key is used to differentiate records that could have similar data in one table. For instance, two workers could have the same first and last names.
• A primary key is also used to define relationships between multiple tables. For example, a worker could be associated with a manager through a Worker ID primary key.
Query – A request for information from a database.
• General Query: Request a record for a worker.
• Specific Query: Request records listing only the last name and address fields of workers that live in Indianapolis.
Database Management System (DBMS) – A program that allows you to create a database, populate a database, modify a database, and query a database.
• Microsoft Access is an example of a database management system.
CREATING A DATABASE
1. In Windows, click on the Start button.
2. Go to Programs >> Microsoft Access
3. In the small box that appears, select Blank Access Database and then select OK.
4. In the resulting small box that appears (see next page), you will need to give a name for the database.
• From the Save in: drop-down list, select Desktop – the location where the database will be saved.
5. In the File name: box, enter the word Campaign1, which will be the name of the database.
When ready, click on the Create button. (Note: Campaign1 is an example used here.)
Once your database has been created, the following box should appear:
For references purposes, the box containing all the options and features above will be referred as the Database Main Menu.
CREATING A TABLE AND TABLE FIELDS
1. In the Database Main Menu, make sure Tables is selected under the Objects bar.
2. Then select Design at the top of the Database Main Menu.
The resulting screen that appears (below) is called Design View. Design View is used to create and modify table fields.
3. To create new fields, type in a name that describes the field (single piece of data) in the Field Name column. An example of a field name would be First Name.
4. The first field entered should be the field that will represent the primary key. The example used in this guide will be Worker ID. Enter the name of the primary key (see illustration on next page).
5. Right-click in the small box just to the left of the field name representing the primary key.
6. Select Primary Key from the drop-down list. This will set the field as the primary key.
A small key icon should show in the small box just to the left of the field name representing the primary key.
7. Once the field name has been entered and the primary key has been set, specify the data type. Ask yourself: Will data contained in the field be:
• Text
• A number
• An auto number (a number that gets incremented automatically for each new record added)
• A Date/Time
• Currency
• A yes/no selection
8. Once the data type is known, make the appropriate data type selection in the Data Type column. Click in the white area – this will bring up a drop-down list. Make the selection from the list.
For the example used above, Worker ID, the data type Auto Number will be selected (see illustration on next page):
9. Continue to enter field names and selecting data types as needed. The sample below
implements nine fields total. NOTE: Once you have defined the primary key, you won’t have to set a primary key for any other fields.
10. Once all the fields and data types have been entered, save the table. Go to File and select Save from the Microsoft Access File menu, which is located at the top, left-hand corner of the screen.
11. In the resulting small box, type a name for the table. For the example used in this guide, Workers is used for the name. When ready, click on the OK button. This action will save the table and fields you created.
12. Close the table Design View by clicking on the ‘X’ located at the top, right-hand corner of the Design View area.
POPULATING A TABLE WITH DATA
1. Return to the Database Main Menu. Since the menu is located in a window within Access, you may need to maximize, or pull up, the window again. Make sure Tables is selected underneath the Objects bar. The table that you just created and saved should show in the white area.
2. Double click on the table name. In this example, Workers would be double-clicked. The resulting screen is the Datasheet View. The Datasheet View is where all data entry takes place. Records are created here. A screen shot of the Datasheet View is located on the next page:
Datasheet View:
3. Create records by filling in the fields with data. NOTE: If one of your fields has the data type of an auto number (as shown above) you do not need to enter data for this field. A number will be automatically inserted when the record is completed and/or data is being entered for the next record.
In the example used in this guide, six records were created:
In the example above, the numbers 1, 2, 3, or 4 are used in the Union Support field. If your table contains a field with a date/time data type (example: First Date of Contact), just enter data for that field in the format of MM/DD/YY.
4. When data entry has been completed, click the ‘X’ button located at the top, right-hand corner of the Datasheet View area. Remember that you can return to the Datasheet View at any time to create or modify records.
MODIFYING DATA IN A TABLE
1. Return to the Database Main Menu. Since the menu is located in a window within Access, you may need to maximize, or pull up, the window again. Double-click on the table that
contains data you want to modify. In the guide’s example, the table Workers would be double- clicked. This brings up the Datasheet View.
2. Simply go into the field containing data that needs to be changed and make the appropriate updates. Updating data in Datasheet View can be compared to making changes in a word document. Updated data will be automatically saved.
CREATING QUERIES
A query is simply a request for information from a database. After a table has been created and populated with data, queries can be created to extract a subset of information such as all
information on workers that live in Indianapolis. The following steps will illustrate how to create basic queries based on the example that has been presented throughout this guide.
1. From the Database Main Menu, select Queries underneath the Objects bar.
2. Select New at the top of the Database Main Menu.
3. In the resulting box that appears, make sure Design View is highlighted. NOTE: Design View in this situation applies to the query we’re about to create. Then, click the OK button.
4. In the resulting small box called Show Table, select the Tables tab. Then select the table that will be involved in the query. In the guide’s example, the table Workers will be selected. Click the Add button and then click Close.
This is what the query Design View should look like after clicking the Add button, then the Close button from the previous action.
As shown above in the bottom pane, there are six areas that can be utilized in creating a query.
Field – Used to specify which fields will be apart of the query (either for display in the results and/or to be used in the criteria).
Table – Used to specify the table that houses the fields that will be apart of the query.
Sort – Used to specify how the records will be sorted in the query results. An example would be sorting records by a worker’s last name A to Z.
Show – Used to specify whether a field that is apart of the query will be shown in the query
results. A field can be used in the criteria, but doesn’t necessarily have to be shown in the results.
Criteria – Used to specify criteria that will limit the number of records returned in the query results.
Or – This area is apart of the criteria that allow you to further limit the number of records returned in the query results. For instance, let’s say we want to return all records for workers that live in Indianapolis or Beech Grove. Indianapolis would be placed in the Criteria area while Beech Grove would be placed in the Or area. Query examples can be seen on the following pages.
TYPES OF QUERIES
There are two types of basic queries you can set up using Microsoft Access:
Queries That Show All Fields:
This type of query would return records with all accompanying fields based on the criteria you specify.
Queries That Show Specific Fields:
This type of query would return records with certain fields specified based on the criteria you select. For instance, you could create a query that returns records with only the First Name, Last Name, and Address fields listed of a worker based on specified criteria.
The following examples will demonstrate how to use the two query types based on the sample workers database we’ve dealt with in this guide.
Queries That Show All Fields
Example: Creating a query that selects records where a worker’s City is Indianapolis.
1. In the Query Design View, select Workers.* from Field drop-down list in the first column.
Regardless of the name of the table you are working with, there should be a selection that has the
* in it. The selection with * in it will indicate that we want to see all fields in the query results.
Note that once the selection is made, the Table and Show areas will be automatically filled in (shown on next page) as long as only one table is involved in the query.
2. Set the criteria. In this example, the City field will be used in the criteria. Select City from the Field drop-down list in the second column. Then enter Indianapolis into the Criteria box in the second column.
3. Uncheck the checkbox for Show in the second column since the query results will already show the City field (since we specified to show all fields in the first column). The query would now look like this:
Note: Microsoft Access will automatically place quotes around any textual criteria as shown above.
4. Now that the query has been set up, it’s now time to run the query. Click on the icon that looks like an exclamation point from the Microsoft Access tool bar at the top.
The query results should look like this based on the example provided:
5. Save the query. Directions and illustrations for this action can be viewed on Page 29.
Queries That Show Specific Fields
Example: Creating a query that shows records with only the First Name, Last Name, and Address fields listed where the worker’s Union Support level is 4.
1. In the query Design View, select First Name from the Field drop-down list in the first column, Last Name from the Field drop-down list in the second column, Address from the Field drop- down list in the third column, and finally Union Support from the Field drop-down list in the fourth column. Union support will be our criteria field used in this example.
Note the Table and Show areas will automatically be filled in for each column where a field is specified as long as only one table is involved in the query.
2. Determine whether you would like to show the criteria field in the query results. Uncheck the box in the Union Support column if you don’t want the Union Support field to display in the query results. In this example, we only want to show the First Name, Last Name, and Address fields, so we’ll uncheck the box.
3. Set the criteria. In this example, the Union Support field will be used in the criteria. Enter 4 into the Criteria box in the fourth column.
4. Now that the query has been set up, it’s now time to run the query. Click on the icon that looks like an exclamation point from the Microsoft Access tool bar at the top.
The query results should look like this based on the example provided. Note the Union Support field is not shown in the results.
5. Save the query. Directions and illustrations for this action can be viewed on Page 29.
SAVING A QUERY
1. While working with the query, go to File and select Save from the Microsoft Access File menu, which is located at the top, left-hand corner of the screen.
2. In the resulting small box, type a name for the query. In this example, First Name Last Name Address is used for the name. When ready, click on the OK button. This action will save the query you created. You can now access this query from the Database Main Menu by selecting Queries underneath the Objects bar.
CONTACT INFORMATION
For questions on Introduction to Databases Using Microsoft Access, contact:
Ryan Diekhoff
Technology Specialist
Indiana University Division of Labor Studies Direct Line: (317) 278-9954
Toll-Free Main Line: 1-800-822-4743 Fax: (317) 278-2280
Email: [email protected]
For questions on the Organize Indiana Project, contact:
David Williams
Lecturer and Non-Credit Program Coordinator Indiana University Division of Labor Studies Direct Line: (317) 274-2518
Toll-Free Main Line: 1-800-822-4743 Fax: (317) 278-2280
Email: [email protected] http://www.organizeindiana.org
