BEGINNING ACCESS

BEGINNING ACCESS 2000

Microsoft Access 2000 is a database program that integrates with other MS programs. Databases are collections of information about an entity, such as all the students enrolled at a school or all customers of a company. Other examples include the phone book or your address book. Each Access database is comprised of 6 objects: tables, queries, forms, reports, macros and modules.

Table	A datasheet where all of the information can be viewed in columns and rows.
Query	Used by Access to pull out "subsets" of information that meet certain criteria, such as all the students who live in Henry county, or all the students with a certain GPA.
Forms	Provide an alternative way to maintain records in the database.
Reports	Provides a professional looking hard copy of a table or query.
Macro	Automates repeated tasks.
Modules	Visual Basic for Applications declarations and procedures.

Tables form the basis for all of the other objects. A table is composed of records - all the information about one person, company, or entity. Each record is made up of fields. Examples of fields might be First Name, Last Name, Address, City, State and Zip. Each record has the same fields in the same order. If you do not know the information for a particular field, you can leave it blank (most of the time). However, the field still has to be in the record. A table in Access looks very much like an Excel spreadsheet, with field names at the top of the column and records in each row.

You may be thinking that if an Access table is just a list of data in columns and rows, why not just use Excel? Think of Access as being three dimensional and Excel as two dimensional. Access is a relational database management system, whereas Excel is known as a flat database. Through the judicious use of field names, you can define a relationship between tables in Access. Relationships help you avoid the following problems:

1.	Repetition. Why enter the information twice if it already exists in another table?
2.	Errors in maintaining the information. Updating one table is a lot easier than updating the same information in 5 or 6 different places.
3.	Duplication: By specifying a field as the primary key, you avoid entering the same information twice.

USING A Primary Key

If you plan to relate two or more tables, you will have to use a primary key and a foreign key in the tables. Let's look at an example of two tables: one called Student Info that contains personal information about the student, and the other called Student Schedule that contains the class information for the student.

Student Info		Student Schedule
SSN		SSN
FirstName		FirstPeriod
LastName		SecondPeriod
Address		ThirdPeriod
City		FourthPeriod
State		FifthPeriod
Zip		SixthPeriod
HomePhone

Notice that SSN appears in both tables. In the Student Info table, SSN is known as the primary key. SSN is the foreign key in the Student Schedule table. The primary key field name and the foreign key field name must be identical. In addition, both keys must have the same data type, i.e.: text, number, etc.

First names and last names might work for primary keys, but suppose you have two Bill Smiths who attend the school? Phone numbers might be another good choice, but suppose two members of the same family attend the school? Social security numbers are an excellent choice for primary keys - because no one else has the same number!

Once you establish a relationship between these two tables, you can choose fields from each table to create queries, forms and reports.

Why not just put all the information in one big table? You could, but then it would be ONE BIG TABLE and very cumbersome. Also, parts of the school are not interested in all of the data. The instructors, for instance, do not need to know where a student lives, just who is going to be in their classes.

You may also have noticed that there are no spaces in the field names. This is the common convention for naming fields, mostly because it saves space in the columns.

PLANNING

Planning the database is just as important, if not more important, than entering the records. If you are creating a database, you should meet with everyone who will be using the database to find out what information they are going to need, how the fields should be defined, how the tables should relate, etc. It is much easier to do this right the first time than to have to go back and re-do after you have already started entering information into the table! Do not scrimp on the planning time!

The Student Info table might look like this:

STUDENT INFO TABLE

Field Name	Data Type	Description
SSN	Text	primary key
FirstName	Text
LastName	Text
Address	Text
City	Text
State	Text	Use 2 character abbreviation
Zip	Text	Use 5 digit zip code
HomePhone	Text	Include area code

Why choose the text data type for SSN, phone numbers and zip codes? These items are known as alphanumeric - meaning a combination of text and numbers. You define them as text because you would never plan on using them in any kind of mathematical expression. Other data types include: numeric, data/time, currency, memo, OLE, AutoNumber, yes/no and Lookup Wizard. Descriptions are for your use in remembering information about each field. It is not necessary to enter a description.

EXERCISE:

1.	Start Access by clicking on Start, Programs, MS Office, Access.
2.	Open a blank database. Access will immediately want to name this database. Click in the File name text box, and key School. Access will automatically add the extension .mdb to indicate an Access database. Databases are named according to Windows '95 file naming rules.
3.	Click on Create. The School database window should open. Notice the six object tabs across the top of the database window. You can click on each of these object tabs to bring those objects into view.

