Data base in Microsoft Access

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)
ms_access_2013_lab_one.docx

accsess.zip

Don't use plagiarized sources. Get Your Custom Essay on
Data base in Microsoft Access
Just from $13/Page
Order Essay

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. .
PART TWO:
1. Start Access – a Backstage view opens allowing you to select the database you want to
work with.
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.
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.
3.
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:
•
•
TotalCommRecvd
GoldClubComm
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
not required.
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
Field Name
•
•
•
•
•
•
•
•
•
•
CustID
CustName
CustAddrL11
CustAdrL2
CustCity
CustState
CustZip
SalesRepID
CustSalesArea
CustAnnualSales
Customer
ID Code
Name
215 ABC Company, Inc
225 Jolly Company
230 Flash, Inc
Data Type:
Length
Text (A/N)
Text (A/N)
Text (A/N)
Text (A/N)
Text (A/N)
Text (A/N))
Text (N)
Text (A/N)
Text (A/N)
Currency (N)
Address
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
5
20
20
20
15
2
9
5
1
Description:
Customer ID Number
Customer Name
Address Line 1
Optional Line 2
Customer City
Customer State
Fixed Zipcode 5 + 4
Sales Rep ID Code
Code = N-E-W-S
Annual Sales Volume
Sales
Rep ID
Sales
Area
Annual
Sales
122
N
25,122.75
122
N
31,543.65
105
E
55.678.90
265 Chuck-Fil-C
1900 Chicken Rd
A/P Department
Ellicott Run, VA
19288
288 Jump On It
345 Flashback Blvd
Wampum, DE 20088
310 Boom
Boom Room 16
Company
1 Quiet RD
Louder, MD 21011
330 Sell It
16th Floor
1 N Anywhere St
York, PA 11117
400 Sold It All, Inc
Wam Bam Bldg
19 Fourth St
Baltimore, MD 21224
410 Nunzio’s Hitem
357 Smith & Wesson
Magnum, SC 35700
Table 5. Customer Information.
210
N
1,975.55
270
W
56,789.10
350
S
10,222.75
397
S
18,299.45
122
N
44,512.37
397
S
100.350.50
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
Field Name
•
•
•
•
•
•
•
•
•
•
InvoiceNo
InvCustID
InvDate
InvAmount
InvTax
InvNet
InvCost
SalesRepID
InvCommPaid
InvCommRate
Invoice Cust
Inv
Total
Number ID
Date
Invoice
221 230 02/2/15 3,451.90
222 225 02/27/15
777.55
223 265 03/15/15
345.97
Data Type:
Text (A/N)
Text (A/N)
Date
Currency (N)
Currency (N)
Currency (N)
Currency (N)
Text (A/N)
Currency (N)
Number (N)
Tax
207.00
38.88
17.30
Net
Sale
3659.80
816.43
363.27
Length
5
5
10
5
Cost
2290.71
486.25
155.35
Description:
Sales Rep ID Number
Customer ID Code
Format mm/dd/yyyy
Total Invoice Amount
Tax on Invoice
Total Amt Less Tax
Cost of Goods Sold
Sales Rep ID Code
Commission Amount
3 decimals
Sale
ID
122
210
105
Comm
Paid
0.00
0.00
0.00
Comm
Rate
0.025
0.025
0.015
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
775.75
255 400 04/02/15 3,978.86
321 410 04/10/15 6,455.60
Table 6. Sales Journal Table.
73.80 1549078
109.85 2306.75
73.42 1297.11
46.55
822.30
238.72 4217.58
322.78 6778.38
1027.85
1688.94
867.34
432.75
2988.56
4975.25
270
122
350
210
122
397
0.00
0.00
0.00
0.00
0.00
0.00
0.025
0.025
0.030
0.025
0.025
0.030
12. After you create the SalesTable, populate the table with the Sales Journal data in Table 7
above.
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
Relationships, displays.
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
15 above.
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
SalesTable.
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:
(1)
(2)
(3)
(4)
(5)
ManagerIDNo
MgrLastName
MgrFirstName
MgrMidInit
MgrSalesGoals
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):
a.)
b.)
c.)
d.)
e.)
f.)
SalesRepID
RepLastName
RepFirstName
RepMI
RepSalesGoal
RepActSales).
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
the table.
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;
a.)
b.)
c.)
d.)
e.)
f.)
g.)
ManagerIDNo
MgrLastName
RepLastName
RepFirstName
RepMI
RepSalesGoal
RepActSales
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.
h.)

Purchase answer to see full
attachment

GradeAcers
Calculate your paper price
Pages (550 words)
Approximate price: -

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!

Calculate the price of your order

Total price:
$0.00

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.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

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.

Admissions

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.

Reviews

Editing Support

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.

Reviews

Revision Support

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.

Order your essay today and save 15% with the discount code DISCOUNT15