Lecture 7 Notes: Introduction to Microsoft® Access

MASSACHUSETTS INSTITUTE OF TECHNOLOGY Sloan School of Management 15.561 IT Essentials An Introduction to Microsoft Access Lecture Script* 1. Introduction Open Database: Northwind.mdb Select “File” then “Open Database” Note file extension .mdb Discuss Access UI An Access database consists of one or more tables Queries can be defined to retrieve information from the tables Forms can be specified to provide a nicer, graphical user interface for entering and viewing data stored in tables or generated by queries Reports can be defined in order to nicely format and print the contents of tables and the output of queries Macros and modules containing Visual Basic code can be written to provide additional functionality to a database application. View Relationships Select “Tools” then “Relationships…” The Northwind Traders database keeps track of the day-to-day operations of a small food distribution company. The company stocks products provided by suppliers. Products are grouped into categories. Its employees service customers who place orders. Every order has an order header and one or more order detail records. Each order detail record contains information about one item ordered. Orders are shipped to customers using one out of a set of shippers that the company works with. 1 The lines in the relationships view represent relationships among tables. Typically, they join together a field in one table, which refers to a field in another table. Lines are created manually by clicking the first field and the dragging to the second field. 2. Tables Open Table (For example: Employees) Double-clicking a table shows content in table view Similar to Excel or other spreadsheet Add rows at end Add “employee” data about Prof. Malone, the TAs, etc. (notice that some field can be specified as “required fields” and the system does not complete record entry unless they have been filled in; example: First Name, Last Name) Rows = data records, database content Columns = fields, part of database design 2 Switch to design view Select “View” then “Design View” Every field has a Name, a Data Type and a Description The lower right part of the screen provides contextsensitive help on the element that the cursor is on. More help can be obtained by pressing F1. There are several different data types that a field can have Text, Number, Date/Time, Currency, Autonumber… Properties of data types Formatting (e.g. birth date field) Validation rules (e.g. birth date field) Indexing: Users can optionally instruct Access to create an index on any field of any table Pros: faster to find and retrieve data Cons: slower to enter and save data Primary key: Every table must have a field whose value uniquely identifies each record in the table (e.g. the SSN for people, the ISBN for books, etc.) That field is called the primary key and is marked in the fields list with a key icon Create New Table (Example: A students table) Use wizard and select a table template that comes close (e.g. Employees table) Selectively add fields from the table template Id, first, last, e-mail Show in table view to add sample record e.g. Ben Lee, yoongl@mit.edu Show in design view to create from scratch (Note: You can also import and export tables to and from Excel.) 3 3. Queries Access uses SQL as its query language. In addition it supports a graphical front-end that allows easier specification of queries and automatically translates them into SQL Simple Queries Create query to list employee names (click “Queries” tab in the main Access window, click the “New” button, select “Design view” – there is also an option to use a wizard for semi-automatically generating some simple queries) Explain sections of design view screen Upper half of screen lists all the database tables that participate in the query (all the tables you would list in the FROM clause of SQL) Lower half of screen lists all the fields of the above tables that are either being displayed or participate in some way in the selection, grouping or aggregation of the final query output Example: Employees table, first and last fields Run To run a query, select “View” then “Datasheet View” Save as query If you attempt to close the query design window then the system asks for a name to save the query under Query is saved under the “Queries” tab Query views Double-clicking a query runs it and opens it in “Datasheet View” Select “View”, “Design View” Select “View”, “SQL View” Modify query to sort by last name Click on the “Sort” row of the Design View Modify query to find USA employees Drag and Drop “Country” field into lower half of screen Click on “Criteria” row of “Country” field and enter “USA” Deselect the “Show” check mark Remove criteria Modify query to find employees hired after 1/1/93 Drap and Drop “HireDate” field into lower half of screen Click on “Criteria” row of “HireDate” field and enter > 1/1/93 4 Modify query to AND the USA criteria As before, insert the USA criterion By default criteria on different fields are “ANDed” together Modify query to OR the USA criteria Move the USA criterion to the row marked “or” Show SQL for AND vs. OR 5 Joins Create query to find all customers who placed an order in a given time frame (e.g. 1/1/95 - 12/31/95) Goto to Queries tab/Click New/Select Design View/Add Customers table and Orders table Access does automatic join based on the relationships that have been encoded by the designer (shown as connecting lines on the screen) Select customer, order date, enter “between 1/1/95 and 12/31/95” in “criteria” row for order date If a field contains more text than is visible, open a Zoom window (Shift F2) to read it Run, show SQL Save query to use in Totals section Create new query to find customers who placed at least one order for a given product Use Customers, Orders, Order Details, and Products tables. Set product name = "Tofu" as a selection criterion Look at complex SQL query generated by a query that looks simple in the Design view. OPTIONAL: Ask students to find all employees who handled an order for a specific customer (e.g. "North/South") Totals (name for queries using aggregate functions) Select “View” then “Totals”. This inserts a row in the lower half of the screen which allows us to define one aggregate function per field selected Construct a query which calculates average unit price over all products select unit price from products table, select “Totals”, select “Avg” in the Total row Generate unique list of customers who placed an order in a given time frame (group by customers, where on order date, and select count as aggregate function on order ID) Number of products supplied by each supplier (join products and suppliers, group by suppliers, count products) 6 Nested queries (queries that use other queries) Generate a list of employees in order of the total of value of all orders for which they are responsible. Use Employees and Orders tables and the query Order Subtotals that already exists in the database. (To add the pre-existing query, select it under the “Queries” tab in the “Show Table” dialog box.) Group by Employees’ LastName and FirstName. Use aggregate function “Sum” on the field “Subtotal” in “Order Subtotals” and Sort (descending) on this field. 7