Queries

1. Queries Comp1000 Lecturer: Ali Darejeh Resources: Alexander, M., & Kusleika, R., 2016, Access 2016 Bible, John Wiley & Sons. Darejeh, A. 2011. Reference guide to Access 2010. Saheer engineering group. Tutorialspoint: https://www.tutorialspoint.com Quac kit: https://www.quackit.com Gcflearnfree: https://www.gcflearnfree.org Microsoft Office support website: https://support.office.com

2. Objectives • Query and its usage • Create a query • Defining criteria • Aggregating data • Determine when to use an action query • Update data with an updatequery • Add records to a table with an appendquery • Create a table with a make tablequery • Delete records with a delete query • Create a parameter query to provideflexibility • Summarize data with a crosstab query • Find unmatched records with a query • Find duplicate records with a query

3. Query • A query is an Access feature that allows users to ask questions about the data, based on certain criteria. • A criterion can be a number, text phrase or an expression that allows records to be searched and filtered based on their field values. e.g., the following criteria lists all records where Publisher = “Wiley” , and Year> = 2017 • Queries also allow users to updateand modify data intables based on the defined criteria. e.g., adding 10$ to all the books published after 2017.

4. Types ofQueries • Selectquery • Returns records that satisfy the definedcriteria • Totalquery • Performscalculations • Actionquery • Updates/modifiesdatabase • Specialisedqueries • Parameter Queries • CrosstabQueries • Find Unmatched Recordsquery • Find Duplicate Recordsquery

5. What are the similarities and differences between filter and query? • Similarity: • Filter does the same sort of thing as queries – it allows selection of records based on the defined criteria using and andor. • Differences: • Filter cannot be used to define complicated criteria using functions. • Filterdoes not allow users to save the results for reuse. • Selection type queries can be converted into action "queries" to update or deleterecords. • In contrast with filter, query can be used to select records from more than onetable.

6. CreatingQueries • MS Access provides a number ofways fordesigningqueries: • DesignView • Querywizard • SQL - Structured Query Language

7. Using DesignView • The following steps show designing a SelectQuery in design view: • Select tables that you wouldlike to use in your query. • Select fields in the order you wish to viewthem. • Set any sortcriteria. • Setcriteria • Run the query.

8. SelectQuery Tables Involvedin query Sortorder Selected Fields Show / Hide Fields Criteriarow

9. Running aQuery • Two solutions: • Clicking on the Run icon (!) in the menu bar orfrom the Querymenu. • Selecting Datasheetview. • Results of query will be displayed in Datasheet just as you would view the contents of a database table. • Results of query are temporarily stored ina dynaset. Changes made in Datasheetviewwill be reflected in the originaltable. However, in Multi-Table Queries changes to results do not change the underlying tables (unlike single-table queries).

10. Sorting QueryResults • Results of a query can be sorted on certain fields by specifying a sortorder. • – Ascending orDescending • Sort order is applied on fields fromleft- to-right Sortorder

11. Specifying SelectionCriteria • When we define criteria only the records whose fields match the specified criteria will be displayed. • Several criteria rows can bespecified • In any given criteria row, a record must matchall the criteria to be displayed. • But if there are multiple criteria rows, the record must match just one row to bedisplayed.

12. ANDOR andNot • In a given criteria row, all fieldsmust match criteria(AND). • If multiple criteria are specified in different rows,records can match any of them(OR). • We can also use logical operatorsto specify criteria in a singlefield: • E.g., >=110 And<130 • E.g., “HD” Or“DN” • E.g., Not“HD”

13. AND, OR, and NOTCriteria • Andoperator • Oroperator BranchID is B20 and Balance >5000 • Notoperator BranchID is B20 or Balance >5000 BranchID is notB20 BranchID is B20 orB30

14. Criteria for Different DataTypes • Text fields – enclose in quotes:"Smith" • Double quotes only necessary if text contains space, otherwise Access will put them. • Text is not casesensitive • Date/time – enclose in # signs: #14/10/2012#, #26-Apr-2005#, #1:15PM# • Typing Date() function returns the current date. • Numeric – no delimitersneeded: <=5000 • We can prefix number or date with logicalcriteria • = ,<, <=, >, >=,<> • = can usually beomitted

