Relational Database Management Systems (RDBMS) such as Microsofts 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. (all the instructions are in the attachment below) please read it
Unformatted Attachment Preview
Purpose: Relational Database Management Systems (RDBMS) such as Microsofts 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
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
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
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.
1. Start Access a Backstage view opens allowing you to select three methods for creating
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:
b.) Click the OK button.
c.) Save the database and table.
Data Types and Uses
Stores alphanumeric data: alphabetic character,
numbers, and special characters
John Jones, Sr.
Length text such as sentences, etc.
Contains numbers that can be used in
calculations, etc., including decimal points and
Holds dates or times that can be used in
Contains monetary values.
AutoNumber A sequential unique number assigned to a
record every time you add a record to the table.
1, 2, 3, etc.
A Boolean field with values such as Yes/No,
True/False, and On/Off.
Contains an object from another application
such as spreadsheets, pictures, audio/sounds,
A web address (URL) or a path to a folder or
Handles multiple spreadsheet files, images, etc. Excel workbook
The results of an expression from one or more
Creates a field that allows you to choose a
value from another table or to set up a list box
or combo box to search for values.
InvProdCost to look
up unit price from
the Inventory table.
Table 1. Data Field Data Types and Uses.
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 its 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>
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:
Assigns the maximum length of a text field or the format of a
Changes the way a field is displayed or printed but does not
change how the data is stored.
Provides a mechanism that facilitates data entry operations such as
displaying hyphens for a Social Security, telephone number or
slashes for a data field.
Permits an alternate name that will be displayed, printed, etc., on
datasheets, forms, and reports.
Automatically enters a predetermined value when a new record is
being added to the table.
Allows data to be verified against defined rules during data entry.
The error message to be displayed when the Validation Rule fails.
Indicates whether a value must be entered in the field.
Allows entry of zero length text strings, for Hyperlinks, Short,
or long text strings.
Defines a field as a Primary (No Duplicates) or Secondary key
(No Duplicates or Duplicates Allowed).
Used on calculate fields. Enter he expression to be calculated.
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
Sales Rep ID Number
Sales Rep Last Name
Sales Rep First Name
Sales Rep Mid Init
Sales Rep Sales Goal
Act Rep YTD Sales
Rep Gold Club?
Rep Gold Comm Rate
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.
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.
Table 4. Sales Rep Table Data.
7. After you have populated the Sales Rep Table, create your next table by returning to the
Main Access Tool bar.
8. To create the next table:
Click the Create button.
Click the Table button.
Click the Access Main button.
Click the Save As button.
Rename the table
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
Mgr Last Name
Mgr First Name
Mgr Middle Initial
MgrAct YTD Sales
Mgr Comm Rate
Mgr Received Gold
Mgr Gold Comm Ra
Manager Last Name
Table 5. Sales Manager Table.
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.) Tables are normally joined in a one-to-many relationship.
b.) One to many relationships enforce referential integrity.
c.) A foreign key is a field in one table that is a Primary Key in another table.
d.) 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
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
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).
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.