Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
All Sections – The Access Workbench
CREATE TABLE COMPUTER_ASSIGNMENT(
SerialNumber Int NOT NULL,
EmployeeNumber Int NOT NULL,
);
Note that Microsoft Access will correctly modify SQL Server 2005 data types into the
appropriate Microsoft Access data types. In the above SQL code, the following SQL
syntax will not work in Microsoft Access:
• The ON UPDATE actions. Set cascading update behavior in the GUI.
• The ON DELETE actions. Set cascading delete behavior in the GUI.
Therefore, the SQL statement for Microsoft Access is:
CREATE TABLE COMPUTER_ASSIGNMENT(
);
All Sections – The Access Workbench
All Sections – The Access Workbench
D. The data for the COMPUTER_ASSIGNMENT table are in Figure 3-35. Use
Access SQL to enter these data into your COMPUTER_ASSIGNMENT table.
INSERT INTO COMPUTER_ASSIGNMENT VALUES(
9871234, 12, '15-Sep-17', '21-Oct-17');
All Sections – The Access Workbench
INSERT INTO COMPUTER_ASSIGNMENT VALUES(
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber, DateAssigned)
VALUES(6541001, 12, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber, DateAssigned)
VALUES(6541002, 13, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber, DateAssigned)
VALUES(9871234, 1, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber, DateAssigned)
VALUES(9871235, 2, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber, DateAssigned)
All Sections – The Access Workbench
SELECT *
FROM COMPUTER_ASSIGNMENT
ORDER BY DateAssigned ASC, SerialNumber ASC;
All Sections – The Access Workbench
E. Who is currently using which computer at WP? Create an appropriate SQL
query to answer this question. Show SerialNumber, Make, and Model. Show
EmployeeNumber, LastName, FirstName, Department, and OfficePhone.
Sort first by Department and then by employee LastName. Save this query
using the query naming rules in exercise AW.3.1.
Note that many computers have been reassigned, and the query needs “WHERE
DateReassigned IS NULL” to determine the current assignment. That query is also
shown below.
/***** Question E - SQLQuery-AWE-3-3-E ************************/
All Sections – The Access Workbench
F. Who is currently using which computer at WP? Create an appropriate QBE
query to answer this question. Show SerialNumber, Make, Model,
ProcessorType, and ProcessorSpeed. Show the EmployeeNumber,
LastName, FirstName, Department, and Employee Phone. Sort first by
Department and then by employee LastName. Save this query using the
query naming rules in exercise AW.3.2.
All Sections – The Access Workbench
APPPENDIX E
CHAPTER OBJECTIVES
• Learn how to use the SQL ALTER statement.
• Learn how to create and use a query on a recursive relationship.
• Learn how to create and use the Microsoft Access equivalent of SQL views.
• Learn how to import Microsoft Excel 2016 data into a Microsoft Access table.
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
TEACHING SUGGESTIONS
• This section of “The Access Workbench” is a continuation of Section 3 on SQL and
QBE. If you are going to cover SQL views, Appendix E should be taught
immediately after Chapter 3, and this section of “The Access Workbench” should
immediately follow Section 3 of “The Access Workbench”.
• Demonstrate Microsoft Access in class, and discuss each of the operations covered
in the text.
• Make sure your students actually work through The Access Workbench steps before
attempting the Access Workbench Exercises.
ANSWERS TO ACCESS WORKBENCH EXERCISES
In the “Access Workbench Exercises” sections for Chapters 1, 2, and 3, you created a database
for Wedgewood Pacific (WP) of Seattle, Washington. In this set of exercises, you will use that
database, as completed in Chapter 3’s section of “The Access Workbench Exercises,” to create
and use Microsoft Access queries as SQL view equivalents.
AW.E.1. Using Access QBE or SQL, create and run view-equivalent queries to complete the
questions that follow. Save each query using the query name format
viewViewQueryName.
A. Create an Access view-equivalent query named viewComputer that shows
Make, Model, SerialNumber,ProcessorType, ProcessorSpeed, MainMemory,
and DiskSize.
All Sections – The Access Workbench
See the file: DBC-e08-WP-AW-AppE.accdb. Note that in the solution the view is
named viewComputer. See the query detail screenshot below.
All Sections – The Access Workbench
B. Create an Access view-equivalent query named viewEmployeeComputer that
uses viewComputer for part C to show EMPLOYEE.EmployeeNumber,
LastName, FirstName, and the data about the computer currently assigned to
that employee, including Make, Model, SerialNumber, ProcessorType,
ProcessorSpeed, MainMemory, and DiskSize.
All Sections – The Access Workbench
AW.E.2. Use Access QBE to create and run the queries that follow. Save each query using
the query name format QBEQuery-AWE-E-1-## where the ## sign is replaced by the
letter designator of the question. For example, the first query will be saved as
QBEQuery-AWE-E-1-A
A. Create an Access QBE query to display the data in viewComputer, sorted
alphabetically by Make and Model, and then numerically by SerialNumber
All Sections – The Access Workbench
B. Create an Access QBE query to display the data in viewEmployeeComputer.
Sort the results alphabetically by LastName, FirstName, Make, and Model
and then numerically by SerialNumber.
See the file: DBC-e08WP-AW-AppE.accdb.
All Sections – The Access Workbench
AW.E.3. Figures E-38 and E-39 show to worksheets in a Microsoft Excel 2016 workbook. The
COMPUTER worksheet shows data about computers owned at WP, while the
COMPUTER_ASSIGNMENT worksheet shows, in the correct format, the data about
which computer has been or is currently assigned to which WP employee. We want
to import this data into two tables in the Microsoft Access 2016 WP.accdb database.
Actually, we have already done a version of this exercise in the Chapter 3 Access
Workbench Exercises. So, in case you have completed those exercises, we will
import the data into two new tables use alternate table names: COMPUTER_2 and
COMPUTER_ASSIGNMENT_2.
A. In Microsoft Access 2016, create a workbook named DBC-e08-WP-
Computer-Assignment-Worksheet.xlsx. In this workbook, create the
COMPUTER and COMPUTER_ASSIGNMENT worksheets shown in Figures
E-38 and E-39.
All Sections – The Access Workbench
B. Using the table names COMPUTER_2 and COMPUTER_ASSIGNMENT_2,
import the data from the DBC-e08-WP-Computer-Assignment-Worksheet.xlsx
file into the WP database.
C. Figure E-40 shows the column characteristics for the COMPUTER_2 table.
Modify the COMPUTER_2 structure as needed to match the table column
characteristics shown in Figures E-40.
D. Figure E-41 shows the column characteristics for the
COMPUTER_ASSIGNMENT_2 table. Modify the
COMPUTER_ASSIGNMENT_2 table structure as needed to match the table
column characteristics shown in Figures E-41. Note: These column
characteristics intentionally vary from those shown in Figure 3-34—can you
figure out why?
All Sections – The Access Workbench
E. Create the needed relationships between COMPUTER_2,
COMPUTER_ASSIGNMENT_2 and EMPLOYEE.
All Sections – The Access Workbench
F. Complete exercises AW.E.1 and AW.E.2 using COMPUTER_2 and
COMPUTER_ASSIGNMENT_2and with appropriate view names. For the
questions in AW.E.2, save each query using the query name format
QBE-Query-AppE-AW-3-##, where ## is replaced by the letter designator of
the question. For example, the first query will be saved as
QBE-Query-AppE-AW-3-A.
See the file: DBC-e08WP-AW-AppE.accdb.
All Sections – The Access Workbench
All Sections – The Access Workbench
SECTION FOUR
CHAPTER OBJECTIVES
• Learn how to create prototype forms in Microsoft Access
• Learn how to create prototype reports in Microsoft Access
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
TEACHING SUGGESTIONS
• This section of “The Access Workbench” introduces more complex forms and reports
to your students.
• Make sure your students actually work through The Access Workbench steps before
attempting The Access Workbench Exercises.
• Take some time to discuss and illustrate modifying forms and reports that are
created by the wizards. Discuss how the banded form and report editors work in
detail.
• Point out that Microsoft Access can create forms and reports based on queries as
well as tables. Mention that this is similar to using a SQL view as the basis for a
form or report. Demonstrate this in class.
• While the first two exercises in this section of the Access Workbench Exercises can
be handled using a form and report built on multiple tables, the last two questions will
need underlying queries to control the object placement in the form and report. See
the solutions to these questions and the Microsoft Access database
file DBC-e08-WP-AW-04.accdb. Note that this file does not include the work done in
the Appendix E section of “The Access Workbench.”
All Sections – The Access Workbench
ANSWERS TO ACCESS WORKBENCH EXERCISES
“The Access Workbench” in this chapter described how to create two prototype databases and
example forms. That section details some steps that are new, but you have done most of the
needed steps before. In the following set of exercises, you will:
• Create prototype forms, and
• Create prototype reports.
AW.4.1. You have built an extensive database for the Wedgewood Pacific (WP.accdb). You
will now use it to build some prototype forms and reports so that the users at WP can
evaluate the proposed database. In this case, there is no need to restructure the
database.
A. Create a form that allows users to view and edit employee data. The form
should show information about the employee, the department that he or she
works for, and which projects the employee is assigned to.
See the file: DBC-e08-WP-AW-04.accdb
There are many possibilities for the final design of the form. Here is one:
All Sections – The Access Workbench
B. Create a report that displays the employee information shown on the form
you created in part A. The report should show this information for all users,
sorted alphabetically in ascending order by LastName.
