Posted: December 10th, 2022

Integrating Microsoft Office Access Project

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
Question Assignment as noted below.
 Display all the phones that are still for sale (set the criteria in SellDate field –
is Null). Run the Question Assignment. Switch to Design View and move the SellDate to the
first column. Sort the Question Assignment by SellerLastName in Ascending order. Run the
Question Assignment. Save the Question Assignment 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 Question Assignment. Save and close the Question Assignment.
Subtotal
46
END of Individual Project
Submit the following files in eCentennial corresponding Dropbox:
 Phones_LastNameFirstName Access file
 Carriers Excel file.

Order | Check Discount