Vendors! 

As part of the operation of the Warrior's Warehouse (the MHS school store), it is necessary to find suppliers for the merchandise that is available for sale in the store.  As you are aware, the Warrior's Warehouse carries a variety of items including apparel, school supplies, snacks, and spirit items.  You have been asked to create a database for the Warrior's Warehouse which will contain two tables.  A "relationship" will then be set between the two tables and queries will be run.  Go ahead and get started!

Create a new Access database saved as [last name] - WW Vendors (ie - Jansen - WW Vendors).  (Remember to save this in your Access folder.)  Within the database, create the a table called Vendors.  Within that table, create the following fields: 

• Vendor Number (autonumber) - this will be the primary key
• Company Name (text)
• Address (text)
• City (text)
• State (text)
• ZIP (number)
• Phone Number (text)
• Fax Number (text)
• Website Address (Hyperlink)

Save the table as Vendors and then create a form saved as Vendors that can be used to enter vendor information.  Use the linked images below as sources of information for 8 new vendors that the Warrior's Warehouse might be able to use to purchase inventory for the school store and enter the company information using the form created.  Try to get as much information as you can for each of the vendors (you will probably need to look for links that say "Contact Us" or "About Us".  Save.

Within this database, create another table saved as Purchases.  Include the following fields for information with the data types that follow:

• Vendor Number (Number) **Be sure that this field is NOT the primary key in this table. No field in this table should be a primary key.**
• Product Number (Number)
• Product Description (Text)
• Product Picture (OLE Object)
• Approved? (yes/no)
• Quantity (Number)
• Cost per Item (Currency)

Open your Purchases table in the "Datasheet View."  From here, you will add a calculated field for the total cost of the purchase (Quantity * Cost per Item = Total Cost).  To do so, on the far right side of the table, click the down-arrow next to the field that states "Click to Add and select Calculated Field > Currency.  Enter an appropriate formula and click OK.  Rename the field Total Cost.  Then, test the formula by entering trial information in the Quantity and Cost per Item fields to see if the new Total Cost field formula is working properly.  The correct answer should show up in the Total Cost field.  Save.

Using all of the fields from the Purchases table, create an Order Form saved as Purchases Order Form arranged in a logical format that can be used for items to be ordered for the Warrior's Warehouse.  Then, use the form to create orders for 8 different items to be purchased for the store with the necessary information.  Note that you should purchase at least 5 of the vendors that were included in the first step of this project.  Please note the following fields as you enter information:

• Vendor Number - use the numbers from the vendors in the Vendor table
• Description - include a complete description of the item and note any additional set-up costs that might be incurred. 
• Quantity - make up a number that you think we should initially order
• Total Cost - note that this field should automatically calculate! 
• Product Picture - copy and paste an image of the item(s).  

Note:  The "paste" feature will not be available unless your previous step is "copy."  No additional steps can be done between the copy and paste!

• Approved - approve 5 of the 8 items you indicated for purchase.  Anything with a check mark in this yes/no data field would be considered to be "approved." 

To finalize this Warrior's Warehouse database, you will create a relationship between the two tables and run a query.  Complete these tasks as you follow these instructions:

1. Create a new query and add both tables. Create a relationship between both Vendor ID fields by clicking and draggin one to the other. This way, Access can recognize that both things mean the same. 
2. Query - Create the following queries using the following criteria.  Copy and paste the results of each query to a Word document called [last name] Vendors Database Assignment.  Be sure each query is clearly labeled, identifying the query criteria.
   • Cost per Item is less than or equal to $50
   • Approval is equal to "yes"
   • 2 Additional Queries - your choice, based on your data!
3. Advanced Query -  Create a query with the following fields:  Vendor Number, Product Number, Product Description,and Approved.  Add a calculated field named Total Cost for the total cost of the purchase (Quantity * Cost per Item).  This will require you to build an expression.
4. How to build an expression:
   • Right click on the field name box and select the Build option. This opens the Expression Builder. Build your expression from your Database menu (you'll need to expand it to find your tables and fields and you'll need to use the * to multiply).
5. Before you are ready to run the query add the following criteria:
   • Approval is Yes
   • Total Cost is less than or equal to $250 (you need to tack this on at the end of your Total Cost expression). 
6. Run the query to see if your expression and criteria work correctly. 
7. Save.

Submit your Vendor DB by following the directions below:

1. Add a title at the top of the Word document you have your queries pasted to: [Your Name] – Vendor Database.
2. Take a “Screen Shot” of the Vendors Table showing vendor information. Paste this in the Word document under the queries and label it as your Vendor Table.
3. Take a “Screen Shot” of the Purchases Table showing the purchase information. Paste this in the Word document and label it as your Purchases Table.
4. Get your teacher to check your screen for your Total Cost expression.
5. Take a “Screen Shot” of the Order Form you created. Paste this into Word and label it as “Purchases Order Form”
6. Crop and resize screen shots as necessary so that information is readable.
7. Save and upload this Word document which documents  your Access work to Edmodo.