15. Dates andTimes • Can specify dates and timesbysurrounding them with #signs: • –E.g., • #26/4/05# • #April 26,2005# • #26-Apr-2005# • #1:15PM# • #13:15PM# • Date() • gets currentdate • – Between #1/1/2013# and#31/3/2013#

16. Demo 1 • List the employees whose salary is between 60000$ - 80000$ and they are female. • List the employees who are employed after >15/02/2011 or before <20/06/2008.

17. BETWEEN, IN,LIKE • Between - specifyingranges • E.g. Between 110 And130 • Same as >=110 And <=130 • In - lists ofvalues • E.g. In(“Ali”,“Peter”) • Same as “Ali” Or“Peter” • Like - searching patternsusing wildcards • E.g. Like“*z” • Strings that end in letter‘z’

18. Wildcards Wildcards can be used to specify patterns in a text query criterion:

19. WildcardExamples • The question mark(?) • “H?LL” would match Hall, Hill, andHull • The asterisk(*) • “S*nd” would match Sand, Stand, andStoryLand • The hash(#) • – “????####” would match COMP1000,MATH1313 • etc • Thebrackets ([]) • “[aeiou]*s” would match anything starting with a vowel and ending ins

20. Demo 2 • List the employees whose last names start with W and ends with N. • List the employees whose family names have only 6 letters. Like "w*n" Like "??????"

21. NullCriteria • This keyword is used to list empty fields. • Is Null • Is NotNull • Use key word Null in Criteriarow • – Do notquote the string “Null” otherwise you will be looking for the text string.

22. Demo 3 • List the employees whose family name was not entered.

23. Expressions and Calculated Fields • We can use expressions to define complicated criteria and calculated columns. • E.g. Assuming we have twofields called HourlyRate and HoursWorked,we canuse these fields to create an expression suchas: [HourlyRate] *[HoursWorked] • We can use the expression as acriterion to show only the records with the results of greater than 500 for the following expression: [HourlyRate] * [HoursWorked] >500 • Also we can use the expressions tocreate computedfields: EmployeePay: [HourlyRate] * [HoursWorked]

24. Demo 4 • We want to list the employees whose salary is more than $5000. The only columns that we have are hourly rate and hours worked. We should Create a new column to calculate the salary based on hourly rate and hours worked columns. EmployeePay: [HourlyRate]*[HoursWorked]

25. Expression Builder, Functions, and DateArithmetic • Typing criteria using Expressionbuilder: • Helps to createexpressions • Provides access to built-in Accessfunctions. • Functions work similarly toExcel • Allows you to perform date arithmetic Either directly with +/–, or using functions like DateDiff() • Avoids typos in the criteria part.

26. PmtExample Pmt Function has five arguments, the lasttwo optional as inExcel Expressionbox Double-click toaddtheexpression

27. Conditional Output usingIIF function • IIF function is used to determine if another expression is true or false. If the expression is true, IIf returns one value; if it is false, IIf returns another.  • Functionsyntax • IIF (condition, then-part,else-part) • Examples • BonusRate:IIf([Cost]>500,[StandardRate]+[Bonus],[StandardRate]+40) • PropertyStatus:IIf(Date() – [DateListed] <= 30,“NewListing”, “ForSale”) • Nested IIFstatement is used when we need to return more than 2 true and false values. • –Example: • BonusRate: IIf(Date() –[DateListed]<=30, condition then-part “NewListing”, IIf(Date() – [DateListed]>=180, “Negotiable”, “ForSale”)) condition then-part else-part

28. Useful date functions • Date() • Returns the currentdate • DatePart("yyyy",[HireDate]) • Returns a part of the date. E.g. it returns the year part of the date that is entered in HireDate column. • DateDiff("yyyy", date1,date2) • returns the number of full years, month, days,… between date1anddate2

29. Demo 5 • We want to create a column called employment duration and show the number of years that each employee has worked for the company. We only have Hire Date column. There are 2 ways: employment duration: Date()-[HireDate] return only days employment duration: DateDiff("yyyy",[HireDate],Date()) return years

30. Demo 6 • We want to create a column called employment status and show professional word if the employment duration is greater than 2 years and non-professional if it is less than 2 years. • Employment status: IIf(DateDiff("yyyy",[HireDate],Date())>=2,"professional","non-professional")

31. Total query Access provides a feature called Total to enable users perform calculations on an entirecolumn of data instead of each field, and return a singlevalue. • What can we do using Total? • Average • Count • Maximum • Minimum • StandardDeviation • Sum • Variance

32. Adding Aggregate Functions toQueries Click Totals toadd querytotals Totalrow Aggregate Functions

33. Adding Aggregate Functionsto Datasheets Click Totals to add the totalsrow Click the Total row arrow to show the choice of aggregate functions

34. Demo 7 • We have a payment table of a company. The company had different payments in each month on the same day. We want to create a query to show the total payment of each month. Use: Payment demo 7 table

35. Using QueryWizard to create a simple query The other way to create a Query is using QueryWizard, then modify the query in the Designview. QueryWizard

36. QueryWizard The first step is selecting query type. • Simple query: It filters data based on criteria. • Crosstab query: A crosstab query calculates a sum, average, or other aggregate function, and then groups the results by two sets of values one set on the side of the datasheet and the other set across the top. • Find duplicate query: A find duplicates query allows you to search for and identify duplicate records within a table. • Find unmatched query: When we want to compare two tables and identify records in one of the tables that have no corresponding records in the other table. 

37. QueryWizard Select the fields that you want to have in your query. Select a Table orQuery Remove all fields from the Selected Fieldslist Remove a single field from the Selected Fields list Move all fields to the Selected Fieldslist Move a single field to the Selected Fieldslist

38. QueryWizard • If you want to see individual records, click Detail, and then click Next. • If you want to see summarized numeric data, such as averages, click Summary, and then click Summary Options.

39. QueryWizard In the Summary Options dialog box, specify which fields you want to summarize, and how you want to summarize the data. Only number fields are listed. If you want the query results to include a count of the records select the appropriate Count records in data source name check box.

40. QueryWizard If you added a date-time field to the query, the Wizard asks how you would like to group the date values.

41. QueryWizard On the last page of the wizard, give the query a title, specify whether you want to open or modify the query, and then click Finish.

42. Demo 8 • We have a payment table of a company. The company had different payments in each month. We want to use wizard to create a query to show the total payment of each month. Use: Payment demo 7 table

43. ActionQueries • It performsan action to change data based on the defined criteria. • It startsby making Select query, then change type to: • Update • Append • Delete • Make table LocationsofActionQueriesonQueryToolsDesignTab

44. Update query • Update query is used to change some fields automatically based on the defined criteria. • Example: • add 20$ to all the rooms’ prices of a hotel. • All athletes with point average greater than 2 should have the Eligible field set to Yes.

45. Demo 9 • We want to add 2$ to the hourly rate of personnel who are employed after 2017 and 10$ if they are employed before 2017. • IIf([HireDate]>#1/1/2017#,[HourlyRate]+2,[HourlyRate]+10)

46. DeleteQuery • Selects records based on the defined criteria and removesthem permanently. • Example: Delete students with GPA greater than 3.7. • For using this query just add the fields that you want to define your criteria for. • It can be used after a Make Tableor Append query to simulate amove.

47. Demo 10 • We want to delete all the personnel who are employed after 2017 or before 2010.

48. MakeTableQuery • Make table query is used to copy information from one table to a newtable. • Example: Copy all people who donated more than 300$ to a new table and delete them using delete query from the main table to keep the table fast andefficient. • If destination table exists, Access will prompt you to delete the existingtable.

49. Demo 11 • We want to create a query to copy first name and last name of employees who are employed after 2017 to a new table called “new employees”.

50. AppendQuery Append query is used to copy records that match the defined criteriafrom one table to an existing table. • Example: Copy donators who donated more than 300$ to an existing table.