Database Storage & Design Appendix N All Sections The Access Workbench Create Table Computerassignment Serialnumber Int Not Null

Unlock access to all the studying documents.

View Full Document

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

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

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.