MICROSOFT ACCESS Use Access to create a table for supplier contact information, a table for inventory information, and a form for entering the data.
1. Create a new Access database.
2. Save the file as SupplierInventory_MEID.accdb.
STRUCTURE OF THE DATABASE
Read the requirements to the database below, design it, and understand how it will work. Remember, Access is a relational database; make sure to reduce data redundancy during the design process. Once you have created tables, you will need to join them and establish referential integrity. For this project, you can assume that each item can be purchased from different suppliers. Be sure to think about what fields should be in the tables in order to properly store the data as some data elements may require more than one field.
CREATING THE DATA
1. Create a new table for Suppliers' contact information and set the primary key and appropriate data types for the fields. The table should at least contain the following information: a. Company name and address b. Representative's name, phone number, and email address
2. Create a new table for Inventory information and set the primary key and appropriate data types for the fields. The table should contain the information about the products including, but not limited to: a. Material/Fabric name, description, and price b. Quantity
CREATING A FORM
1. Create a form that can be used to enter information in the database.
2. Use the form to enter a minimum of 5 records. Use your name in one of the records as a representative for one of the companies.
CREATING A QUERY
1. Create a query that will select suppliers who have a specific fabric type. You may choose the fabric type for the query.
CREATING A REPORT
1. Create mailing labels to be used for the letters to the company representatives.
MICROSOFT EXCEL Use Excel to create a template to keep track of the product lines material cost and sales. Be sure to think about what fields should be in the tables in order to properly separate the data as some data elements may require more than one column.
1. Create a new Excel workbook.
2. Save the file as CreateAndSell_MEID.xlsx.
P RODUCT LINES WORKSHEET
1. Name the first worksheet Products.
2. Assign the tab a color of your choice.
3. At the top of the worksheet add the heading: Wendy's Wears.
4. Below the heading, create Row Labels for the following: a. Company Tagline (i.e. advertising slogan) b. Established c. Location
5. Format the Row Labels and cells so they appear like the image to the right.
6. Fill-in the values for step 4 above. You can make this up, but remember it as you may use it in other areas as well.
7. Below the Row Labels titles, create a table with headers that lists the product line information. The information should include as a minimum: a. Product Line b. Product Names and IDs c. Target Market (e.g. men, women, teen, children, etc.)
8. Add at least 2 product lines and 3 products names per product line to the table. You can make up the names and information.
9. Format the worksheet for printing on one page.
10. Add your name, MEID, and course and section to the footer of the page.
C REATION WORKSHEET
1. Create a new worksheet and name it Creation.
2. Assign the tab a color of your choice.
3. At the top of the worksheet add the heading: Clothing Creation.
4. Below the heading, create a table with headers that contains a list of product names with pertinent information, including, but not limited to:
a. Reference the product name and ID.
b. Allow the tracking of the following information for each product, but not limited to the following. i. Fabric Type and amount used
1. Make the Fabric Type column a drop down list listing at least 4 types of fabric ii. Cutting, Sewing, and Cleaning Times c. Automatically calculate with the use of formulas the following: i. Product cost ii. Total Creation Time (based on 4.b.ii) iii. Production (how many items can be produced in a full-time worker's work day - 8hrs/day) d. Automatically calculate totals for the items in 4c above. 5. Add at least 8 items to the table. You can make up the information. 6. Format the worksheet for printing on one page. 7. Add your name, MEID, and course and section to the footer of the page. Tagline Established Location
SALES WORKSHEET 1. Create a new worksheet and name it Sales. 2. Assign the tab a color of your choice. 3. At the top of the worksheet add the heading: Clothing Sales 4. Below the heading, create a table with headers to accomplish the following: a. Reference the product name and ID. b. Allow the tracking of the following information for each product, but not limited to the following. i. Sale price ii. Shipping cost c. Automatically calculate with the use of formulas the following. i. Product Income ii. Projected Monthly Income d. Automatically calculate totals for the items in 4c above. e. Apply conditional formatting to indicate with a change of cell and font color any product that has less than $20 product income. 5. Fill the table in with the information, which you can make up. Be sure at least one product has less than $20 product income so the conditional formatting is applied to it. 6. Below the table, insert a chart that displays each product's sales price, product income, and projected monthly income. You will need to select the appropriate type of chart to help compare each of these items.