Description
This is a two part lab assignment. In part one, you will create a database and two tables; a Sales Rep Table and a Sales Rep Manager Table. Input data forms for each table will be created with the two tables populated with data listed in Table 1 and 2 below. A relationship between will be established between the two tables based on Sales Manager ID with a query set up to show Sales Rep Manager and the assigned Sales Rep. In 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.
MS ACCESS 2013 LAB ONE – CREATE A DATABASE
Purpose: Relational Database Management Systems (RDBMS) such as Microsoft’s Access
contains many objects such as tables, queries, forms, and reports. Each of these objects
allow you to process, update, maintain, and store information in distinct tables within
the database. Tables within the database can be related to other tables within the same
or different database system using what is referred to as a “foreign” key.
Keys are important data elements and components within a database. For example, a
“primary” key is a unique identifier assigned to each record in the database. Customer
Number, Social Security Number, Credit Card Number are examples of a primary key
that could be assigned to a Customer, Social Security Recipient or Credit Card Holder.
Thus, the uniqueness of the primary key assigned identifies a single record in the
database. This record, in turn, can and may contain one or more “foreign” keys to
records in other tables within the same or a different database.
Access supports creating/maintaining a database:
•
•
•
•
With or without templates
On a Server using SharePoint
Copying, importing, appending, or linking data from another source into an
Access Table
Adding an application interface.
A few comments:
•
•
•
•
Once you start Access and begin working in a database, you can add fields,
tables, and/or application parts to your project.
An application part provides a powerful tool that allows you to incorporate
various “object” as part of your Access project. For example, an application
part could be a table and a form, query, or report based on the table.
Template can be a useful tool that reduces development time to make your
Access project available for use. This requires you to analyze the feasibility
and practicality of a template satisfying what you are trying to achieve. The
drawback to using a template is that templates have a predefined data structure
that might not satisfy what you are doing and therefore might require a
significant amount of work to be expended to alter the structure to suit your
needs.
You may elect to not use a template if you are importing data from another
source such as Excel
Assignment: This is a two part lab assignment. In part one, you will create a database and two
tables; a Sales Rep Table and a Sales Rep Manager Table. Input data forms for
each table will be created with the two tables populated with data listed in Table 1
and 2 below. A relationship between will be established between the two tables
Create an Access Database and Query – Page 1
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
based on Sales Manager ID with a query set up to show Sales Rep Manager and
the assigned Sales Rep.
In 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 will be given you next week.
PART ONE:
1. Start Access – a Backstage view opens allowing you to select three methods for creating
a database:
a.) A custom web-based application.
b.) A blank desktop database.
c.) A database from a template.
NOTE: You will create a blank database.
2. Click the File tab and then the New button.
a.) In the Backstage view, click Blank desktop database.
3. In the text box, enter the database name as follows:
Last Name_First Name InitialMiddleName Initial.
NOTE: It is recommended you frequently save your database on either your “H”
drive or preferably on a flash drive. A flash drive allows you to work on the
project outside of school on another computer.
When you create a new database in Access 2013, a default table called
“Table1” will be created as well.
a.) Click the Browse button to navigate to the Drive, Folder, flash drive, etc., where you
want to save your database and then click OK.
b.) .After naming the database, click the “Create” button.
4. Select Design View from the View tool bar tab.
a.) Change the name of the default table to:
SalesRepTable
Create an Access Database and Query – Page 2
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
b.) Click the OK button.
c.) Save the database and table.
Data Types and Uses
Data Type
Short Text
Description
Stores alphanumeric data: alphabetic character,
numbers, and special characters
Long Text
Length text such as sentences, etc.
Number
Contains numbers that can be used in
calculations, etc., including decimal points and
negative signs.
Date/Time
Holds dates or times that can be used in
date/time arithmetic.
Currency
Contains monetary values.
AutoNumber A sequential unique number assigned to a
record every time you add a record to the table.
Yes/No
A Boolean field with values such as Yes/No,
True/False, and On/Off.
OLE Object Contains an object from another application
such as spreadsheets, pictures, audio/sounds,
and graphics
Hyperlink
A web address (URL) or a path to a folder or
file.
Attachment
Handles multiple spreadsheet files, images, etc.
Calculated
The results of an expression from one or more
fields
Lookup
Creates a field that allows you to choose a
Wizard
value from another table or to set up a list box
or combo box to search for values.
Table 1. Data Field Data Types and Uses.
Example
John Jones, Sr.
Sentences, etc.
123.45-
1/1/2015
1:00:05 AM
$2,350.00
1, 2, 3, etc.
Yes
JPEG Image
http://www.towson.
edu
Excel workbook
path/name
GrossPay*HourlyR
ate
InvProdCost to look
up unit price from
the Inventory table.
5. In Design View, set up the SalesRepTable entering the data fields in the order listed
below and named as indicated.
NOTE 1: In design View, the data field definition screen allows you to both define
the data field name and it’s attributes. Some attributes are easy to assign.
For example, SalesRepID, Last Name, First Name are required. Middle
Initial is not. Money and similar type fieds should be initialized with
zeroes. Of interest, the “Caotion” entry is the information that will be
displayed on Forms, Reports, etc. You should think abour and analyze
what you are doing and act. If you have any questions about any entry,
ask your instructor what to do>
Create an Access Database and Query – Page 3
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
The Table Property Types table below defines various data elements and
what can be assigned, entered, etc., in the field. Explore entries such as
Validation Rule and Validation Text by going online and using Google or
the Help available in Access. Use some of your ingenuity and creative skills
when working with the assignment.
Common Table Property Types and Description include:
Property Type
Field Size
Description
Assigns the maximum length of a text field or the format of a
number field.
Format
Changes the way a field is displayed or printed but does not
change how the data is stored.
Input Mask
Provides a mechanism that facilitates data entry operations such as
displaying hyphens for a Social Security, telephone number or
slashes for a data field.
Caption
Permits an alternate name that will be displayed, printed, etc., on
datasheets, forms, and reports.
Default Value
Automatically enters a predetermined value when a new record is
being added to the table.
Validation Rule Allows data to be verified against defined rules during data entry.
Validation Text
The error message to be displayed when the Validation Rule fails.
Required
Indicates whether a value must be entered in the field.
Allow Zero
Allows entry of zero length text strings, “” for Hyperlinks, Short,
Length
or long text strings.
Indexed
Defines a field as a Primary (No Duplicates) or Secondary key
(No Duplicates or Duplicates Allowed).
Expression
Used on calculate fields. Enter he expression to be calculated.
Result Type
Handles the format for calculated results.
Table 2. Data Field Property/Data Field Types and Type Descriptions.
Data Field List for the Sales Rep Table
Field Name
•
•
•
•
•
•
•
•
•
Sales RepID
RepLastName
RepFirstName
RepMI
RepSalesGoal
RepActsales
RepCommRate
RepGoldClub
RepGoldClubCommRate
Data Type:
Text (A/N)
Text (A/N)
Text (A/N)
Text (A/N)
Currency (N)
Currency (N)
Number (N)
Yes/No
Number (N)
Length
5
16
12
1
Create an Access Database and Query – Page 4
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
Description:
Sales Rep ID Number
Sales Rep Last Name
Sales Rep First Name
Sales Rep Mid Init
Sales Rep Sales Goal
Act Rep YTD Sales
Comm Rate
Rep Gold Club?
Rep Gold Comm Rate
•
ManagerIDNo
Text (A/N)
5
Sales Rep Mgr ID
NOTE: Currency are in the format 9,999,999.99± except for the commission rate
fields. The format for commission is 0.999. In the lower half of the data
field definition screen under “Auto” select the decimal places desired.
The Commission Rate fields are defined as Number (N- Numeric). Enter
the format as Currency and the number of decimal places to 3. This
applies to the SalesRepTable and the SalesMgrTable.
a.)
b.)
c.)
d.)
Make the SalesRepID the Primary Key field.
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. For example,
you can test to see if RepActSales is equal to or greater than RepSalesGoals to place a
Y or an N in RepGoldClub.
6. Create a Data Entry Form for this table by clicking the Create tab on the Access Main
tool bar and then clicking the Form button.
a.) Name the form SalesRepInputForm.
b.) Enter the data listed in Table 4 below to populate the Sales Rep Table.
Sales
Rep ID
Last Name
First
Name
105 Adams
Joseph
122 Cuddington
Thomas
158 Baff
Andrew
195 Adala
Mary
210 Smith
John
225 O’Hare
Louis
270 Notto
Joseph
330 Gordon
Lewin
355 Lewis
Maurice
370 Petri
Wendell
397 Dee
Louis
420 Caramanico
Janice
Table 4. Sales Rep Table Data.
Mid
Init
A
R
U
S
J
N
S
H
K
J
R
F
Sales
Goal
50,000
75,000
55,000
50,000
35,000
50,000
75,000
80,000
40,000
75,000
35,000
45,000
Actual
Sales
38,225.70
82,125.90
47,550.15
52,257.75
36.666.75
47,750.55
72.450.85
85,123.45
32,848.90
74,995.50
38,987.65
49,255.90
Com
m
Rate
0.015
0.020
0.020
0.020
0.020
0.020
0.025
.0.025
0.015
0.020
0.025
0.025
Gold
Club
N
Y
N
Y
Y
N
N
Y
N
N
Y
Y
Gold
Com
Rate
0.020
0.025
0.025
0.025
0.025
0.025
0.025
0.030
0.020
0.025
0.030
0.030
7. After you have populated the Sales Rep Table, create your next table by returning to the
Main Access Tool bar.
Create an Access Database and Query – Page 5
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
Mana
ger ID
152
110
110
215
215
152
110
325
152
325
215
325
8. To create the next table:
a.)
b.)
c.)
d.)
e.)
Click the Create button.
Click the Table button.
Click the Access Main button.
Click the Save As button.
Rename the table
SalesMgrTable
f.) Click the OK button.
g.) Save the Table and Database.
h.) Follow the procedure starting from Step 5 above through Step replacing the Data
Field List in Step 4 above with the Data Field List below for the Sales Managers
Table and populate the table with the data in Table 5 below.
i.) When you get to the create form step, name the form SalesMgrInputForm.
j.) After completing this operation, go to Step 9 below.
Data Field List for the Sales Rep Managers Table
Field Name:
Data Type:
Length
•
•
•
•
•
•
•
•
•
Text
Text
Text
Text
Currency
Currency
Number (N)
Yes/No
Number (N)
5
16
12
1
ManagerIDNo
MgrLastName
MgrFirstName
MgrMidInit
MgrSalesGoals
MgrActSales
MgrCommRate
MgrGoldClub
MgrGoldCommRate
Manager Last Name
ID
First
Name
110 Jones
Mary
152 Brown
Peter
215 Johnson
Andrew
325 Caligula
Charles
Table 5. Sales Manager Table.
Mid Sales
Init Goal
Actual
Sales
K
A
J
B
635,200.90
470,128.55
488,230.50
525,127.75
525,000
475,000
500,000
495,000
Create an Access Database and Query – Page 6
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
Description:
Manager’s ID
Mgr Last Name
Mgr First Name
Mgr Middle Initial
MgrSales Goal
MgrAct YTD Sales
Mgr Comm Rate
Mgr Received Gold
Mgr Gold Comm Ra
Com
Gold
m
Club
Rate
0.027
Y
0.020
N
0.015
N
0.025
Y
Gold
Comm
Rate
0.350
0.020
0.020
0.035
9. In a relational database, the same field can be in 2 or more tables and thus a relationship
can be established between the tables.
a.)
b.)
c.)
d.)
Tables are normally joined in a one-to-many relationship.
One to many relationships enforce referential integrity.
A foreign key is a field in one table that is a Primary Key in another table.
The ManagerIDNo in the SalesRepTable is the foreign key and a common field with
the SalesMgrTable Primary Key ManagerIDNo.
e.) This commonality allows you to join the two tables and thus access the data from
both tables.
10. 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 SalesMgrTable, to be joined
11. 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 SalesMgrTable (the 1 side) and the
SalesRepTable (the many side).
12. Turn in all of your work through Blackboard.
Create an Access Database and Query – Page 7
Copyright © Dominic M, Mezzanotte, Sr., April, 2015
Purchase answer to see full
attachment