CREATING THE TABLE
1.	To create the Student Info table described above, click on the Table tab.
2.	Click on New. The New Table dialog box should open.
3.	Select Design View, then click on OK. The design view window should open.

Tables have two views: design and datasheet. Design view defines the structure of the table - rather like a bookshelf. The datasheet view shows the records in the table - like the books on the bookshelf. Although the records can be entered into the table before you design it, in most cases, you will design the table first, then enter the records.

This is the information we are going to enter:

Field Name	Data Type	Description
SSN	Text	primary key
FirstName	Text
LastName	Text
Address	Text
City	Text
State	Text	Use 2 character abbreviation
Zip	Text	Use 5 digit zip code
HomePhone	Text	Include area code

Use the tab key to move from column to column. In the Data type column, you can either key in the first letter of the data type or click on the list arrow that appears in the column and select the data type.

1.	Key SSN under Field Name in the first row. Press the tab key.
2.	Press the "t" and text should appear in the data type. Press the tab key.
3.	Key the description, if there is one. Press tab to go to the next row.
4.	Repeat these steps until all field names, their data types, and descriptions are entered.
5.	Click on SSN. Your design view should now look like this:

SETTING PROPERTIES

You will also need to decide on properties for each field. These depend greatly on which data type you have chosen. Text and Number, for instance, are the only two data types where you can choose the field length. All the rest of the field lengths are set for you depending on the data type you chose.

Can you change the properties after you have entered data? It depends. Certainly you could make a field longer without any problems. However, if you shorten a field you run the risk of losing data. You could create a default after entering data, but you might have to update records that you have already entered. Again, planning is essential!

These are the properties for a field whose data type has been defined as text:

Field Size	Limits the number of characters that can be entered into this field.
Format	Selects how you would like dates, numbers, time and currency to appear.
Input Mask	Allows you to "preset" the formatting on some items such as a phone number. The ( )'s and the - will be inserted for you by Access, thus greatly reducing your keystrokes.
Caption	Allows you to enter a "nickname" for the field. We could have named the SSN field SocialSecurityNumber and used a caption of SSN.
Default value	If you expect that most of your records will contain certain information, you can set a default value that will appear in the record automatically unless you enter something different. Again, this reduces your keystrokes.
Validation Rule	Limits the values that can be entered.
Validation Text	Error message that appears when the validation rule is not met.
Required	If you choose yes, you MUST enter a value in this field.
Allow Zero Length	Specifies whether a zero-length string (" ") is a valid entry in a table field.
Indexed	No(Default) No index. Yes (Duplicates OK) The index allows duplicates. Yes (No Duplicates) The index doesn't allow duplicates.

EXERCISE:

Set the following properties for each field name:

SSN	Field Size: 12 Required: yes (since this is going to be our primary key)
FirstName	Field Size: 25
LastName	Field Size: 25
Address	Field Size: 50
City	Field Size: 25
State	Field Size: 2
Zip	Field Size: 5
HomePhone	Field Size:15

When you have completed setting these properties, save the table by clicking on the Save icon. Name the table Student Info. If you are asked about creating a primary key, choose No. Object names, such as tables, queries, forms, and reports may have up to 64 characters and may include spaces.

Now that you have created the design, you may enter data into the table.

EXERCISE:

Change to the datasheet view of the table from the design view:

1.	On the standard toolbar, click on the View icon. It is the first button on the left side. It toggles between the design view and the datasheet view. Your screen should look like this: Each column represents one field in the design view, and each row represents one record.

The triangle on the left side represents the current record. As you start to type information, the triangle will change to a ! , indicating that you are in the process of editing a record. When you are adding a new record, the next row will change to a *, indicating the next available row. This way, Access always stays one record ahead of you.

When you complete a row and move to the next row, Access immediately saves the record you have just completed, thus preventing data loss.

As you enter information, move from column to column by pressing the tab key, or by pressing Enter. Shift+Tab moves you back to the left. You can also use the horizontal scroll bar to move the fields to the right or left, then point and click in the column you want to edit.

Even with the best planning, tables may need some fine tuning to make them usable. It's a good idea to try adding a few records just to see how the table is going to work.

EXERCISE:

Add these two records to your table:
SSN: 999-98-1789
Sam
Smith
198 Cobblestone Rd.
Atlanta
GA
30345
(404)776-4646

