IS223 Microsoft Access 2016 Tutorial 1 PDF

Preview

Summary

Tutorial for homework assignments...

Description

Microsoft Access Lab Session I (Access 2016) Introduction A database is a collection of data that is stored electronically. The person who “owns” the database needs to manage this data, i.e., retrieve data upon request, delete data that is not needed, add new data, and modify existing data. These tasks are enormously complex when the volume of data gets large. A database management system (DBMS) is a program (software) that helps the user manage large volumes of data with relative ease. Terminology Databases are usually designed and implemented according to a fairly standard architecture that is well suited for computer processing. Tables (also called Files): A collection of related data (a file folder); a database consists of one or more related tables. Records: A collection of individual items; many such collections may exist in a table or file (a piece of paper in a file folder). A record runs horizontally, they are similar to having an entry for each student in a class. Fields: A single item in a record; characteristics about an observation (Last Name, First Name, etc). An Example: Consider your address book. Typically, the book contains several addresses and each address has a name, street address, city, state, zip code, and telephone number. If you were to store these data in a database, the entire address book would make up the table or file, each address would be a record, and each item in an address (e.g., name) would be a field. Microsoft Access 2016 The new developed DBMS that we will use is Microsoft Access 2016. This is a windowsbased product that uses a graphical user interface (GUI). It has the capability to embed graphic images within the database. Microsoft Access, like all other commercial DBMSs,

requires that each record within a table have the same structure as every other record in the same table. Once you have opened Microsoft Access… Task 1: Creating and Naming a Database 1. From Microsoft Access startup dialog box, single click Blank Desktop Database. 2. In the File Name text box in the center of the page, type “Bookstore Inventory”. 3. Click on the Create button. Task 2: Creating the Author Table 1. Close the current table by clicking the X in the upper right corner of the window. 2. Under the Create tab, click on Table. 3. Under the Home or Fields tabs above the toolbar, click View and select Design View. (Note: We are building this table from scratch. In a later lab we will use Wizards to create a database and tables.)

4. For Table Name, type “Author” and click OK to save. 5. Under the Field Name column of the table, type “Author Number”, then hit the Tab or Enter key. 6. Under the Data Type column of the table, click on the drop-down arrow to see the available data types. 7. Choose Number, then hit the Tab or Enter key. 8. Type “Author Name” under the Field Name column below where you typed “Author Number”, and hit the Tab or Enter key. 9. Under the Data Type column, accept Short Text as the data type, and hit the Tab or Enter key. 10. For the Next Field, under the Field Name column, enter in “Annual Salary”, then hit the Tab key or Enter key. 11. Under the Data Type for Author Salary, select Currency.

12. We are going to set the Author Number as the Primary Key for this table. Click on the cell where you wrote “Author Number”, and then click on the Primary Key button (a picture of a key that says “Primary Key” on the left side of the toolbar) if it isn’t already selected. A small, gold key icon should be seen to the left of “Author Number”. Note: a “Primary Key” is a unique key to uniquely identify each row in the table.

13. Save this table structure by closing the table (by clicking on the X in the upper right-hand corner of the window). Select Yes to save the changes. Task 3: Entering Data in a Table (the Author Table) (The data to be entered in the Author table is found in the attachment to this document.) 1. Click on the Author table, located on the left side of the screen, under Tables. 2. Input the Author table data from the attachment to this document into the Author table that you have just created. 3. When you have completed entering the data, save this table structure by closing the table (by clicking on the X in the upper right-hand corner of the window). Select Yes to save the changes. Task 4: Creating another Table (the Wrote Table) 1. Under the Create tab, click Table. Then, under the Home tab, click Design View, as you did with creating the Author table. 2. Following the same general approach outlined in Task 2 above, create the “Wrote” table. Name the first field “Book Code” and make it data type Short Text. Name the second field “Author Number”, and name the third field “Sequence Number”, both of which you will label as data type Number. 3. In this table, set both the “Book Code” and the “Author Number” as the Primary Keys of this table. To do this, select both rows by holding down the Control (Ctrl)

key while clicking on the Row Selector button to the left of the field name in both rows. Then, click on the Primary Key toolbar button. 4. Following the same general approach outlined in Task 3 above, populate the Wrote table by entering the data found in the attachment to this document. Task 5: Considering the Form View for Working with a Table (Wrote Table) *Optional Do not close the Wrote table yet. Instead, click the Create tab above the toolbar, and then click Form on the toolbar. Access builds a simple form “one record per screen” and displays it on your screen. This is an alternative way of viewing a table, and it may be easier for data entry, especially for tables with a large number of data fields.

Task 6: Defining the Relationship between the Author and Wrote Tables 1. Close any open tables so that only the database window is visible. 2. Click on the Database Tools tab above the toolbar, and then click Relationships. This will open up a Show Table dialog box.

3. Click on the Author table name to highlight it, then hold down shift, click on the Wrote table name to highlight it, and click Add. Click on Close to take you to the Relationships window. Add the tables to the Relationships window.

4. To define the relationship between these two tables, click on “Author Number” of the Author relationship box, and drag that field name to the corresponding field in the related table (that is, drag it to the appropriate foreign key). In our case, drag it to the Author Number field in the Wrote table. This causes a dialog box to open.

5. Click on the Enforce Referential Integrity box. This will ensure that every record in the Wrote table has a corresponding record in the Author table, and it will also prevent you from deleting an Author record if that author has books in the Wrote table. Click on Create to complete the definition of the relationship.

6. Make sure you save your Relationships changes by clicking close (x) at the right hand corner and Yes to Save. Return to the database window. Task 7:

Use One-Table Queries

Query A: List all the author names with a last name beginning with one of the letters A through H. 1. Click on Create, then Query Design while in Design View. We will use the default query type in these initial queries, which is a Select query.

2. Select the Author table, then click Add and close the table. 3. At the bottom of the screen, click on the drop-down arrow in the first column (next to field) and choose “Author Name”. This puts “Author Name” in the first column and puts a checkmark in the Show row so that “Author Name” will show up in the answer to the query. 4. In the Criteria row, type box, which will move all field names to the Selected Fields box. Click Next.

3. We don’t want any grouping levels, so click on the < box, which will move “Book Code” back to the left. Click Next again. 4. Sort on “Book Code” (ascending). Click on the drop-down arrow at the right of the first blank and select “Book Code”. Click Next. 5. Accept the default values for Tabular Layout and Portrait Orientation. Click Next. 6. Preview the report to see if you like it by clicking Finish. 7. Print the report by clicking on the printer icon on the toolbar. You DON’T have to print out this report for the tutorial. I am just showing you how to go about printing it when the time comes.

Task 11: Closing Access Close all open windows by right-clicking and selecting close all option....