ACCESS 2013 LAB ONE – MODIFY A DATABASE

In this lab, you will: 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. 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 Workboo
access_lab_two___create_a_database_1.docx

access_lab_two___create_a_database_1.docx

Don't use plagiarized sources. Get Your Custom Essay on
ACCESS 2013 LAB ONE – MODIFY A DATABASE
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.
Create an Access Database and Query – Page 1
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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.
Create an Access Database and Query – Page 2
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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
Create an Access Database and Query – Page 3
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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
•
•
•
•
•
•
•
•
•
•
Data Type:
InvoiceNo
InvCustID
InvDate
InvAmount
InvTax
InvNet
InvCost
SalesRepID
InvCommPaid
InvCommRate
Text (A/N)
Text (A/N)
Date
Currency (N)
Currency (N)
Currency (N)
Currency (N)
Text (A/N)
Currency (N)
Number (N)
Invoice Cust
Inv
Number ID
Date
221
230 02/2/15
222
225 02/27/15
223
265 03/15/15
Total
Invoice
3,451.90
777.55
345.97
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
Create an Access Database and Query – Page 4
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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
109.85
73.42
46.55
238.72
322.78
1549078
2306.75
1297.11
822.30
4217.58
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.
Create an Access Database and Query – Page 5
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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.
Create an Access Database and Query – Page 6
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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.) Turn in all of your work through Blackboard.
Create an Access Database and Query – Page 7
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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.
Create an Access Database and Query – Page 1
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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.
Create an Access Database and Query – Page 2
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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
Create an Access Database and Query – Page 3
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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
•
•
•
•
•
•
•
•
•
•
Data Type:
InvoiceNo
InvCustID
InvDate
InvAmount
InvTax
InvNet
InvCost
SalesRepID
InvCommPaid
InvCommRate
Text (A/N)
Text (A/N)
Date
Currency (N)
Currency (N)
Currency (N)
Currency (N)
Text (A/N)
Currency (N)
Number (N)
Invoice Cust
Inv
Number ID
Date
221
230 02/2/15
222
225 02/27/15
223
265 03/15/15
Total
Invoice
3,451.90
777.55
345.97
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
Create an Access Database and Query – Page 4
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
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
109.85
73.42
46.55
238.72
322.78
1549078
2306.75
1297.11
822.30
4217.58
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.
Create an Access Database and Query – Page 5
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
18. Create a Query using the …
Purchase answer to see full
attachment

Order a unique copy of this paper
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency

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