SSN: 171-77-1828
Frieda
Little
1777 Surrey Court
Atlanta
GA
30311
(770)716-4625

Consistency is important - notice that the state used the 2 character abbreviation, each zip code was five digits, and the phone numbers include the area code. All of these things were stipulated in the table design, and while they are not required, they do create clear, logical records.

Your datasheet should now look like this:

wpe4.jpg (11955 bytes)

MOVING AROUND THE DATASHEET

The bar at the bottom of the datasheet window contains navigation buttons that tell how many records you have and which record you are currently on. Remember - there is always one extra row, so even though it says 3 of 3, we actually only have 2 records.

If you can see the record you want to edit, you can always point and click. Otherwise, you may need to use the vertical scrollbar or the buttons on the navigation bar.

wpe1.jpg (9113 bytes)

CHANGING THE DATASHEET COLUMN WIDTH

You also may have noticed that the address column is not wide enough to show the entire address. This has nothing to do with how many characters we set as the length when we were defining the table. We set the length as 50, after which no more characters will be allowed. However, the width of a column in the datasheet defaults to 1", so we are only seeing the first 1" of the address. You can change the width of a column by pointing the mouse to the lines between the column headers. When the mouse pointer changes to a double headed arrow, click and drag to the left or right. You can also double-click to autofit the column (this works exactly the same in an Excel worksheet or a Word table.)

EXERCISE:

Autofit all columns by pointing to the divisions between the column headers and double clicking. Your datasheet should look like this:

wpe6.jpg (12622 bytes)

We did not set a primary key in our design view. It would be a good idea to do this before adding any more records.

SETTING THE PRIMARY KEY

EXERCISE:

1.	Return to the design view by clicking on the View icon.
2.	Click on SSN in the field names.
3.	Click on the key icon on the standard toolbar. A small key should appear just to the left of SSN. The SSN field has been designated as the primary key.
4.	Change the Indexed field to Yes (no Duplicates)
4.	Click on the save icon and return to the datasheet view.

By choosing SSN as the primary key, setting required to yes, and allowing no duplicates:

There can be no duplications in the SSN field. A primary key is unique. This will keep us from entering the same student twice.

Since we set the Required property to yes, the SSN has to be entered. This may seem unnecessary, but in the case of the School database, all student records are saved by SSN, so we must have this information.

Records will be sorted by SSN.

EDITING/ADDING/DELETING RECORDS

The easiest way to edit a record is to use the navigation buttons to move to that record, then point and click with your mouse. To add a new record, click on the new record icon on the navigation bar, and key the new record.

To delete a record, select the entire record by clicking on the row indicator for that record, then press the delete key. Access will ask for confirmation, click OK. Be careful! Deletions are permanent - there is no undo!

EXERCISE:

1.	Change the address for Frieda Little to 1777 Surry Court (remove the e from Surrey)
2.	Add this new record: SSN: 262-62-1854 Chris Jackson 180 Smith Ave. Apt. 15B Morrow GA 30260 Notice that Chris does not have a phone number. Although we really need this - it is not a required field. Therefore, leave it blank.
3.	Delete the record for Sam Smith.
4.	Autofit the Address column again. Your datasheet should look like this:

CAUTION: As you are keying the records, you may accidentally begin a new record. Since we have set SSN as required, Access will not allow you to proceed until you provide a SSN. You can not delete the accidental record, you can not edit other records - in other words, you are stuck until you deal with this. Make Access happy by entering a bogus SSN. Then you can delete the record.

EXERCISE:

1.	Click on File, Close to close the School database.
2.	Click here to download the New School database.
3.	Click on File, Save As and save the New School database in your My Documents folder.

The New School database contains the same table you just created with 10 records added, and an additional table named Student Schedule. We will use these two tables to establish a relationship, then create a query and a report.

CREATING A RELATIONSHIP

In order to use more than one table in a query, you must create a relationship. Our relationship will be based on the SSN field in the Student Info table (primary key) and the SSN field in the Student Schedule table (foreign table).

There are three kinds of relationships: one-to-one, one-to-many, and many-to-many. Access will decide what kind of relationship to create based on the records in the tables, but you should know what kind of relationship to expect.

