Question

Create a query in microsoft access  based on tblTransactions and tblProducts that calculates total sales volume for each ProductType during the first quarter of 2018. The query should calculate the total quantity of items sold by applying the Sum aggregate function to the Quantity field. Group the results by ProductType.

tblTransactions 8/6/2019 ProductID TransID CustID RepID TransactionDate Quantity 12 Nguyen 13 Nguyen 14 1/1/2018 Jenkins Johnson 1/1/2018 17 3 20 Branson Jenkins 1/6/2018 7 9 1/6/2018 21 Peters Corez 12 Johnson 1/7/2018 22 Mitchell 2 4 23 Preston 1/7/2018 Moriarity 9 3 30 Richardson 1/9/2018 Corez 15 5 Jenkins 1/11/2018 31 Bryant 1 4 33 Mitchell Jenkins 1/13/2018 15 4 Jenkins 1/22/2018 41 Bryant 8 1 42 Preston 1/22/2018 Jenkins 19 2 Johnson 1/22/2018 43 Bryant 4 2 1/28/2018 50 Nguyen Corez 1 5 1/30/2018 51 Preston Moriarity 7 2 Moriarity 1/31/2018 52 Peters 20 1 2/6/2018 61 Nguyen Corez 1 5 Jenkins 62 Peters 14 2/7/2018 3 Johnson 2/8/2018 63 Thomas 6 1 15 2/12/2018 71 Bryant 72 Richardson Jenkins 1 2/13/2018 Moriarity 13 4 2/14/2018 73 Peters Jenkins 1 5 2/25/2018 80 Bryant Corez 2 5 Johnson 15 2/26/2018 81 Preston 4 3/3/2018 3/7/2018 83 Preston Corez 8 3 Jenkins 90 Peters 13 Jenkins 3/9/2018 91 Peters 16 5 92 Branson Corez 19 3/12/2018 2 93 Nguyen 3/13/2018 Corez 7 2 3/20/2018 100 Bryant Johnson 14 3 Tranh 8 3/24/2018 103 Branson 4/1/2018 110 Peters Tranh 9 3 4/1/2018 111 Peters Jenkins 15 5 112 Richardson Jenkins 4/1/2018 15 2 Moriarity 113 Bryant 4/2/2018 4/10/2018 15 3 120 Richardson Jenkins 1 122 Richardson 4/11/2018 Corez 10 5 4/12/2018 123 Peters Johnson 17 130 Preston 4/20/2018 Moriarity 12 132 Nguyen 4/21/2018 Jenkins 20 142 Mitchell Moriarity 5/14/2018 6 4 5/16/2018 143 Bryant Johnson 7 4 15 5/19/2018 150 Preston Corez 1 Page 1

Answer 1

Please find the SQL query below.

QUERY

SELECT p.ProductType, sum(t.Quantity) FROM tblTransactions t JOIN tblProducts p ON t.ProductID = p.ProductID WHERE t.TransactionDate BETWEEN '1/1/2018' AND '3/31/2018' 'GROUP BY p.ProductType;