1 / 33

Chapter 4

Chapter 4. Query Examples for S&S. Query 1. Sales events to D.Ainge and Sales Rep associated with event Key to querying a database Identify the tables/queries that have the data that you need Include these tables/queries in your query design

esma
Download Presentation

Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 Query Examples for S&S

  2. Query 1 • Sales events to D.Ainge and Sales Rep associated with event • Key to querying a database • Identify the tables/queries that have the data that you need • Include these tables/queries in your query design • If needed, establish the necessary joins between the data sources • Do not include extra tables

  3. Table 4-5 • Data for this query contained in 2 tables • Sales table • Customer table

  4. Relationship map

  5. Relationship map using Visio

  6. Relationship Map Select create tab Then select Query Design

  7. Highlight needed tables and select add

  8. Double click or highlight and drag each attribute you need to add to the table below

  9. Enter your selection criteria • Text criteria needs to be in quotes When finished run the query

  10. Resulting Dynaset SQL code generated by Access for this query SELECT Sales.[Sales Invoice #], Sales.Salesperson, Customer.[Customer Name] FROM Customer INNER JOIN Sales ON Customer.[Customer #]=Sales.[Customer #] WHERE (((Customer.[Customer Name])="d. ainge"));

  11. Close out queryYou will be prompted to saveIf you want to save it, select yes and give the query a name

  12. Query 2 (modified) • Query will calculated unit sales by item for the month of October 2018 • Query will require use of the summation aggregation function • Query will use the group by feature • Query will use date selection criteria

  13. Query 2 Dynaset

  14. Aggregation Functions in Queries • An aggregation function summarizes the data values within a field (column) • COUNT summarizes the number of rows that contain a given value in the field • AVERAGE computes the arithmetic mean value of all rows included in the answer • SUM computes the arithmetic sum of all rows included in the answer • MIN identifies the minimum (lowest) attribute value for the field • MAX identifies the maximum (greatest) attribute value for the field

  15. Table 4-5 • Data for this query contained in 3 tables • Sales table (date) • Sales - Inventory table (quantity) • Inventory table (description)

  16. Date criteria Between #10/1/2018# And #10/31/2018# Note: do not check show date for this query

  17. Select total symbol • The total line will appear • Select option from drop down menu

  18. You may want to give “SumOfQuantity a more meaningful name

  19. Place label in front of attribute Unit Sales: Quantity or

  20. Or right click on quantity field to bring up property sheet Enter caption

  21. Query 2 Dynaset

  22. Query 4 (modified) • Query will calculate total for each October sales invoice • Book example uses 1 query • Modified Query will be done in two parts • Part A will do the necessary horizontal calculations for line item extension • Horizontal” calculations mathematically combine values from different fields for each row • Creates a calculated field in the query • Final part will use part A query to calculate the totals using summation aggregation function

  23. Query 4 Dynaset

  24. Table 4-5 • Data for this query contained in 3 tables • Sales table (date & invoice #) • Sales - Inventory table (quantity) • Inventory table (Unit Price)

  25. Part A Line item extension: [unit price]*[quantity] Syntax for Formula important, must use [ ] Typos in attribute name will result in error message

  26. Syntax error example: Line item extension: (unit price]*[quantity]

  27. Typo in attribute name example:Line item extension: [unit price]*[quantty]

  28. Part A dynaset

  29. Part B Line item extension query is input for final query to get invoice total

  30. Query 4 Dynaset

  31. If you want Invoice Total field to be in currency format, right click in the field column and select properties

  32. In property sheet box, select the format you want for the calculated field

More Related