Applied Business Software

Integrating Microsoft Office Access Project

46 Marks (10%)

Used Cell Phones for Sale:

You started a new business (create a company name by yourself) selling used cell phones,

MP3/MP4 players, and accessories. You have been using an Access database to track your

inventory. You decide to improve the data entry process by adding a few additional tables.

After the new tables are added and the relationships are set, you will create several queries to

analyze the data. Students will submit their individual files for grading.

TASKS:

Marks INDIVIDUAL TASKS

4 a) Download the Phones.accdb file. Rename the Phones database file 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), Author (your name), and Manager (your name).

4 b) Import the data from the Carriers Excel file (Carriers worksheet) into a new Access table named Carriers. Make sure check the box “First Row Contain Column

Headings”. Let Access add a primary key field. Accept all default settings. 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.

6 c) 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.

10+10=20 d) Repeat steps b and c for the fields ManufacturerID and ColourID. To do this, in the Carrier Excel file, first change the name of worksheet Sheet2 to Manufacturers,

which contains each unique manufacturer found in the Inventory table (Copy the

whole column of Manufacturer field and paste it in the renamed Manufacturers

worksheet, remember remove the duplicate value).

Change another worksheet Sheet3 to Colours, which contains each unique colour

found in the Inventory table (Copy the whole column of Colour field and paste it in

the renamed Colours worksheet, remember remove the duplicate value). Save and close the Carriers Excel file (you need to submit this excel file later).

Create the lookup fields in the Inventory table for ColourID and ManufacturerID

field, as you did for the CarrierID in step c.

Open the Inventory table and the Manufacturers table. Refer to the ManufacturerID

field in the Manufacturer table and enter the correct ManufacturerID into each record

in the Inventory table.

Open the Inventory table and the Colours table. Refer to the ColourID field in the

Colours table and enter the correct ColourID into each record in the Inventory table.

6 e) Create or modify the relationships between the Inventory table and each of the other tables (Device, Manufacturer, Colour, Carrier). Create One-to-Many relationship

types. Enforce referential integrity. Ensure all relationships can be seen clearly.

6 f) Use Inventory table to create following queries. Make sure the text fields from the supporting tables appear in the queries (not include all the ID fields). Save each

query as noted below.

 Display all the phones that are still for sale (set the criteria in SellDate field - is Null). Run the Query. Switch to Design 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. Then close it.

 Copy LastName_Phones4Sale. Paste and rename as LastName_NotMicrosoftPhones. Remove all the existing sorting & criteria.

Display all the phones that are not made by Microsoft (Set the criteria in

Manufacturer field – not “Microsoft”). In Design View and Sort by

Manufacturer in Ascending. Run the Query. Save and close the Query.

Subtotal

46

END of Individual Project

Submit the following files in eCentennial corresponding Dropbox:

 Phones_LastNameFirstName Access file

 Carriers Excel file.