Chat with us, powered by LiveChat MIS303 Business Information Systems Microsoft Access and Excel Project | Abc Paper
+1(978)310-4246 credencewriters@gmail.com
  

all information required to complete assignment is in the pdf file attached. please make sure to complete the full project, the answer should include a microsoft excel and a microsoft access file.
project.pdf

Unformatted Attachment Preview

MIS 303 Introduction to Business Information Systems
Project
1. All assignments must be submitted via Blackboard by the deadlines in order to receive credits.
2. If you missed a deadline, you will not receive credits for the missed assignment.
3. It is your responsibility to ensure that your submitted solution satisfies all requirements. If your submitted
solution failed to satisfy any of the requirements, you will not receive credits. There is no such thing as
“partially correct solution”. A solution is either correct or incorrect.
What to submit for grading?
 Your completed .xlsx file
 Your completed .accdb file
Project Overview
In this project you will use Microsoft Excel to create a flat database (spreadsheet) and use Microsoft Access
to create a relational database. This database will include tables that store the following information pertinent
to a corporation/business:
 Employees
 Customers
 Products
 Orders
You will also setup the relationships between tables, and design/create queries and a report.
Project Overview
This project has been organized into 6 different parts:
1. Creating a Flat File database (spreadsheet file) using MS Excel
2. Importing existing data from an Excel Spreadsheet (created in Part 1) into MS Access database.
 This part will create the database table EmployeesTbl and import records into the table.

If you have difficulty with this part, you will have to skip this part and
o Create EmployeesTbl by yourself
o Enter data records by yourself
3. Creating additional database Tables: CustomersTbl, ProductsTbl, OrdersTbl
4. Establishing table Relationships
5. Building Queries to access specific data
6. Creating a Report
Step-by-step instructions are provided in the following pages.
You are not required to follow these steps in order to complete the project.
It is your responsibility to make necessary adjustments to complete this project.
Page 1 of 17
 Part 1 – Creating a Spreadsheet File
Use MS Excel to create a spreadsheet to contain 10 rows/employees, as shown in the screenshot below.
Your records/rows/employees DO NOT need to be the same as shown. But, your spreadsheet must have:

5 female employees

ONLY 5 different job positions
Save your workbook/file as YourLastNamePJ.xlsx
 Part 2 – Importing data from an Excel Spreadsheet
This part will create the database table EmployeesTbl and import records into the table.
 If you have difficulty with this part, you will have to skip this part and
o Create EmployeesTbl by yourself
o Enter data records by yourself
 Step-by-step instructions are provided in the following pages.
 You are not required to follow these steps.
 It is your responsibility to make necessary adjustments to complete this part of the project.
1. Open Microsoft Access and create a blank database and name your file as YourLastNamePJ.accdb.
Page 2 of 17
2. Click on the External Data tab. Click on the Excel (Import Excel spreadsheet) button.
3. In the Get External Data window, use the Browse… button to locate and select the .xlsx file created in
Part 1. Select the Import the source data into a new table in the current database option. Click the
OK button.
4. In the Import Spreadsheet Wizard window:
a) Check the box First Row Contains Column Headings. Click the Next > button.
Click OK, if the following windows pops up.
b) Select the No column in the table. Under Field Options, change the Field Name to
EmployeeNum. Click the Next > button.
Page 3 of 17
c) Select the Choose my own primary key. radio button. Using the pull-down, select the
EmployeeNum option. Click the Next > button.
d) Note: A primary key field is used to uniquely identify each record in a database table.
e) For the Import to Table textfield, enter EmployeesTbl. Click the Finish and Close button.
5. Open your Employees table in Design View.
Page 4 of 17
6. Delete the Gender and Salary fields by right clicking on the field row and selecting the Delete Rows
option. Click the Yes button to permanently delete the selected field.
7. Add a new field to the table by typing Employee Status in the first empty cell in the Field Name
column. Set the Data Type to the Lookup Wizard… option.
8. In the Lookup Wizard window:
a) Select the I will type in the values that I want. radio button. Click the Next > button.
b) Under Col1, type in the following three options in the first three cells: Full-time, Part-time, and
On-call. Click the Next > button.
Page 5 of 17
c) Check the Limit To List checkbox. Click Finish.
9. Close Design View window and save your changes.
10. Open the EmployeesTbl in the Datasheet View.
11. Add a status to each of the employees in the table. To add a status, you can simply click in the Employee
Status cell for each employee and utilize the lookup values provided by clicking on the pull-down menu.
(Your records do not need to be the same as shown in the figure.)
Page 6 of 17
 Part 3 – Creating Additional Database Tables
 Step-by-step instructions are provided in the following pages.
 You are not required to follow these steps.
 It is your responsibility to make necessary adjustments to complete this part of the project.
