BADM 2301 Lab Session

Lab 5 A – Microsoft Access

Background

In this lab session, you will learn how to use Microsoft Access to manage order data in a sales organization, where customer, item and order information are stored in three separate tables. We will make connections between the tables by defining the relationships between them, and then create multiple queries and a report.

Primary Keys

To start with, set the primary keys for each table.

1.   Right click on “Items” in the Table pane on the left; choose “Design View” to open it.

2.   To set the primary key, highlight “Item ID” as the primary key by selecting the corresponding

row heading. In the ribbon, find and click on the “Primary Key” button. If set correctly, you should see a small key logo by the row headings.

➢  Please follow similar procedures to set “Customer ID” as the primary key for the

“Customer” table.

3.   Now set both “Customer ID” and “Item ID” as primary key(s) for the “Order” table. ➢ This is called a Composite Primary Key.

➢  To do so, select both rows in the table by using CTRL+click. Then click the “Primary Key”

button on the menu bar. Hint: when you see an arrow in the first row, drag it down to select both rows. 4. Save all the work and close all open tabs.

Understanding and Managing Relationships

The three tables of the database contain three different sets of information that is likely input and maintained by different departments at the organization. We can integrate the information from these tables by defining relationships between them.

1.   In the menu, select “Database Tools” → “Relationships” to bring out the “Relationships”

window. If the “Show Table” dialog box does not show up, select “Relationships” →

“Show Table” from the menu bar. Select each table and click “Add” to add them into

“Relationship” window. Relocate the tables in proper positions.

2.   Drag “Customer ID” of “Customer” table to “Customer ID” in “Order” table, check “Enforce referential integrity”, and click “Create”.

➢  You just created a one-to-many relationship between the two tables using the common field “Customer ID” .

➢  Notice an infinity symbol (i.e. ∞) appearing by the registration tables. It suggests the one-to-many relationship you just created.

3.   Do the same thing for “Item ID” in “Items” and “Order” table to create the second relationship.

4.   Save the work and close the Relationship tab.

Query and Parameter Query

We need to use queries to get data from a database. Queries are a convenient tool to extract the information we need by following a set of specified criteria. In this part of the tutorial, we will learn how to create a “Looking for Items” query that will extract records for all items from your database.

1. Choose “Create” in the menu bar and then click on “Query Design” . In the “Show Table”

dialog box, select “Items” and click on “Add” . Close the “Show Table” dialog box; double- click the fields you need, to select them into the lower table. These are the fields that will show up in the query results. Close the query design window and save it as

“Looking for Items” . 2. Click the “Run” button on the menu bar to test your query.

Next, we will create a parameter query which will allow users to search for customers information.

3. Choose “Create” in the menu and then click “Query Design” . In the “Show Table” dialog box, select “Customers” and click on “Add” . Close the “Show table” dialog box; double-click the fields to select them into the lower table. These are the fields that will show up in the results. First, locate the “ Criteria” row of the field “State“ in the lower table. Next, type

“ [Input a State]” (do NOT omit the SQUARE brackets!). Close the query design window and save it as “Looking for Customers by State” . 4. Test your query using a state, say “VA” . Click the “Run” button.

Next, we will learn how to create a new parameter query which will allow us to list items bought by customers based on Item IDs.

5.   Choose “Create” in the menu and then click on “Query Design” . In the “Show Table”

dialog box, select all three tables this time. Close the “Show table” dialog box. To select fields into the lower table, double-click on Item ID, Item Name, and Item Description, from the "Items" table. Then, double-click on “Customer Name” from "Customer" table. These are the fields that will show up in the query results. Add “ [Input Item ID]” in the “Criteria” row of “Item ID” field. Close the query design window and save it as “ Looking for Items bought by Customers” .

6.   Run and test your query - similar to what you did above.

7.   Save your work.

As you can see, by setting queries in a database management system, you can retrieve data from multiple tables - as long as they have been “connected” using proper relationships.

Please remember if your relationships are not formed correctly, the results of the query will be wrong.

Create a Report

1.   From the menu bar, choose “Create” → “Reports” → “Blank Report.” A new empty report will appear in the window with a Field List, including all the tables on the right.

2.   On the Field List (on the right side), click on "Show all tables".

3.   Locate "Customer" table, click on the + sign. Add customer name customer address and customer state fields in the report by double-clicking on them.

4.   Similarly, you can choose date of order, quantity and amount fields from the “Order” table and item name field from the “Items” table to add to this report.

5.   You can resize a column just like in an Excel window. First click on the column header to select it. Next, move the mouse to the right border of the column header until shape of your cursor changes to a double arrow. Click and drag the border to the left to make it smaller or to the right to make it bigger.

6.   You can use the “Format” tab and then “Font” on the menu bar to adjust the font size, color and alignment.

5.     Add a title to the report by choosing “Design” →     “Title”. A title text box will appear on top of the report. Name the report “Customer Report.”

6.     Add page numbers to the report by choosing “Design” → “Page Numbers” . Choose the format and the position of the page number.

7.     To add the current date, choose “Design” → “Date and Time.” Select the format you like to present the current date and time.

8.     To see a preview of what the printed report will look like, right-click the report title bar and choose “Print Preview” from the drop-down menu. Or you can also click on the View button, then choose “Print Preview.”

9.     To go back to the Layout View, right-click anywhere on the report and select “Layout View.” Close the report and when prompted, save the report as “Customer Report.”

10.   Your file should have the following on the left side bar:

a.   Tables: Customer, Items, Order

b.   Queries: Looking for Items, Looking for Customers by State, Looking for Items Bought by Customers

c.    Reports: Customer Report

Submission

Save the Access file as “FirstName_Lab5” and download it from the Virtual Lab to your local computer.

Please make sure to submit it in the Assignment Lab 5 link.

Submit the downloaded file in the file format .accdb to Blackboard. Please make sure that the extension of your Access file MUST be in .accdb.

Files ending with .laccdb will NOT be accepted.