CSIS 2010

Question

I have been working at this question on one of my homework assignments for quite some time and I thought I finally got it figured out but Access keeps telling me that I am missing something. Can somebody please tell me what it is?<br><br><br><br>Create a query using SQL based on tblTransactions and tblProducts that calculates total sales volume for each ProductType. Use the INNER JOIN clause to connect the two tables. Use the AS clause to rename the calculated field that totals the Quantity field as HighSalesVolume. Group the results by ProductType. Use the HAVING clause to ensure that only products with a total quarter 1 sales volume of at least 100 are returned. Use the ORDER BY clause to sort the results in descending order by the calculated field.<br><br><br><br>Here is what I have:<br><br><br><br>SELECT Quantity AS HighSalesVolume, ProductType <br><br>FROM tblTransactions INNER JOIN <br><br>tblProducts ON <br><br>tblTransactions.ProductID= <br><br>tblProducts.ProductID <br><br>GROUP BY ProductType <br><br>HAVING ([TransactionDate])>=4/30/2022 <br><br>ORDER BY HighSalesVolume DESC;

Answer 1

There are great resources on line like https://www.sqltutorial.org/. Keep in mind the syntax will vary based on the database. SQL Server has some differences from MS Access. Your requirements are all basic syntax and should be identical in all database types.

Answer 2

That's your HAVING clause. Refer to the study materials accompanying your course for details on that.

Answer 3

How do I implement this:

only products with a total quarter 1 sales volume of at least 100

into the query? What type of clause does that?

Answer 4

I don't think that query would give you the results requested in the assignment.

Among other things:

"totals the Quantity field as HighSalesVolume"

"only products with a total quarter 1 sales volume of at least 100'

Neither of those appears to be implemented in the SQL shown.

Also, Access requires delimiters for certain datatype. To use a text string, for example, it must be surrounded by quotes. "String"

To use a date, it must be surrounded by the octothorpe, or hash mark. #8/3/2022#

We can't give you direct answers, of course, because that would cheat you of the chance to figure it out and learn for yourself. Those clues ought to give you an idea where to look, though.

Answer 5

My first suggestion is "totals the Quantity field" which suggests using Sum.

"total quarter 1 sales volume" suggests filtering to January - March.

Try implement these and report back.