Task

Part 1

1. Save the Phones.zip file on your H: drive. Unzip. Rename the Phones database as Phones_LastNameFirstName (where LastNameFirstName is your last name and first name). Open the database. In the Database Properties settings update the following fields: Title (file name), Company (company name), and Author (you).
2. Import the data in the Carriers Excel worksheet into a new Access table named Carriers. Let Access add a primary key field. Open the table and verify that the data imported correctly. Change the name of the ID field to CarrierID. Save and close the Carriers table.
3. Open the Inventory table in Design View and add a new field above/before the Carrier field named CarrierID. Set the Data Type to Lookup and Lookup the CarrierID in the Carriers table. Save and close the table. Open the Inventory table and the Carriers table.
   
   Refer to the CarrierID field in the Carriers table and enter the correct CarrierID into each record in the Inventory table. Open the Relationships window and create a One-to-Many relationship between the Carriers table and the Inventory table using the CarrierID field (you may need to modify the existing relationship). Enforce referential integrity. Close the Access Relationships window.
   
   
4. Repeat steps b and c for the fields ManufacturerID and ColourID. To do this, first create an Excel spreadsheet named Manufacturers, which contains each unique manufacturer found in the Inventory table. Create another Excel worksheet named Colours, which contains each unique colour found in the Inventory table. Create the lookup fields in the Inventory table for ColourID and ManufacturerID field, as you did for the CarrierID.
5. Create or modify the relationships between the Inventory table and each of the other tables (Device, Manufacturer, Colour, Carrier). Create One-toMany relationship types. Ensure all relationships can be seen clearly.
6. After all the new tables have been added create the following queries. Make sure the text fields from the supporting tables appear in the queries (not the ID fields). Save each query as noted below.

• Display all the phones that are still for sale (SellDate is Null). Run the Query. Switch to Datasheet View and move the SellDate to the first column. Sort the Query by SellerLastName in Ascending order. Run the Query. Save the Query as LastName_Phones4Sale.
• Display all the phones that are not made by Apple. Save as LastName_NotApplePhones. Run the Query.

Part 2

a) Invent a company name. Create and save a logo for your company (image file).

Add a field to the Inventory table called Photo with data type Attachment. Insert six phone images, one for each of six different phone models within the Inventory table.

b) Create the following Queries. Make sure the text fields from the supporting tables appear in the queries (not the ID fields). Save each query as noted below.

• In Query Design View, add the Manufacturer, TypeOfDevice, Model and PurchaseDate, AskingPrice, SellingPrice, and SellDate fields. Set Criteria to show sold phones; or phones where the asking price is less than $50. Sort by Asking Price. Run the Query. Save as PhonesSoldOR<50>

• Display the phones that were purchased before 4/1/2012. Exclude the sold phones. Sort by purchase date. Run the Query. Save as ObsoletePhones.

c) Import the Excel Buyers worksheet as the Buyers table. Use the BuyerID as the unique Primary key. Import the Excel Sellers worksheet as the Sellers table. Use the SellerID as the unique Primary key. 

d) Before you do this step, you should make a copy of your database file in case you need to start over. Create a new Lookup field in the Inventory table called BuyerID. Place this field before the BuyerFirstName field. This field should look up the BuyerID within the Buyer table. When database users enter the BuyerID in the inventory table they should be able to select from a list that shows the BuyerID with name and address so they can be sure they have the correct buyer and ID. Sort by BuyerLastName and do not Hide the key column. 

e) Before you do this step, you should make a copy of your database file in case you need to start over. Create a new Lookup field in the inventory table called SellerID. Place this field before the SellerFirstName field. This field should look up the SellerID within the Seller table. When database users enter the SellerID in the inventory table they should be able to select from a list that shows the SellerID with name and address so they can be sure they have the correct seller and ID. Sort by SellerLastName and do not Hide the key column. 

f) Create a form for the Buyers table and add new buyer records. Create random names for the new buyer records. Edit the form and add the company logo to the Header. Save the form with an appropriate name. 

g) Open the Inventory table in Datasheet View and add the new records to the table based on the new buyers you entered into the Buyer table through the Buyer Form. When adding the field values use the colours, carriers, and manufacturers provided in the Lookup fields. 

h) Create a Report of mailing labels by choosing Label Wizard and using the Buyers table. Use Avery 8660 as your destination label. Save the Report as Labels Buyers. 

i) Export the Buyers table to a Word Merge as the recipient list and use the Buyers Promotion Word file as the Main Document in a Mail Merge.