RELATIONSHIP	DEFINITION	EXAMPLE
One-to-one	One record in the primary table matches one and only one record in the foreign table.	If students were allowed to take only one class, and every student was required to take a class.
One-to-many	One record in the primary table matches more than one record in the foreign table.	A student takes a full load of classes.
Many-to-many	Multiple records in the primary table match multiple records in the foreign table.	This could not exist in our database, since we have set a primary key to assure uniqueness. However, had we not set a primary key, we could have had many students with the same name taking many classes, i.e. three students named Bill Smith taking full loads.

EXERCISE:

Creating the relationship

1.	Make sure the New School database window is open.
2.	Click on the relationship icon on the standard toolbar.
3.	Click on the Student Info table, then click on Add. The Student Info table should appear in the relationship window. Repeat for the Student Schedule table, then close the Show Table dialog box.
4.	Click on SSN in the Student Info table and drag to SSN in the Student Schedule table. The relationships dialog box opens when you let go of the mouse. Double check that SSN is listed for both tables, and that the relationship is one-to-many.
5.	Click to select referential integrity. The Help feature defines referential integrity as "a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data." See Help for more on referential integrity.
6.	Click on create. A line should appear joining the SSN fields. The line has a 1 on the Student Info table and the infinity symbol on the Student Schedule table. This indicated the one-to-many relationship. Your screen should look like this:
7.	Close the relationship window. Save the relationship if prompted to do so.

CREATING A SIMPLE QUERY

Access queries "pull out" information that matches a certain criteria, thus answering questions like "who's in Chem101 first period," or "who's taking Acct101 fifth period." There are many kinds of queries; we will create a select query with only a single criteria.

Queries can be created from one table, many tables (if a relationship exists) or from other queries. We are going to use both tables in the query.

EXERCISE:

Creating a simple query

1.	At the database window, click on the Query tab, then click on New.
2.	Click on Design View. Click on OK. The Show Table dialog box should appear.
3.	Click on Student Info, then click on Add. Repeat for the Student Schedule table, then close the Show Table dialog box. (If you ever need to add more tables, click on the Show Table icon on the standard toolbar.) Both tables should appear with their relationship line in the Select Query dialog box.

The bottom part of the Select Query window contains a grid called the QBE - Query By Example. To create a query, you place the necessary fields in the QBE grid, add criteria, sort if desired, then run the query.

If you would like to put all fields from a table into the QBE grid, double click on the table title (Student Info or Student Schedule), then drag any field except the * down to the first available space in the QBE grid. All fields from the table will be added in order to the QBE grid. Our example will use only some of the fields.

1.	Click on the SSN in the Student Info table and drag it to the first row, first column of the QBE grid.
2.	Repeat for FirstName and LastName from the Student Info table, and FirstPeriod from the Student Schedule table. Your QBE should look like this:
3.	Click on the red exclamation point on the toolbar to run the query. You should see the SSN, first name, last name and all classes for first period.
4.	Return to the query design by clicking on the View icon on the standard toolbar.
5.	Click on the Criteria row under the FirstPeriod field. Key in Eng101. Access will add quotation marks around your text automatically. Run the query, and you should see only the students taking Eng101 first period. Didn't work? Return to the query design and double check: Did you key Eng101 exactly as it is in the table - we did not use a space between Eng and 101. Did you? Did you spell out the word English? We didn't in the table. Did you put it in the criteria row for the FirstPeriod column? Any other column, and no records will be returned. Did you use a zero or the letter O in 101? What about the number 1 and the lowercase letter L? They may look the same to you, but the computer knows the difference, and no records will be returned in the query results.
6.	To sort by LastName, return to the query design, click on Sort under LastName, click on the list arrow and choose Ascending from the list. Run the query.
7.	If you would like for the SSN field not to show, return to the query design and remove the check from the Show box in the SSN column. Run the query.
8.	To save the query, click on the Save icon, and name the query Eng101 First Period. Close the query.

CREATING A REPORT

You can create a custom report, but Access has an Autoreport function that quickly creates a presentable report. We will use this to create a report to send to the instructor.

EXERCISE

USING AUTOREPORT

1.	Make sure the database window is visible, then click on the Report tab. Click on New.
2.	Click on AutoReport: Columnar.
3.	Click on the list arrow at the bottom of the dialog box, select ENG101 First Period. Click on OK.
4.	The report should appear in the print preview mode (it takes just a minute to generate, so be patient). To print the report, click on the printer icon on the toolbar.
5.	Close the report by clicking on the X in the top right corner, save if prompted, naming the report ENG101 First Period.