Computer Science & Engineering 2111
Download
1 / 12

Computer Science & Engineering 2111 - PowerPoint PPT Presentation


  • 49 Views
  • Uploaded on

Computer Science & Engineering 2111. Lecture 12 Sorting, Summarizing and Calculating in Queries. Sorting Results in a Query. Use the Sorting line - Ascending or Descending to sort the resulting Query Table To sort multiple fields

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Computer Science & Engineering 2111' - phuoc


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Computer Science & Engineering 2111

Lecture 12

Sorting, Summarizing and Calculating in Queries

CSE 2111 Sorting, Summarizing and Calculating in Queries


Sorting Results in a Query

Use the Sorting line - Ascending or Descending to sort the resulting Query Table

  • To sort multiple fields

    • The computer will apply sort precedence for sorting fields listed on a query from left to right.

  • Sorting can be combined with any of the filtering techniques (criteria) we have already discussed.

CSE 2111 Sorting, Summarizing and Calculating in Queries


Write a query to list the City and State of all clients The list should be sorted in ascending order by State and then by City.

Minor Sort

Major Sort

CSE 2111 Sorting, Summarizing and Calculating in Queries


The data table list should be sorted in ascending order by State and then by City.

The resulting dynaset

CSE 2111 Sorting, Summarizing and Calculating in Queries


You have decided to give list should be sorted in ascending order by State and then by City.all clients a 10% break on each charge they have made. Write a query to list the ClientID, each charge amount before the discount, the discount, and the charge amount after the discount .

What if we want to create a field in a query that is the result of a calculation?

CSE 2111 Sorting, Summarizing and Calculating in Queries


The resulting list should be sorted in ascending order by State and then by City.dynaset

Notice new field names

CSE 2111 Sorting, Summarizing and Calculating in Queries


To Aggregate fields in a Query use the Group by feature list should be sorted in ascending order by State and then by City.

Group by

Sum

Avg

Min

Max

Count

StDev

Var

First

Last

Expression

Where

  • Use the Group by feature to summarize a field – calculating its average, min, max, sum etc.

  • Select the group(s) you wish to aggregate - Access will automatically group by the lowest detail level field.

  • In the desired field(s) select the function (sum, average, etc) using the pull down menu

CSE 2111 Sorting, Summarizing and Calculating in Queries


Query design grid's Total Row options list should be sorted in ascending order by State and then by City.:

Group By: Define the groups you want to perform the calculations for.

Aggregate Functions: Sum, Avg, Min, Max etc.

Where: Specify criteria for a field, so only those records that meet this criteria will be included in the aggregates.

Expression: Use if a calculated field is required in the query. Expressions will be calculated AFTER the fields are aggregated.

CSE 2111 Sorting, Summarizing and Calculating in Queries


Write a query that lists how many payments were made by each Client and their total payments. List the First Name, Last Name, the number of payments made, and the total dollar amount of these payments.

CSE 2111 Sorting, Summarizing and Calculating in Queries


The data Client and their total payments. List the First Name, Last Name, the number of payments made, and the total dollar amount of these payments.tables

(partial view)

The resulting dynaset

Notice new field names

CSE 2111 Sorting, Summarizing and Calculating in Queries


Write a query to summarize by Client, all of the charges made after 5/1/2008. Add a surcharge of 20% to these charges. List the First Name, Last Name, Original Charge and New Charge.

CSE 2111 Sorting, Summarizing and Calculating in Queries


The data made after 5/1/2008. Add a surcharge of 20% to these charges. List the First Name, Last Name, Original Charge and New Charge.tables

Notice new field names

The resulting dynaset

CSE 2111 Sorting, Summarizing and Calculating in Queries


ad