Question

8. Use the Access_Practice.accdb database to complete the following tasks in Access. O LO 4-2 a. The database contains three tables containing information about this company's sales process: Inventory, Sales, and Salesitems. Use the Relationships window to link the tables together. b. The Salesltems table records the quantity and price of each item sold on each sale (sales may include more than one item). Calculate the extended amount of sale (call it Amt) for each item (Quantity * UnitPrice). Include InvoiceID, InventoryID, Quantity, and UnitPrice in the query. Name the query Item_Extension_Calculation. c. Calculate the total dollar amount of each sale. Include InvoiceID, InvoiceDate, CustomerID, and Employeeld from the Sales table and the Amt from the Item_Extension_Calculation query. Name the query Sale_Amount_Calculation. d. Calculate total sales for each inventory item. e. Calculate total sales. f. Calculate the month in which each sale occurred. Include InvoicelD and Invoice Date from the Sales table. Name the query Sales_Months. (Hint: Look for the Month function in the expression builder.) g. Calculate the sum of sales for each month.

Answer 1

a.

1. Link the tables

Open the access database (called Access_Practice.accdb), select “Enable Content” in the yellow SECURITY WARNING to work on the assignment. If you do not see the warning, proceed to step 2.

SECURITY WARNING Some active content has been disabled. Click for more details. Enable Content

2. Select the “DATABASE TOOLS” tab. Select “Relationships” in the “Relationships” box to open the Show Table window. Holding down shift, select “Inventory,” “Sales,” and “SalesItems” from the Tables list. Select Add then Close.

EXTERNAL DATA DATABASE TOOLS Databas GAnalyze Relationships Object Dependencies Analyze Relationships A

Show Table ? х Tables Queries Both Customers Employees Inventory Sales SalesItems Add Close

The following screen will show up:

AD RELATIONSHIP TOOLS Access_Practice_Solution : Database- C:\Users\Briann... ох FILE HOME CREATE EXTERNAL DATA DATABASE TOOLS DESIGN Briann Belliveau - A Close Hide Table X Clear Layout Direct Relationships Edit Show 5 Relationship Report 89 All Relationships Relationships Table Tools Relationships Tables Relationships X Customers Inventory Sales Salesitems Employees 7 InventoryID 7 InvoiceID InvoiceID 7 InventoryID Name FET Inventory Quantity InvoiceDate Order Date CustomerID Employeeld CustomerPO UnitPrice Beverage Type Caffeinated Flavored Price Sales Salesitems Ready NUM LOCK

3. Link the tables by dragging the primary key to its foreign key in the appropriate table. For each link place a check in the Enforce Referential Integrity box. Select Create.

Edit Relationships ? Х Table/Query: Inventory Related Table/Query: SalesItems Create Cancel InventoryID InventoryID Join Type.. Create New.. Enforce Referential Integrity Cascade Update Related Fields Cascade Delete Related Records Relationship Type: One-To-Many

Relationships Inventory 7 InventoryID Name BeverageType Caffeinated Flavored Price Sales * InvoiceID InvoiceDate OrderDate CustomerID Employeeld CustomerPO Salesitems 1 InvoiceID 7 InventoryID Quantity UnitPrice

4. Save your work then close the relationship window.

b.

Create the Item_Extension_Calculation query:

1. Select the CREATE tab. Select Query Design in the Queries box.

CREATE EXTERNAL DATA DA Table SharePoint Query Query Design Lists Wizard Design Tables Queries

2. Select SalesItems in the Show Tables box. Select Add, then Close:

Show Table ? х Tables Queries Both Customers Employees Inventory Sales SalesItems Add Close

3. Drag InvoiceID, InventoryID, Quantity, and UnitPrice to the Field row of the design grid creating four columns.

Query1 X Salesitems 7 InvoiceID InventoryID Quantity UnitPrice InventoryID Salesitems Quantity Salesitems UnitPrice Salesitems Field: InvoiceID Table: Salesitems Sort: Show: Criteria: or:

4. In the blank column on the right of the design grid, type Amt: then select Builder in the Query Setup box of the DESIGN tab:

e Insert Rows " "Insert Columns Peter Rows * Delete Columns Show Table Builder Return: All Query Setup

5. Expand Access_Practice.accdb by selecting its plus sign (+). Select Tables. (Either select the plus sign or double-click Tables). Select SalesItems from the Expression Elements list, then double-click Quantity from the Expression Categories list. Type, * in the calculated query field. Double-click UnitPrice in the Expression Categories list. In the calculated query field delete <>. Select OK.

Х Expression Builder Enter an Expression to define the calculated query field: (Examples of expressions include [fields] + [field2] and [field1] <5) Amt: [SalesItems)!(Quantity] * [SalesItems]! [UnitPrice] OK Cancel Help << Less Expression Values <Value> Expression Categories InvoiceID InventoryID Quantity UnitPrice Expression Elements Functions A Access_Practice_Solutic Tables Customers Employees Inventory Sales SalesItems A Queries

6. Verify the Show: box in the Amt: column is selected. Then, select Property Sheet in the Show/Hide box of the DESIGN tab. The Property Sheet appears on the right side of the screen.

7. Choose currency for the Format, close (x) the Property Sheet:

8. Select in the Results box of the DESIGN tab. The extended amount of sales will appear.

9. Select Save Save the query as Item_Extension_Calculation. Select OK.

Save As ? X Query Name: Item_Extension_Calculation OK Cancel

c.

Calculate the total dollar amount of each sale.

1. Select the CREATE tab. Select Query Design in the Queries box.

a. Select the Queries tab. Choose Item_Extension_Calculation, then Add. Select the Tables tab. Choose Item_Extension_Calculation, then Add.

