Expressions and Summarization

1. Expressions and Summarization William Klingelsmith

2. Expressions • Expressions perform calculations row-wise and derive new fields from existing fields in queries • Expression syntax will always resemble: • Expression Name: <Expression Body> • Expressions have an advantage over storing items in a field in your tables • Smaller database size • Drawback: if the expression is very complex, query run time will be longer

3. Expressions • In the right-most blank in your field list inside of your query design, click in the field row. • Expressions can be entered directly into the blank by hand or the expression builder can be used (Builder button on the design tab)

4. Expressions • Within builder, you have access to built-in functions in access(SUM, MIN, MAX, etc.) • Most of the time, your expressions will be constructed using basic mathematical operators • Expanding the dropdown next to the name of your database will let you access tables(and fields), queries, forms, and reports for use in your expressions

5. Expressions • Let’s create a few expressions: • Calculate the number of albums sold • Calculate the price of an album with tax • Projected next year sales • (NumberofAlbumsSold +1000) * 0.50 • Write an expression to determine whether an album is expensive (Over $10 in price) • Write an expression to display only the year that albums were released

6. Summarization • Expressions are very powerful, but as previously stated they work row-wise • What if we wanted to find an average price of albums by genre or the total number of albums sold in each genre?

7. Summarization • By using the Total Row in your design, these types of queries can be created. • To activate the total row in your query design, click the Totals button located on the Design tab(Giant Sigma).

8. Summarization • Create a new query which contains the tables (Albums, Sales, and Genre) • Populate the query using the fields (GenreName, SalesinDollars, and AlbumPrice) • Turn on the Totals Row mechanism

9. Summarization • Notice that a new row is created in your query design. • We will set up summaries of data by using this row. • All fields have ‘Group By’ as their default option. This will need to be changed depending on what fields you wish to summarize your queries by.

10. Summarization • For this example, we will using the following total row options • GenreName: Group By • SalesinDollars: Average • Album Price: Average

11. Summarization • When you run the query, notice that there are fewer results than before. • There are only unique entries for the GenreName field under which we placed the ‘Group By’ clause • What is going on here?

12. Summarization Notice that when the Totals Row is turned off there are two results for EBM, but when it’s on we only get one results with EBM The Total Row will compact the results of a query by the field with the Group By clause into bins Functions under fields in these bins are performed

13. Summarization • Notice that each field using a function in the totals row now has prepended to it the name of the function • AvgOfAlbumPrice • These fields can be aliased to have a more-specific name by using expression syntax • Average of Album Price: AlbumPrice

14. Totals Row Examples • Create a query to find the number of Albums in each genre • You should get 12 results • Find the highest priced album in each genre • You should get 12 results