Add two fields to each of the two tables you created in Part OneAdd two new tables to the databaseWrite a query extracting selected information from the databaseGenerate a reportExport a table to Excel for special processing.(the Access file and instructions in the attachment below, please read it)
Unformatted Attachment Preview
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
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. .
1. Start Access a Backstage view opens allowing you to select the database you want to
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
2. From the Navigation Pane, select Open Other Files.
a.) Select Computer and then Browse.
b.) Select the desired database.
c.) Access will automatically Open the selected database.
From the list of tables contained in your database, double click the SalesRepTable.
a.) Access will display the data contained in the SalesRepTable.
b.) You are to add two new fields to this table:
c.) Both fields are currency with 2 decimals, required.
d.) The fields are not indexed, and are not zero length.
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.
4. 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.
a.) Each time you add a field to the table, another Add New Field will be displayed to
the right of the field just named.
b.) Do this procedure twice, once for each new field.
c.) 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.
5. Select Design View from the View tool bar tab.
a.) Add the first data field to the SalesRepTable at the bottom of the field name list.
b.) Follow the procedure used in Part One of this assignment to define the data field.
6. Save your database and modified tables.
7. After adding the fields to the SalesRepTable, repeat Steps 3 through 5 replacing
SalesRepTable with SalesMgrTable.
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.
8. Select the Create tool on the Access Main Tool bar.
9. Select Table from the Create tool bar displayed.
10. 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.
a.) All fields are required unless noted otherwise.
b.) The primary key to the CustIDTable is CustID, no duplicates, the primary key to the
SalesTable is InvoiveNo, no duplicates.
c.) In the CustIDTable, the second line of address, CustAddrL2, is optional and therefore
d.) After you create the CustIDTable, populate the table with the Customer information
in Table 6 below.
Data Field List for the Customer ID Table0
215 ABC Company, Inc
225 Jolly Company
230 Flash, Inc
PO Box 1919
127 N Chabunga Rd
City Light, MD 22201
1900 York St
Towson, MD 21252
PO Box 13
17 Street Name
Dullaney, MD 21294
Customer ID Number
Address Line 1
Optional Line 2
Fixed Zipcode 5 + 4
Sales Rep ID Code
Code = N-E-W-S
Annual Sales Volume
1900 Chicken Rd
Ellicott Run, VA
288 Jump On It
345 Flashback Blvd
Wampum, DE 20088
Boom Room 16
1 Quiet RD
Louder, MD 21011
330 Sell It
1 N Anywhere St
York, PA 11117
400 Sold It All, Inc
Wam Bam Bldg
19 Fourth St
Baltimore, MD 21224
410 Nunzios Hitem
357 Smith & Wesson
Magnum, SC 35700
Table 5. Customer Information.
11. After you create and populate the CustIDTable, create the SalesTable using the data
fields in the order listed below.
Data Field List for the Sales Journal Table
221 230 02/2/15 3,451.90
222 225 02/27/15
223 265 03/15/15
Sales Rep ID Number
Customer ID Code
Total Invoice Amount
Tax on Invoice
Total Amt Less Tax
Cost of Goods Sold
Sales Rep ID Code
224 230 03/15/15 1,475.98
226 230 03/17/15 2,196.90
229 310 03/20/15 1,223.69
240 330 04/01/15
255 400 04/02/15 3,978.86
321 410 04/10/15 6,455.60
Table 6. Sales Journal Table.
12. After you create the SalesTable, populate the table with the Sales Journal data in Table 7
a.) Save the database and table often while you are entering each data field.
b.) Be aware of the field sizes as you are entering each data field.
c.) 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.
13. 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.
a.) Name the CustIDTable CustomerInputForm and the SalesTable SalesInputForm.
14. On the database tools tab, click the Relations Group.
a.) When you create a relationship, you enforce referential integrity.
b.) Select the two tables, SalesRepTable and CustIDTable, to be joined
15. To establish the relationship when you click the Relations Group, a dialog box, Edit
a.) In most cases, check the Enforce Referential Integrity.
b.) Check Cascade Update Related Fields.
c.) Establish a one to many relationship between the SalesRepTable (the 1 side) using
SalesRepID and the CustIDTable (the many side) using CustSalesRep.
16. After establishing the relationship between the SalesRepTable and the CustIDTable,
establish a relationship between the CustIDTable and the SalesTable as you did in Step
a.) Establish the one to many relationship between the CustIDTable (the 1 side) using
CustID and the SalesTable (the many side) using InvCustID.
17. Another optional relationship can be established between the SalesRepTable and the
18. Create a Query using the Query Wizard.
a.) Select the Simple Query Wizard.
b.) Select the following fields to be included in the wizard:
c.) Name the query Managers Sales Goal.
d.) 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.
19. Switch to Design View and click the Show Table Icon and ADD the SalesRepTable
to the Query.
20. 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):
21. Click Save to save your changes.
22. 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
23. Create a report using the Report Wizard.
a.) Click the Create tab.
b.) Select Report Wizard.
c.) Select the Managers Sales Goal Query from the Tables/Queries section drop down.
24. Select the following fields for your report from the query;
25. The report layout should be stepped and portrait.
26. Title the report Managers Sales Goal to Actual Report.
27. Click the Run icon to run the report.
Purchase answer to see full
Why Work with Us
Top Quality and Well-Researched Papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
Professional and Experienced Academic Writers
We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.
Free Unlimited Revisions
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.
Prompt Delivery and 100% Money-Back-Guarantee
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.
Original & Confidential
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.