MIS 204 Lesson 11 Hands-on Activity: Joins (Windows Version) | Complete Solution

MIS 204 Lesson 11 Hands-on Activity:
Joins (Windows Version)

Introduction

In this assignment you will learn about joining tables together to increase the power of the database.  If two tables have a relationship between them, a join will connect the data in the tables.  For this exercise you will work in a sample car dealership database that tracks customers, salespeople, vehicles, and sales.

Step 1:  Preparing for the assignment.
Your first task is to download the base database file from ANGEL. This file is called MIS 204 HOA11 Base Customer Database for Windows.  Download the file to some location that you will easily remember.  Once downloaded, open the file.

Step 2:  Reviewing the database.
This database is relatively simple in its current form.  There are four tables in the database:  CUSTOMERS, SALES, SALESPEOPLE, and VEHICLES.  The tables contain the following:
•    CUSTOMERS:  first name, last name, and id data for customers.
•    SALES:  a sales id, the employee id of the salesperson involved in the transaction, the customer id of the customer involved in the transaction, and the vehicle id of the vehicle sold in the transaction.
•    SALESPEOPLE:  first name, last name, id, and commission rate data for sales personnel.
•    VEHICLES:  id, make, model, and price data for vehicles in the dealership’s inventory.
Open each table and look around to familiarize yourself with the data but do not make any changes at this time.

Step 3:  Creating relationships between the tables.
In order to be able to join data in a database, tables must have relationships between them.  Otherwise, each table represents a discrete entity with no connection to any other entity in the database.  ACCESS provides tools to create relationships between tables.
1.    Click on the Database Tools tab on the ribbon.
2.    Double Click on Relationships.  A dialog box will open asking you which tables you want to show:
 

3.    Holding the SHIFT key down, select all four tables and then click the Add button.  You will now see all four tables displayed with their fields listed:

4.    Now it is time to create the first relationship.  In this database, the CUSTOMERS, SALESPEOPLE, AND VEHICLES will all be related through the SALES table.  In other words, an individual customer is connected to a salesperson only through a sales transaction.
a.    To create your first relationship, click on CUSTOMERID in the CUSTOMERS table and drag it to the SALES table.  NOTE:  It is important that when you drag it to the Sales table that the mouse pointer is hovering over “CustomerID” in the sales table in order to establish a relationship between “CustomerID” from the CUSTOMERS table and “CustomerID” from the SALES table.   
If you did it correctly, you should see the following dialog box open up:

Make sure that CUSTOMERID is listed in both columns.

If you did it incorrectly, you will see one of the other fields (e.g. SalesID, EmployeeId, VehicleID) from the Sales table.

b.    Notice that the “Relationship Type” is specified as One-To-Many.  This is correct.  A single customer may be involved in multiple sales; but only one customer is involved in each sale.
c.    Check the box for Enforce Referential Integrity.  What this does is tell ACCESS that this relationship will only work if a CUSTOMERID in the SALES table is also in the CUSTOMERS table.
d.    Click Create.
e.    You will now see a line with a 1 at the CUSTOMERS end and a ∞ at the SALES end.

f.    Now repeat steps (a – e) to create the following relationships:
i.    EMPLOYEEID in SALESPEOPLE to EMPLOYEEID in SALES.
ii.    VEHICLEID in VEHICLES to VEHICLEID in SALES.

g.    Your relationships should now look like this:

h.    Click Save.
i.    Click the Close icon.

5.    Your relationships are now created, and now you can create queries that will join the data in the tables.

Step 4: Creating Joins
1.    To demonstrate a join, we’ll create simple query to show all the vehicles each salesperson has sold.
a.    Click on the Create tab on the ribbon.
b.    Click the Query Wizard icon.
c.    Select Simple Query in the dialog box and click OK.
d.    In the next dialog box, you can select what fields from what tables you wish to include in your query.

