Share via

CSIS 2010

Anonymous
2022-08-03T17:01:44+00:00
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;
Microsoft 365 and Office | Access | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

5 answers

Sort by: Most helpful
  1. Duane Hookom 26,825 Reputation points Volunteer Moderator
    2022-08-03T23:11:49+00:00

    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.

    Was this answer helpful?

    0 comments No comments
  2. George Hepworth 22,855 Reputation points Volunteer Moderator
    2022-08-03T21:47:34+00:00

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

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2022-08-03T21:29:55+00:00

    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?

    Was this answer helpful?

    0 comments No comments
  4. George Hepworth 22,855 Reputation points Volunteer Moderator
    2022-08-03T19:02:35+00:00

    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.

    Was this answer helpful?

    0 comments No comments
  5. Duane Hookom 26,825 Reputation points Volunteer Moderator
    2022-08-03T17:18:10+00:00

    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.

    Was this answer helpful?

    0 comments No comments