Access Lab

User Generated

Wbqn

Computer Science

Description

MS ACCESS 2013 LAB ONE – MODIFY A DATABASE

Purpose: Many times when you are working with a database, you may have to modify one or more tables contained within the database or add additional tables to accommodate changes to improve performance to handle changing requirements. This is the second part of Access Lab One.

In this lab, you will:

  • Add two fields to each of the two tables you created in Part One (Attached)
  • Add two new tables to the database
  • Write a query extracting selected information from the database
  • Generate a report
  • Export a table to Excel for special processing.

A few comments:

  • Save your database and related tables frequently while working with this assignment. If you make a mistake, it is easier to go back to a database that has not been damaged.

Assignment: In this Part Two, you will add two more tables to the database created in Part One and establish relationships between the four tables, completing a query, report, and exporting the data to an Excel Workbook. .

PART TWO:

  • Start Access – a Backstage view opens allowing you to select the database you want to work with.
  • From the Navigation Pane, select Open Other Files.
  • From the list of tables contained in your database, double click the SalesRepTable.
  • If you decided to add the data field names to the tables marked as “Add New Field,” click the “Add New Field” and replace that name with the new field name provided above.
  • Select Design View from the View tool bar tab.
  • Save your database and modified tables.
  • After adding the fields to the SalesRepTable, repeat Steps 3 through 5 replacing SalesRepTable with SalesMgrTable.
  • Select the “Create” tool on the Access Main Tool bar.
  • Select “Table” from the Create tool bar displayed.
  • Follow the same procedure described above to create the SalesRepTable using the data fields below to create first the CustIDTable defined below followed by the SalesTable.
  • After you create and populate the CustIDTable, create the SalesTable using the data fields in the order listed below.
  • After you create the SalesTable, populate the table with the Sales Journal data in Table 7 above.
  • Create a Data Entry Form for each of the new tables created by clicking the Create tab on the Access Main tool bar and then clicking the Form button.
  • On the database tools tab, click the Relations Group.
  • To establish the relationship when you click the Relations Group, a dialog box, Edit Relationships, displays.
  • After establishing the relationship between the SalesRepTable and the CustIDTable, establish a relationship between the CustIDTable and the SalesTable as you did in Step 15 above.
  • Another optional relationship can be established between the SalesRepTable and the SalesTable.
  • Create a Query using the “Query Wizard.”
  • Switch to Design View and click the “Show Table” Icon and ADD the SalesRepTable to the Query.
  • Drag and Drop each of the following 6 fields from the Sales Reps table into 6 separate columns in the Query grid. (Drag and Drop fields):
  • Click Save to save your changes.
  • Click the Run icon to run the Query to see the results of the extracted data from the table. Or switch to the “Datasheet view” to see the results of the extracted data from the table.
  • Create a report using the “Report Wizard.”
  • Select the following fields for your report from the query;
  • The report layout should be stepped and portrait.
  • Title the report Managers Sales Goal to Actual Report.
  • Click the Run icon to run the report.

NOTE: This should be the database you created in Part One. A good technique you might want to consider when you save your database is to append an iteration number to the database name each time you save the database. This allows you to go back one iteration and restore your database if you have a problem with the current database you are working.

  • Select Computer and then Browse.
  • Select the desired database.
  • Access will automatically Open the selected database.
  • Access will display the data contained in the SalesRepTable.
  • You are to add two new fields to this table:
  • Both fields are currency with 2 decimals, required.
  • The fields are not indexed, and are not zero length.
  • TotalCommRecvd
  • GoldClubComm

NOTE: The data field names are listed across the top of the listed data. On the right hand side of the field name list is a data field named “Add New Field.” You can add the two fields listed above to the table here and then proceed to Step 4 below to define the field or proceed to Step 5 below. Since you will have to define the data field, the recommended procedure is to proceed to Step 5 below.

  • Each time you add a field to the table, another “Add New Field” will be displayed to the right of the field just named.
  • Do this procedure twice, once for each new field.
  • In order to complete the field definition, select the Design View from the View tool bar tab and follow the procedure described in Step 5b below.
  • Add the first data field to the SalesRepTable at the bottom of the field name list.
  • Follow the procedure used in Part One of this assignment to define the data field.

NOTE: After completing the addition of the two new fields to the SalesMgrTable, you will be adding two new tables to your database: a CustIDTable and a SalesTable. Follow the procedure below to add the two new tables.

  • All fields are required unless noted otherwise.
  • The primary key to the CustIDTable is CustID, no duplicates, the primary key to the SalesTable is InvoiveNo, no duplicates.
  • In the CustIDTable, the second line of address, CustAddrL2, is optional and therefore not required.
  • After you create the CustIDTable, populate the table with the Customer information in Table 6 below.

Data Field List for the Customer ID Table0