e.    Using the drop-down arrow change the table to SALESPEOPLE.
f.    Select EMPLOYEEFIRST then EMPLOYEELAST using the single-chevron (order is important here!).

g.    Using the drop-down arrow change the table to VEHICLES.
h.    Select MAKE and MODEL using the single-chevron.  You should now have four fields displayed in the “Selected Fields” window:

i.    Click Next.
j.    Ensure that the “Detail (shows every field of every record)” button is selected; and Click Next.
k.    Change the title of the query to Vehicles Sold by Salesperson.
l.    Click Finish.
m.    You should see the following table:

The reason the results are in the order they are in is because they are ordered by the SALESID field in the SALES table even though it is not included in the query.  This order is not particularly useful for our needs so we need to redesign the query to order our results in a more useful manner.

n.    Right-click on the query tab and select Design View.
o.    Click in the Sort field for the EMPLOYEELAST column and select Ascending.

p.    Click the Run icon on the ribbon.  You should see your results have been reordered in the following manner:

q.    As you can see, this order is much more intuitive as you can see each car an individual salesperson sold.
r.    Click the Save icon and close the query.

2.    Let’s say that as the manager you wanted to know how much commission you paid out on each sales transaction.  You can do that with a query.
a.    Click on the Create tab.
b.    Click on the Query Design icon.  Select all four tables to show and click on ADD.  You should see the tables with their fields open up in the design window.  Close the “Show Table” pop up.
c.    Select the following fields in order by dragging them from their table to where it says Field:  in the query design space at the bottom of the window.
i.    SALESID
ii.    CUSTOMERFIRST
iii.    CUSTOMERLAST
iv.    MAKE
v.    MODEL
vi.    PRICE
vii.    EMPLOYEEFIRST
viii.    EMPLOYEELAST
ix.    COMMISSIONRATE

d.    ACCESS provides the functionality to calculate on the fly the commission paid out on a sale.
i.    Click in the Field space on the first empty column next to COMMISSIONRATE.
ii.    Type the following:  Commission:[COMMISSIONRATE]*[PRICE].
iii.    Place your cursor in the row below where you just entered the formula and right click.  This will open the Property Sheet for this field to the right.  In the Property Sheet on the right change the Format to Currency.
iv.    Click the Run icon on the Ribbon.
e.    You should see the following results:

f.    Save your query as Commission Payouts.
g.    Close your query.

3.    The last step is to create one last query to see total commissions earned by the sales staff so that you can see whom your top earner is.
a.    On the Create tab, click Query Wizard.
b.    Select Simple Query Wizard.
c.    Select the following fields:
i.    EMPLOYEEFIRST, EMPLOYEELAST and COMISSION from the Commission Payouts query (that’s right you’ll be querying a query!)
ii.    Click Next.
iii.    This time, Change the query type from Detail to Summary.
iv.    Click the Summary Options button.
v.    Select Sum; Click OK
vi.    Click Next.
vii.    Give the query the name Salesforce Earnings Query.
viii.    Change the setting from Open the query to view information to Modify the query design.
ix.    Click Finish.
x.    In the design space of the design view, click in the Sort field of SUM OF COMMISSION and select Descending.

xi.    Click the Run icon on the ribbon.  Your query results should look like this:
xii.    Save and close your query.

 
Step 5: Submit your assignment.
You have now completed your assignment.  Upload your ACCESS database to the Lesson 11 Hands-On Activity drop-box. Your assignment will be graded using the following rubric:

Category    Points
Relations created correctly
•    Referential integrity enforced.
•    Relationships correct    5
Vehicles Sold by Salesperson Query created successfully
•    Correct query results returned
•    Query results sorted correctly    5
Commission Payouts Query created successfully
•    All fields selected correctly
•    Commission calculated correctly
•    Commission field formatted correctly    10
Salesforce Earners Query created successfully
•    Correct fields chosen
•    Results summed correctly
•    Results sorted correctly    5
Total:     25pts