Homework answers / question archive / Organizational Background Consultants regularly work on a project-by-project basis with clients, where they provide their particular expertise to address various challenges their clients face

Organizational Background Consultants regularly work on a project-by-project basis with clients, where they provide their particular expertise to address various challenges their clients face

MS Access

Share With

---

Organizational Background

Consultants regularly work on a project-by-project basis with clients, where they provide their particular
expertise to address various challenges their clients face. As consultants tend to specialize in specific
types of work, they usually join firms that employ many consultants so that clients can come to one
place to address their numerous needs.

To assist such a firm, you are to design and configure a MS Access database that facilitates the following
business processes:

1. Once a consultant joins the firm, their personal information is recorded along with the type of work
they specialize in. Based on their specializations, consultants are assigned to specific projects
(Process, Systems, or Data).

2. When a new client contacts the firm, their contact information is stored along with a primary point
of contact. The point of contact handles any communication between the client company and the
firm.

3. When a client agrees to a certain scope of work, a project is entered into the system and consultants
are assigned.

As part of the initial design, you sketch out the following Entity-Relation Diagram, knowing that some
changes might occur when configuring the database:

CONSULTANTS Bc O< PROJECTS BO) O CLIENTS
Furthermore, you collect a preliminary list of attributes that these tables will need to capture. The
details of these attributes can be found on the next page.

You decide to use Microsoft Access since the firm is in need of a quick and simply system for tracking
and assigning consultants to client projects, with the intention to develop a more sophisticated system
later. In developing the database and interface, you will need to:

#" Create & populate tables

® Link tables together through lookups and relationships

=" Create queries to display results

=" Create forms for data entry

® Create reports for tables and queries

® Create a navigation menu for the database

Database Table Descriptions
CONSULTANTS | |
Attribute ‘Type Sie | Index Comments |
Consultant_ID Number a ger Dali ates) Primary Key
Consultant_Last_Name | Short Text 30 | |
Consultant_First_Name Short Text 30 | |
Use Expression Builder to
Consultant_Full_ Name Calculated concatenate the First and Last
| | Names. |
Consultant_Address —_ Long Text | . |
Consultant_Phone Short Text 15 Use the default Phone Number
| . | _input mask. |
-Process_Assignments Yes/ No |
Systems_Assignments Yes/No | |
_Data_Assignments Yes/No | . .
| CLIENTS
Attribute Type Size Index Comments
; Yes / Unique/ ;
Client_ID Number Long Integer No Duplicates Primary Key
Client_Company_Name Short Text 50
Client_Last_Name Short Text — 30 |
Client_First_Name Short Text — 30 . |
Use Expression Builder to
Client_Full_Name Calculated concatenate the First and Last
| _Names. |
Client_Address Long Text _ |
Client_Phone Short Text 15 Use Default Phone Number Input
| . . Mask |
PROJECTS . | . |
| Attribute Type  «*Size Index Comments |
; Yes / Unique/ No ;
Project_ Number AutoNumber tong Integer ‘Duplicates ‘Primary Key
Project_Title Short Text . |
Project_Description Long Text |
Project_Type Short Text Lookup-based
Consultant Number Long Integer Foreign Key (Lookup-based)
Client _Number _Long Integer | Foreign Key (Lookup-based) _
Date_Begin Date/Time _Format: Medium Date |
Date_End Date/Time Format: Medium Date

 

Requirement 01: Tables (20 Points)

Background:

To start developing your database, you will need to create a new Microsoft Access database file. Within
this file you will need to create and populate tables with data, based on the table descriptions provided.
Instructions:

Requirement 1.1: CLIENTS Table (4 Points)

e Referring to the table descriptions on the prior page, create a CLIENTS table that can store client
data.

Requirement 1.2: CONSULTANTS Table (4 Points)

e Referring to the table descriptions on the prior page, create a CONSULTANTS table that can store
consultants’ data, including their specializations.

Requirement 1.3: Populate the CLIENTS and CONSULTANTS Tables (2 Points)

e Make up names and other pertinent information for 6 consultants (including yourself as one of
them) and 8 clients. Populate the CLIENTS and CONSULTANTS tables with this sample data before
proceeding to the next requirement.

Requirement 1.4: PROJECTS Table (8 Points)

e Referring to the table descriptions on the prior page, create one or more tables that will allow you

to store the list of firm projects and the consultants who are working on them.
Requirement 1.5: Populate the PROJECTS Table (2 Points)
e Add 10 different jobs to your newly created JOBS table, making up the dates, titles, and
descriptions. Create three jobs where you are the consultant.
Requirement 02: Table Relationships (8 Points)
Background:
In order to store data efficiently in the database, you will need to form relationships between the tables
you have created.
Instructions:
1. Using the Relationships view and Lookup fields, form relationships between the tables. Ensure that
“Enforce Referential Integrity” and as well as the two cascade options are selected.

 

Requirement 05: Database Enhancement (10 Points)

Background:

While your initial design of the database will help the firm manage their projects, further enhancements
could be made. Aside from the initial requirements outlined previously, you are going to implement
additional queries and forms for management to consider. To convince them of their utility, you are
going to provide a brief report describing these enhancements.

Instructions:

Referring to the directions below, update the database file and prepare a brief report:

1. Create an additional query in the database that uses the Totals Function, while using a
combination of fields from at least 2 tables in this database. Provide a short description of the
purpose and benefit of this query in your report. (2 Points)

2. Can you think of any additional queries that will be useful for the firm? Implement at least ONE
additional new query in the database and provide a short description of the purpose and
benefit of the query in your report. (3 Points)

3. Create a Consultant Management hierarchical form (main form/sub form) that uses
information from the Consultant table (Consultant_Full_ Name) in the main form and the
following fields in the sub form: PROJECT.Date_Begin (sort: Ascending order),
CLIENT.Client_Full_ Name. Provide a short description of the purpose this form could serve in
your report. (2 Points)

4. Can you think of a hierarchical form (main form/sub form) that will be useful for this database?
Implement at least ONE additional new hierarchical form in the database and provide a short
description of the purpose of the form in your report. (3 Points)

5. Your report must be professional in appearance and adhere to the following requirements:
© Include a Cover Page and Declaration of Academic Integrity
o Must be between 2-4 pages (excluding the Cover Page and Integrity Declaration)

o Normal 1” (2.54cm) margins on all sides.

©  Single-spaced with double-spacing between paragraphs.

© Size 11-12 font with your choice between: Time News Roman, Calibri, Trebuchet MS, Segoe
UI, Arial, Verdana.

© Page numbers in the Footer