LectureWhat’s a Query?Creating a QueryAdding Tables to your QuerySlide 5Adding Fields to your QueryRun your QuerySort and FilterExpressionsWhy Use Expressions?Expression SyntaxExpressions in UseEntering ExpressionsCommon ExpressionsTotalsTotals (cont.)SQL (Structured Query Lang.)SQL, exampleReports from QueriesQuery to ReportLectureAccess – QueriesWhat’s a Query?•A question you ask a database–ie: “Who are my Stockton customers?”–ie: “How much did Bob sell on the 14th?”•A saved set of filters, sorting, and other specifications for later use in a form, report, or to make a new tableCreating a Query•Create Ribbon•Query DesignAdding Tables to your QueryAdding Tables to your Querynote the relationshipis already created because of the lookup the tables you chose areadded to the queryAdding Fields to your Querydouble-click a field toadd it to your queryfield is added to your queryadd a few more fieldsRun your Querylike a table, you can now see the recordsthat resulted from your queryClick this icon to go back to design modeSort and FilterUse the “Sort” row to specify ways to sort your data. This setting will save with your query for later use. Use the “Criteria” row to specify which rows should stay. Try any combination of these:like "*david*"> 1.25Between #4/7# and #4/10#Expressions•Expressions allow you to create new fields (in a query) that are calculated from data in other fields.•The values of an expression are temporary – they are not “saved” anywhere, they only exist when the query is run.Why Use Expressions?•Modify Text:–Combine strings together–See just the first few letters of a string•Calculations:–Commission–Tax–Quantity * PriceExpression SyntaxName of our new ExpressionA colon (:)The calculation.Expressions in UseIn this query, we have created a new Expression (called “FullName”) that calculates a string based on each record’s First and Last name field.When the Query is run, we can see the results of our new Expression. But… oops! We made a minor mistake.Entering Expressions•Enter directly into Query Builder, or•Build using Expression Wizard(right click)Common Expressions•FullName: [FirstName] & " " & [LastName]•Earned: [Price] * [Commission]•Location: [City] & ", " & [State] & " " & [ZIPCode]TotalsTotal row allows you to group, sum, average, etc…Totals (cont.)In this query, we are telling Access to group by Employee, and sum the Prices. We’re also filtering only for sales between the 1st and 5th.SQL (Structured Query Lang.)•special-purpose programming languageDdesigned for managing data held in aDRDBMS•Based on algebra and calculus •Data manipulation and data definition–SELECT Book.title AS Title, –COUNT(*) AS Authors –FROM Book –JOIN Book_author ON Book.isbn = Book_author.isbn –GROUP BY Book.title;SQL, example–SELECT Table.Field, Table.Field, Table.Field, etc, [Field]operator(s)[Field] AS NewFieldName –FROM Table1 INNER JOIN [Table2] ON Table1.PrimaryKeyField = Table2.SameField–ORDER BY Table.Field, Table.Field;**ORDER MATTERS – you cannot organize, sort, etc. until you have established the relationship. The SELECT command must come first and typically the FROM command must immediately follow.Reports from Queries•After a query is created, it can be used as the source data for a report.Query to