Project #42226 - database design microsoft access project


 Access Database Assignment






This project includes the design and creation of a database and corresponding reports using Microsoft Access. There are three milestones to be submitted, one each in Modules Three, Five, and Nine. Each student will complete and submit his or her assignments individually, consisting of MS Access tables, relationships, forms, queries, and reports.




This project requires the use of Microsoft Access 2010.* Microsoft Access is available at no charge to IT students through Microsoft’s DreamSpark program. Refer to the information you received via email early in the term for information on the DreamSpark program.




*Microsoft Access 2010 should be used when not familiar with Access, as all tutorials and other aids are based on Access 2010. Access 2013 is acceptable for use by experienced users.




**Note for Mac users: As MS Access is not available natively for Macs, students will need to either complete the work on a Windows PC or set up a virtual Windows environment on their Mac. Virtualization software (VirtualBox) from Oracle Corp. and Microsoft Windows and MS Access (DreamSpark program) are available at no charge to students. Refer to the documentation on installation and setup of a virtual Windows environment.


Project Scenario


Wall Manufacturing is a small manufacturer of custom cases for smartphones, with approximately $3 million in annual revenue. Their customers are the retailers and wholesalers that carry their products. Wall does not sell directly to consumers.




To date, Wall has been manufacturing cases only for iPhones (models 4, 4s, and 5). In the interest of growing their company, senior management has decided to expand into making cases for Samsung’s Galaxy line of phones as well as keeping pace with new models of iPhone.




As the inventory manager, you have been using spreadsheets to keep track of component parts and finished goods inventory. While this has been working adequately, recent sales growth in the iPhone line has started to highlight some of the limitations of the current spreadsheet-based system. With the coming expansion into the Samsung line, you recognized that the spreadsheet-based system will be insufficient to track the expanded numbers of component parts, suppliers, and finished goods. After informing senior management of the need to move from the current spreadsheet system to a real database, you have been directed to develop a pilot database using Microsoft Access. This pilot will help the company better understand its inventory database needs in preparation for research and acquisition of a commercial-grade database product or service. The pilot will consist of two phases, with phase 1 covering the component (parts) inventory. (Phase 2, the finished goods inventory, is not part of this assignment.)




The pilot gives you the opportunity to implement several major improvements to how inventory has been managed. Eliminating redundant data and inconsistencies in data, reducing errors, and improving reporting will all contribute towards a better-managed inventory.




The spreadsheet below identifies the information tracked for each component used in phone cases and provides examples of the data as currently used by Wall. (While additional information is tracked for each component, it is outside the bounds of this project.) Examination of data in the spreadsheet will show various inconsistencies and errors, which is a primary benefit of moving to a well-designed database. Definitions of the information elements can be found below.


NOTE: This is fictional data. You will need to fabricate additional fictional components, vendors, phones, and cases as needed to fulfill the requirements of the project.




Wall Manufacturing – Component Inventory List














Part Name

Part Description


Supplier Part Number

Last Price Paid

Quantity on Hand

Minimum Quantity on Hand

Case Where Used

Fits Phone




Blue Frame

Blue anodized metal frame

Joy Corporation





MagiCase, FlashCase


Always ask for Fred in sales. 875-123-9999. Afternoon is best



Bumper, Red

Red rubber bumper








Iphone 4s




Black Bumper

Painted metal frame - black

Goodyear Tire & Rubber Corp.










Silver frame

Silver frame







iPhone 5

603-243-5464. North State St. Concord NH






















Part Name Wall’s name for the component/part


Part Description Description of the component


Supplier Vendor name – company that Wall purchases this component from


Supplier Part Number Vendor’s part number


Last Price Paid Amount Wall paid for one unit of the component when last ordered


Quantity on Hand Actual number of that component on hand at Wall


Minimum Quantity on Hand Lowest number of that component desired to always have available at Wall


Case Where Used The model of case the component is used in


Fits Phone The phone that the case fits


Notes Miscellaneous important information






Milestone One (Due in Module Three): Define and Create Database Tables


The deliverable for this milestone will be screenshot(s) showcasing your database tables, populated with a small set of sample data.




  1. Based on the information elements in Wall’s inventory spreadsheet, define the data tables that will support the new system.

  2. Construct the tables in MS Access. Table titles should include your last name (e.g., Smith-Vendor)

  3. Enter two data records into each table.

  4. Submit screenshots of your tables, including the populated data, in a Microsoft Word (or equivalent format) document.




Resources: MS Access video tutorials demonstrating the necessary skills for these exercises can be found in the Module Resources section of Modules One and Four.




Milestone Two (Due in Module Five): Define and Create Relationships and Data Entry Forms


The deliverable for this milestone will consist of two sets of screenshot(s). The first set is to show the database tables created in Milestone One with relationships defined between tables. The second set is to show the data entry forms created.




  1. Define the data relationships between tables created in Milestone One within your Access database. Consider the different types of relationships (one-to-one, one-to-many, many-to-many) when constructing the relations.

  2. Define one data entry form per table to allow for addition/change/deletion of all information hosted within your database. Data entry form titles should include your last name (e.g., Smith-Vendor)

  3. Using your data entry forms, enter additional fabricated data for a minimum total of 12 component parts, 5 vendors, 5 cases, and 4 phones (at least one Samsung). This data will be used with queries and reports to be developed in Milestone Three.

  4. Submit screenshots of your tables and relationships in a Microsoft Word (or equivalent format) document

  5. Within the same document, submit screenshots of your data entry forms




Resources: MS Access video tutorials demonstrating the necessary skills for these exercises can be found in the Module Resources section of Modules Four and Six.




Milestone Three (Due in Module Nine): Define and Create Queries and Reports


The deliverable for this milestone will consist of a document containing six screenshots showing the three queries used to produce reports and the three reports.




  1. Define and create the three queries that will:

    1. Identify which components are used in cases that fit iPhone 5

    2. Identify which vendors supply components for iPhone 4 case

    3. Identify all components supplied by each vendor

  2. Define and create three reports that:

    1. List all components used in cases that fit iPhone 5

    2. List the vendors who supply components for iPhone 4 cases

    3. List all vendors and all components they each supply


Include your last name within the title of each report (e.g., Smith-Vendor/Components)


  1. Submit a Microsoft Word (or equivalent format) document containing six screenshots—three queries and three reports. (Reports may be larger than can be displayed within a window. In this case, capture as much of the report as possible, beginning from the upper left corner of the report.)




Resources: MS Access video tutorials demonstrating the necessary skills for these exercises can be found in the Module Resources section of Modules Six and Ten.




Milestone Submission Summary




Module Due


Create an Access database. Define, create, and populate tables



Table Relationships and Data Entry Forms



Queries and Reports








Instructor Feedback: Students can find instructor feedback within Grade Center.


Subject Computer
Due By (Pacific Time) 10/05/2014 12:00 am
Report DMCA

Chat Now!

out of 1971 reviews

Chat Now!

out of 766 reviews

Chat Now!

out of 1164 reviews

Chat Now!

out of 721 reviews

Chat Now!

out of 1600 reviews

Chat Now!

out of 770 reviews

Chat Now!

out of 766 reviews

Chat Now!

out of 680 reviews
All Rights Reserved. Copyright by - Copyright Policy