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

kara
Télécharger la présentation

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

  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. Saved query

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

  14. Query 2 Dynaset

  15. 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

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

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

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

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

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

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

  22. Query 2 Dynaset

  23. 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

  24. Query 4 Dynaset

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

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

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

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

  29. SQL code generated by Access for this query SELECT Sales.Date, Sales.[Sales Invoice #], [unit price]*[quantity] AS [Line item extension] FROM Sales INNER JOIN (Inventory INNER JOIN [Sales-Inventory] ON Inventory.[Item #] = [Sales-Inventory].[Item #]) ON Sales.[Sales Invoice #] = [Sales-Inventory].[Sales Invoice #] GROUP BY Sales.Date, Sales.[Sales Invoice #], [unit price]*[quantity] HAVING (((Sales.Date) Between #10/1/2005# And #10/31/2005#));

  30. If you want line item extension calculated field to be in currency format, right click in the field column and select properties

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

  32. Second part Line item extension query is input for final query to get invoice total

  33. Query 4 Dynaset SQL code generated by Access for this query SELECT [qryInvoiceTotalsAlt a].Date, [qryInvoiceTotalsAlt a].[Sales Invoice #], Sum([qryInvoiceTotalsAlt a].[Line Item Extension]) AS [Invoice Total] FROM [qryInvoiceTotalsAlt a] GROUP BY [qryInvoiceTotalsAlt a].Date, [qryInvoiceTotalsAlt a].[Sales Invoice #];

More Related