In part 3, you will create three additional database tables that will store Customer, Product, and Order
information: CustomersTbl, ProductsTbl, OrdersTbl.
You create a database table that will store all customer-related information (at least 10 entries).
Shown below is a sample customer table:
1. Click Create tab and click Table Design.
2. Enter the following Field Names and Data Types.
Field Name
CustomerNum
CustomerName
Address
City
State
Zip
Telephone
Fax
Data Type
AutoNumber
Short Text
Short Text
Short Text
Short Text
Short Text
Short Text
Short Text
3. Set up the primary key field by selecting the CustomerNum field and then selecting the Primary Key
icon under the Design tab.
Page 7 of 17
4. Save the table and name it CustomersTbl.
5. Set up an Input Mask for the Telephone field. An input mask specifies a pattern, e.g. (213) 740- 4542, for all
data to be entered in the field. Open Design view on CustomerTbl.
a) Click on the Telephone row in the CustomersTbl.
b) Click on the General tab and click on the Input Mask row and then click on the
(Builder) button to
start the Input Mask Wizard.
c) In the Input Mask Wizard window, select the Phone Number option and click Next.
d) For the Input Mask, the textfield should have !(999) 000-0000 in it. Click Finish.
Page 8 of 17
6. Set up an Input Mask for the Fax field.
7. Close Design View and save. Open Datasheet View for CustomersTbl table and enter information for 10
customers. Use the tab key to move from cell to cell. Notice that CustomerNum is automatically
generated by the software.
8. Close the Datasheet View – the information will be saved automatically.
Now you will create a Products table for products and their prices. The figure below shows a sample of how
the ProductsTbl table will look like.
9. Create the Products table using the following field names and data types:
Field Name
ItemNum
ItemName
RetailPrice
Data Type
AutoNumber
Short Text
Currency
10. Make the ItemNum the Primary Key.
11. Save the table and name it as ProductsTbl.
12. Switch to the Datasheet View and enter at least 7 different products into the table.
Create the Orders table that will store all order-related information. Here is a sample of how the Orders
table will look like:
Page 9 of 17
Values
13. Create the Orders table using the following field names and data types:
Field Name
OrderNum
OrderDate
ItemNum
Quantity
CustomerNum
EmployeeNum
Data Type
AutoNumber
Date/Time
Number
Number
Number
Number
14. Set the OrderNum as the primary key.
15. Add an Input Mask for the OrderDate field and select the Short Date option.
16. Save the table and name it OrdersTbl.
Page 10 of 17
17. Set up a Lookup Values for the ItemNum such that it will look up values for the ItemNum from the
ProductsTbl.
a) In the Design View for OrdersTbl, select the
ItemNum field.
b) Click on the Lookup tab.
c) For the Display Control, select the pull-down and choose the List Box option.
d) Set the following options:
List Box
ProductsTbl – The table to link to for information
– The column of the ProductsTbl which contains
the information to put into the ItemNum field
– The number of columns to display information
from the ProductsTbl when using the Lookup Value
(pull-down) in Datasheet View
0.25”;1” – The respective column widths to display
information (the number of widths will match the
Column Count)
Page 11 of 17
18. Set the Lookup Values for CustomerNum field as follows:
CustomersTbl – The CustomerNum field is linked
to the CustomerTbl
– The column of the CustomerTbl which contains
the information to put into the CustomerNum field
– The number of columns to display
0.25”;1” – The appropriate column widths to
display information
19. Set the Lookup Values for the EmployeeNum field as follows:
EmployeesTbl – The EmployeeNum field is linked
to the EmployeesTbl
1 – The column of the EmployeesTbl which contains
the information to put into the EmployeeNum field
4 – Display information from four fields:
EmployeeNum, Title, First Name, and Last Name
0.25”;0”;.75”;1” – Column widths, but set the
second one to 0 so it won’t display the Title
20. Close Design View and save.
21. Open the Datasheet View and enter at least 6 different orders into the table. Make sure there are
at least 4 orders for March 2012.
22. Save and close the table.
Page 12 of 17
 Part 4 – Establishing Table Relationships
 Step-by-step instructions are provided in the following pages.
 You are not required to follow these steps.
 It is your responsibility to make necessary adjustments to complete this part of the project.
