Lab 1 Introduction to Microsoft Access
Shared by: tcg14679
Lab 1: Introduction to Microsoft Access Objective By the end of this lab, you should be familiar with Some basic features of MS Access, including tables, queries, forms, and reports Standard terminology Microsoft Access In this lab, we will be implementing the theories on database design that we learned in class with a commercial Relational Database Management System called Microsoft Access (MSA). The program comes with the standard Microsoft Office install, but should you wish to purchase it separately you might be able to get a good deal at the student store: http://calcomputers.berkeley.edu/ If you have a laptop, we encourage you to install MSA on your computer and bring it to the lab sessions, since we don’t have enough computers to accommodate every student individually. If you do not have a copy of MSA on your home PC, you can launch it on any of the lab machines by selecting Start All Programs Microsoft Office Microsoft Office Access 2007. To use the lab machines, you will need to set up an account with the IEOR department; please see Jay Sparks on the fourth floor for details on this procedure. Most of the information provided in this lab can be accessed in greater detail under Microsoft Access’s extensive help files. All the information provided in this tutorial is based on MS Access 2007 unless otherwise specified. If you have a different version installed on your machine, you should expect to see some minor differences between what is described here and what you observe on your screen. What is a database? Features of a database Tables A table allows the user to store a collection of data about a specific topic, such as Customers or Orders Queries Queries allow the user to view, change, and analyze data in different ways, such as combining data from two different tables (Customers and Orders), in order to create a custom view (e.g. London orders placed in April). They may also be used as the source of records for forms, reports, and data access pages. Forms Forms allow users to enter, change, or update data. Reports Now that you have all of your data stored in a database, you need to have a way to view it; this is where reports come in handy. Terminology It is important to note that MS Access sometimes uses different terms from those we use in class and the textbook. The following is a summary of such terms and how they should be interpreted: MS Access Lecture/Text Table (Lab 3) Relation (Ch. 5) Column/Field (Lab 3) Attribute (Ch. 3) Row/Record (Lab 3) Tuple (Ch. 5) Relationship View1 (Lab 4) Lines indicate foreign keys (Ch. 7) Quiz 1 (Due by next Friday) Choose one of the following two ways to open the Northwind 2007 database: Open MS Access; click Sample under the category listed in the left task pane on the Getting Started with Office Access page; then click on the Northwind 2007 icon, enter the name and location on the right task pane and click the Download button. or Go to directory C:\Program Files\Microsoft Office\Templates\1033\Access\ and double click Northwind.accdb. After the Northwind 2007 is launched, please answer the following questions (Note: the Northwind sample database in an older version of MS Access is very different from the one in MS Access 2007): 1. What version of Access do you have? 2. Choose Object Type on the Navigation Pane. Please list different objects or components available in the Northwind Database window? Describe two of them in simple words in terms of their functions. 3. In the TABLE Shippers, how many shippers are there? Please list them. 4. In TABLE Orders, which field (i.e. column heading) is identical to the field in TABLE Employees? and TABLE Customers? and TABLE Shippers? Please list all you can find in the format like Orders.field1=Employees.field2, and etc. (Clarification: consider the identity in terms of contents but not the field names, and allow combination of fields in the same table, for example Orders.fieldnameX= Employees.fieldnameY+ Employee.fieldnameZ) 5. Find the order with the most expensive Shipping Fee, and list the Order ID and Shipping Fee. 6. What are the Quick Links available in the FORM Home? 7. What is the dollar amount of products ordered by Company BB during the 2nd Quarter of 2006? What is the TOTAL dollar amount ordered by Company BB in 2006? (Hint: find the relevant form or report first) 8. What are the 10 biggest orders? What is the 3rd biggest order? (Hint: find the relevant report first 1 Note that the Relationship View in MS Access looks similar to but is different from the ER Diagram.