Show Table ? X Tables Queries Both Item_Extension_Calculation Add Close

2. Double-click InvoiceID, InvoiceDate, CustomerID, EmployeeID in the Sales table and Amt in the Item_Extension_Calculation query.

3. Select
Σ Totals
in the Show/Hide box of the DESIGN tab. Select Sum for Amt and leave the others as Group by. Format Amt as Currency using the Property Sheet as previously discussed.

4. Select

you will get the total dollar amount of each sale. Save the query as Sale_Amount_Calculation.

d.

Calculate total sales for each inventory item.

1. Create new query and select SalesItems and Inventory under the Tables tab.

2. Double-click Inventory ID and Name in the Inventory table to add them to the design grid. In the blank column on the right, type Amount and select Builder in the Query Setup section of Design tab.

3. In the Expression Builder under Expression Elements select the SalesItems table and double-click Quantity in the Expression Category list. Type *. Double-click UnitPrice under Expression Category. Delete <>, then click OK.

4. Click and select Sum for Amount in the Total row.

5. Format Amount as Currency.

6. Select you will get total sales for each inventory item.

AH Access_Practice Solution: Database - C:\Users\Briann\Documents\Access_Practice Solution.accdb (Access 2007 - 2013 file format) - Access FILE HOME CREATE EXTERNAL DATA DATABASE TOOLS Briann Belliveau Calibri Save abc Replace → Go To Select- BIU A aby All X Delete Find Text Formatting Cut Al Ascending Y Selection New Σ Totals E Copy A Descending Advanced Spelling View Paste Filter Refresh Find Format Painter 4 Remove Sort Y Toggle Filter More Views Clipboard Sort & Filter Records Tables Item_Extension_Calculation Sale_Amount_Calculation Sales_for_Inventory_Items InventoryID - Name Amount Customers 1101 Darjeeling Badamtam $1,441.80 Employees 1102 French Vanilla $922.20 1103 Seville Orange $1,185.80 Inventory 1104 Vanilla Nut Fudge $980.50 Sales 1105 Vanilla Almond $1,444.00 1106 New Guinea $1,324.60 Salesitems 1107 Assam Fancy 2nd Flush $1,909.60 1108 China Yunnan $1,957.00 1109 Raspberry $784.00 1110 English Breakfast $1,863.20 1111 Mocha Java $691.20 1112 Southern Pecan $1,650.00 1113 Chamomile Blossom $1,698.40 1114 Costa Rica La Manita $1,852.50 1115 Vanilla $2,650.50 1116 Chocolate Raspberry $848.00 1117 Gunpowder $581.00 1118 Tanzania Moshi $774.00 1119 Lapsang Souchong $673.40 1120 Almond Amaretto $1,162.80 1121 English Breakfast $828.00 1122 Russian $2,197.80 1123 Rumba $968.00 1124 Passionfruit with Flowers $1,201.20 1125 Chocolate Macadamia $657.00 1126 Jasmine $1,368.00 1127 Chocolate Raspberry $697.50 1128 Ethiopia Yergacheffe $1,023.50 1129 Guangxi Guihua $1,092.00 1130 Orange Spice $605.20 1131 Chocolate Cinnamon $717.60 Record: 1 of 154 1x No Filter Search Ready NUM LOCK E SQL

Save the query as Sales_for_Inventory_Items.

e.

Calculate Total Sales.

1. Select the CREATE tab. Select Query Design in the Queries box. Select SalesItems.

CREATE EXTERNAL DATA DA Table SharePoint Query Query Design Lists Wizard Design Tables Queries

2. In the first column, select Builder in the Query Setup section of Design tab. Type in the formula written in the calculated query field in the Expression Builder image below. Select OK.

3. Select

to see the query result.

4. Save the Query as Total_Sales.

f.

Calculate the month in which each sale occurred

1. Select the CREATE tab. Select Query Design in the Queries box Select Sales.

2. Double-click Invoice ID and Invoice Date to insert into the first two columns. In the third column select Builder and type the formula written in the Expression Builder:

3.

Expression Builder Enter an Depression to define the code (Bamples of expressions include felt) and < Marche MoreSeeInvite] OK Cancel < Expression Categories Expression Elements Acceso Table Customers Inventory Icebe Order Employee Customer eshme

4. Select

and see the result:

10 10 10 10 10 10 10 10 10 Invoicel · InvoiceDate Month 214010 10/1/2017 214011 10/1/2017 214012 10/1/2017 214013 10/1/2017 214014 10/1/2017 214015 10/2/2017 214016 10/2/2017 214017 10/3/2017 214018 10/3/2017 214019 10/4/2017 214020 10/4/2017 214021 10/4/2017 214022 10/5/2017 214023 10/5/2017 214024 10/5/2017 214025 10/5/2017 214026 10/6/2017 214027 10/6/2017 214028 10/6/2017 214029 10/7/2017 214030 10/7/2017 214031 10/7/2017 214032 10/10/2017 214033 10/10/2017 214034 10/10/2017 214035 10/11/2017 Record: "1 of 500 No Filter 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 Search

5. Save the query as Sales_Month.

g.

Calculate the sum of sales for each month.

Select the CREATE tab. Select Query Design in the Queries box. Select the SalesItems table and Sales_Month query.

2. Link the table and query by dragging InvoiceID from the SalesItems table to InvoiceID in the Sales_Month query.

3. Double-click Month from the Sales_Month query. In the second column, select Builder and type the following formula:

4. Select
Σ Totals
select Group By for Month and Sum for Sales_Month in the Total row.

5. Format Sales_Month as Currency using the Property Sheet.

6. Click
! Run
and save the query as Sales_by_Month.