Once you create your four tables with the appropriate number of fields, key fields and field attributes, you must setup relationships between the tables including Enforce Referential Integrity. Referential integrity is a set of rules
that MS Access enforces to maintain consistency between related tables when you update data in a database.
Next, you will establish relationships between the four tables. Refer to the diagram shown below:
1. Click the Database Tools tab and click on the Relationships option.
2. Click on the Show Table button. Select all four tables (use the Shift key to multiple select). Click the
Add button.
3. Arrange the tables as shown above.
4. Click, hold, and drag the CustomerNum field in the CustomersTbl over the CustomerNum field in the
OrdersTbl. Let go.
5. In the Edit Relationships window, checkmark the Enforce Referential Integrity checkbox. Click the
Create button.
6. Click, hold, and drag the ItemNum field in the ProductsTbl over the ItemNum field in the
OrdersTbl. Let go.
7. In the Edit Relationships window, checkmark the Enforce Referential Integrity checkbox. Click the
Create button.
8. Click, hold, and drag the EmployeeNum field in the EmployeesTbl over the EmployeeNum
field in the OrdersTbl. Let go.
9. In the Edit Relationships window, click on the Create button. (Do not enforce referential
integrity.)
10. Click on the Close button to close the relationships and save your changes.
Page 13 of 17

Part 5 – Building Queries to Access Data
 Step-by-step instructions are provided in the following pages.
 You are not required to follow these steps.
 It is your responsibility to make necessary adjustments to complete this part of the project.
You will create two queries. Both queries will extract data from at least two of the tables and sort the resulting
information to be displayed. Both queries will also include a formula.
1. Click on the Create tab.
2. Click on the Query Wizard option.
a) In the New Query window, select the Simple Query Wizard option. Click the OK
button.
b) For the Tables/Queries option, select Table: OrdersTbl. Select the following two fields:
OrderDate and Quantity.
c) For the Tables/Queries option, select Table: ProductsTbl. Select the following two fields:
ItemName and RetailPrice. Click the Next button.
d) Select the Detail radio button. Click the Next button.
e) For the title of your query, enter Orders for March Query. Select the Modify the query design.
radio button. Click the Finish button.
3. Select the Quantity column and drag it between the ItemName and RetailPrice columns.
4. Within the OrderDate column, set the Criteria for the query by typing Between #3/1/12# And
#3/31/12# in the cell on the Criteria row.
5. Sort the information by date by selecting Ascending from the pull-down on the Sort row within the
OrderDate column.
6. Add a new column of information for the Total. On the Field row in the first black column, enter the
following: Total:[Quantity] *[RetailPrice]
Page 14 of 17
7. Switch to the Datasheet View to view the query results.
Create a second query to list orders by customers. A sample is shown here.
8. Using the Simple Query Wizard, create a query named Orders by Customers Query.
9. The fields for your query will be taken from three tables:
Table
CustomersTbl
CustomersTbl
OrderTbl
OrderTbl
ProductsTbl
Field
CustomerNum
CustomerName
ItemNum
Quantity
RetailPrice
10. Create a field for Total and use an appropriate formula to calculate the price.
11. Place the columns of information in the order shown in the sample above.
12. Sort the information by both customer name and total price. The CustomerName should be sorted in
Ascending order and the Total in Descending order.
13. Switch to the Datasheet View to view the query results.
Page 15 of 17
 Part 6 – Creating a Report
 Step-by-step instructions are provided in the following pages.
 You are not required to follow these steps.
 It is your responsibility to make necessary adjustments to complete this part of the project.
You will create one report that collects at least one field of data from three of the tables. Formulas will be used to
keep a running total and to calculate a grand total for the report. The name of your report will be lastname
firstname created mm/dd/yy.
You will create a report of Accounts Receivables, in landscape format, using the Reports Wizard. Add formulas
for Totals, Running Totals, and a Grand Total. You may want to review the helpful design hints given in Part 4.
1. Click on the Create tab.
2. Click on the Report Wizard.
a) The fields for your query will be taken from three tables. Add them in the following order:
Table
Field
CustomersTbl
CustomerName
ProductsTbl
ItemName
OrdersTbl
Quantity
ProductsTbl
RetailPrice
b) For viewing the data, select by OrdersTbl.
c) For sorting, select CustomerName in Ascending order.
d) For the Layout, select Tabular.
e) For the Orientation, select Landscape.
f)
For the Title, enter lastname firstname created mm/dd/yy.
g) Select the Modify the report’s design before clicking on the Finish button.
Page 16 of 17
3. Add the Total label to your report using the Label tool.
a) Click on the Design tab. In the Controls section, select the Label icon (Aa).
b) Draw the label object within the Page Header section of the report.
c) In the Label box, enter Total.
d) Adjust the size and location of the new object.
4. Add the Total field to your report using the Text Box tool.
a) In the Design tab in the Controls sections, select the Text Box icon.
b) Draw the field object within the Detail section of the report.
c) Access created a Text label within the Detail section. Delete this Text object by right- clicking on it and
selecting the Delete option.
d) Select the new Unbound control object. In the Property Sheet, select the Data tab. Set the Control Source
property to =[Quantity]*[RetailPrice]
e) In the Property Sheet, select the Format tab. Set the Format property to Currency.
f)
Adjust the size and location of the new field object as shown in the sample.
5. Add the Running Total objects to the report by adding a Label in the Page Header section and a Text Box in the
Detail section.
a) Repeat steps 3 and 4 you did for the Total label and Total field.
b) Additionally, for the Running Total field object, in the Property Sheet in the Data tab, set the Running Sum
property to Over All.
6. Update the Page Footer section.
a) Remove any objects that are currently in the Page Footer section.
b) Add a horizontal line to the top of the section. In the Design tab in the Controls section, use the Line option.
You can format it by selecting on the Format tab.
c) Add a Grand Total text box. For the Grand Total label, change the text to Grand Total.
d) Select the Grand Total field object. In the Property Sheet, select the Data tab. Set the
Control Source property to the name of the Running Total field object. For example:
=[Text15]
Note: your text number may be different. Check the Name property of the Running Total object on your report
to get the correct name.
e) In the Property Sheet, select the Format tab and set the Format property to Currency.
7. In the Report Header section, change the title of the report to Accounts Receivable by your name
8. Switch to the Report View and verify that your report resembles the sample and that all values are calculated and
displayed properly.
9. Return to the Design View to make any necessary corrections.
10. Save and close the report.
Page 17 of 17

Purchase answer to see full
attachment

error: Content is protected !!