Field Name Data Type: Length Description:

  • CustID Text (A/N) 5 Customer ID Number
  • CustName Text (A/N) 20 Customer Name
  • CustAddrL11 Text (A/N) 20 Address Line 1
  • CustAdrL2 Text (A/N) 20 Optional Line 2
  • CustCity Text (A/N) 15 Customer City
  • CustState Text (A/N)) 2 Customer State
  • CustZip Text (N) 9 Fixed Zipcode 5 + 4
  • SalesRepID Text (A/N) 5 Sales Rep ID Code
  • CustSalesArea Text (A/N) 1 Code = N-E-W-S
  • CustAnnualSales Currency (N) Annual Sales Volume
  • InvoiceNo Text (A/N) 5 Sales Rep ID Number
  • InvCustID Text (A/N) 5 Customer ID Code
  • InvDate Date 10 Format mm/dd/yyyy
  • InvAmount Currency (N) Total Invoice Amount
  • InvTax Currency (N) Tax on Invoice
  • InvNet Currency (N) Total Amt Less Tax
  • InvCost Currency (N) Cost of Goods Sold
  • SalesRepID Text (A/N) 5 Sales Rep ID Code
  • InvCommPaid Currency (N) Commission Amount
  • InvCommRate Number (N) 3 decimals

Customer ID Code

Name

Address

Sales Rep ID

Sales

Area

Annual Sales

215

ABC Company, Inc

PO Box 1919

127 N Chabunga Rd

City Light, MD 22201

122

N

25,122.75

225

Jolly Company

1900 York St

Towson, MD 21252

122

N

31,543.65

230

Flash, Inc

PO Box 13

17 Street Name

Dullaney, MD 21294

105

E

55.678.90

265

Chuck-Fil-C

1900 Chicken Rd

A/P Department

Ellicott Run, VA 19288

210

N

1,975.55

288

Jump On It

345 Flashback Blvd

Wampum, DE 20088

270

W

56,789.10

310

Boom Boom Company

Room 16

1 Quiet RD

Louder, MD 21011

355

S

10,222.75

330

Sell It

16th Floor

1 N Anywhere St

York, PA 11117

397

S

18,299.45

400

Sold It All, Inc

Wam Bam Bldg

19 Fourth St

Baltimore, MD 21224

122

N

44,512.37

410

Nunzio’s Hitem

357 Smith & Wesson

Magnum, SC 35700

397

S

100.350.50

Table 5. Customer Information.

Data Field List for the Sales Journal Table

Field Name Data Type: Length Description:

Invoice

Number

Cust ID

Inv

Date

Total

Invoice

Tax

Net

Sale

Cost

Sale

ID

Comm

Paid

Comm Rate

221

230

02/2/15

3,451.90

207.00

3659.80

2290.71

122

0.00

0.025

222

225

02/27/15

777.55

38.88

816.43

486.25

210

0.00

0.025

223

265

03/15/15

345.97

17.30

363.27

155.35

105

0.00

0.015

224

230

03/15/15

1,475.98

73.80

1549078

1027.85

270

0.00

0.025

226

230

03/17/15

2,196.90

109.85

2306.75

1688.94

122

0.00

0.025

229

310

03/20/15

1,223.69

73.42

1297.11

867.34

355

0.00

0.030

240

330

04/01/15

775.75

46.55

822.30

432.75

210

0.00

0.025

255

400

04/02/15

3,978.86

238.72

4217.58

2988.56

122

0.00

0.025

321

410

04/10/15

6,455.60

322.78

6778.38

4975.25

397

0.00

0.030

Table 6. Sales Journal Table.

  • Save the database and table often while you are entering each data field.
  • Be aware of the field sizes as you are entering each data field.
  • Take note of the Validation text. Validation of data entered rules can be entered here to ensure the kind of information you are looking for is being entered.
  • Name the CustIDTable CustomerInputForm and the SalesTable SalesInputForm.
  • When you create a relationship, you enforce referential integrity.
  • Select the two tables, SalesRepTable and CustIDTable, to be joined
  • In most cases, check the Enforce Referential Integrity.
  • Check Cascade Update Related Fields.
  • Establish a one to many relationship between the SalesRepTable (the 1 side) using SalesRepID and the CustIDTable (the many side) using CustSalesRep.
  • Establish the one to many relationship between the CustIDTable (the 1 side) using CustID and the SalesTable (the many side) using InvCustID.

  • Select the Simple Query Wizard.
  • Select the following fields to be included in the wizard:
  • Name the query Managers Sales Goal.
  • Click Save to save your changes.
  • e.)Click the Run icon to run the Query to see the results of the extracted data from the table. Or switch to the “Datasheet view” to see the results of the extracted data from the table.
  • ManagerIDNo
  • MgrLastName
  • MgrFirstName
  • MgrMidInit
  • MgrSalesGoals
  • SalesRepID
  • RepLastName
  • RepFirstName
  • RepMI
  • RepSalesGoal
  • RepActSales).
  • Click the Create tab.
  • Select Report Wizard.
  • Select the Managers Sales Goal Query from the Tables/Queries section drop down.
  • ManagerIDNo
  • MgrLastName
  • RepLastName
  • RepFirstName
  • RepMI
  • RepSalesGoal
  • RepActSales
  • Turn in all of your work through Blackboard.

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Please zip it and uploa...


Anonymous
Nice! Really impressed with the quality.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags