“ComputerBrandsInfo”

1. (24 points) Create a new table called “ComputerBrandsInfo” in the database in the starter
   Access file
   a. Create a new table in design view in the database in Access (4 pts)
   b. Create a new field named as “ComputerBrand” in the design view of the table. And
   also, please assign the data type “Short Text” to the “ComputerBrand” field (8 pts)
   c. Assign the primary key to the “ComputerBrand” field of the table (4 pts)
   d. Save the table as “ComputerBrandsInfo” (4 pts)
   e. Switch to datasheet view of the table. Add the below information to the
   “ComputerBrand” field of the “ComputerBrandsInfo” table (4 pts)
   Apple
   HP
   Lenovo
   Razer
2. (16 points) Import data from the “ComputerSalesInfo.xlsx” file
   a. Import data from the “ComputerSalesInfo.xlsx” file to the “ComputerSalesInfo” table
   (8 pts)
   b. Rename the primary key of the “ComputerSalesInfo” table from “ID” to “SaleID” (4
   pts)
   c. Change the data type of the “SalePrice” field of the “ComputerSalesInfo” table from
   “Number” to “Currency” (4 pts)
3. (8 points) Modify the “Computers” table so that the “ComputerProcessor” field in the
   “Computers” table uses a combo box lookup field with the following four values:
   Intel Core i5, Intel Core i7, Intel Core i9, AMD Ryzen 7
4. (8 points) Create a relationship between the “Computers” table and the
   “ComputerSalesInfo” table
   (Hint: the “ComputerID” field is the primary key of the “Computers” table. The
   “ComputerID” field is also the foreign key of the “ComputerSalesInfo” table;
   Also please make sure the “Computers” table and the “ComputerSalesInfo” table are
   closed before you create a relationship for the two tables)
5. (16 points) Create a query named “5 - Computer Sale Information” that can show the
   following information for computer sales:
   a. ComputerYear
   b. ComputerBrand
   c. ComputerProcessor
   d. ComputerColor
   3
   e. ComputerCondition
   f. ComputerCost
   g. SalePrice
   h. SaleDate
   (Hint: the fields above come from the “Computers” table and the “ComputerSalesInfo”
   table)
6. (10 points) Create a summary query named “6 - Summary of Cost by Brand” to
   summarize information about costs of computers by their brand. Specifically, this
   summary query can group information about computer cost by:
   a. ComputerBrand
   and also show the following summarized information about computer cost:
   b. Average ComputerCost
   c. Maximum ComputerCost
   (Hint: the fields, “ComputerBrand” and “ComputerCost” come from the “Computers”
   table; use the Max aggregate function for Maximum ComputerCost)
7. (14 points) Create a new query named “7 - Discount” with the following information:
   a. ComputerYear
   b. ComputerBrand
   c. ComputerProcessor
   d. ComputerCondition
   e. SalePrice
   f. Create a calculated field called Discount which is equivalent to 15 percent of SalePrice
   (Hint: the fields, “ComputerYear”, “ComputerBrand”, “ComputerProcessor” and
   “ComputerCondition” come from the “Computers” table; the “SalePrice” field comes
   from the “ComputerSalesInfo” table)
8. (4 points) Save and Submit your work
   a. Save your answers in the Access file, and name it as
   “nnn00000_access_assignment.accdb” where nnn00000 is your NetID (AKA the account
   you log into HuskyCT with) (2 pts)
   b. Go back to the Access Assignment section in HuskyCT course website. You should
   see the file uploading part similar to the screenshot below
   4
   Please submit your completed file to HuskyCT to finish your assignment (2 pts)
   Note: It’s recommended that you close the database file after you save the file in Access.
   When the database file is still open, another associated middle file with icon and .laccdb
   file name extension can be shown in your folder with the Access file with icon and .accdb
   file name extension. Please don’t submit the .laccdb file! Your submission Access file should
   be the one with the .accdb file name extension.

Sample Solution