DB Implementation MS Access Queries 1 Outline n

DB Implementation: MS Access Queries 1

Outline n Access Queries ► ► Query Creation Sorting & Filtering Query Types Dynamic Query 2

MS Access Queries n Database Queries ► ► n Core DBA skill From SQL to Query by Example (QBE) What does it do? ► Find target information • Retrieve, Filter, Sort, Aggregate/Summarize ► Manipulate data • Perform calculations • Add, Change, Delete, Combine data in tables • Assemble/Supply data forms and reports n How does it work? ► ► ► Access translates QBE to SQL performs data manipulations based on Relational Algebra Access queries create a dynaset (“live” view of table) • changes made in data by query is reflected in underlying tables 3

Access Queries: Views n Datasheet view ► ► n For displaying the result of the query Useful for reviewing/validating the query Design View ► ► For creating/modifying a query using drag & drop GUI (i. e. , QBE) Consists of Diagram Pane & Grid Pane • Add tables/queries to the Diagram Pane • Add fields to the Grid Pane (Field row) ► Can sort/filter/compute by fields • Sort row: set to Ascending/Descending • Criteria row: use Expression to apply data filter • Total row: compute (sum, min, max, count, etc. ) of each field ► n Automatically generates SQL statements SQL View ► ► For creating/modifying a query by manually writing SQL statements Only way to create SQL-specific queries • Union/Data-definition/Pass-through query 4

Access Queries: Basic Types n Simple Query ► ► n Uses one table/query To generate a subset (row/column) of a table Multi-table Query ► ► Joins multiple tables/queries To merge small chunks of data in normalized tables • Linked tables are automatically linked in in the Query Design Grid • Creating a link in the Query Design Grid does not permanently link tables n Select Query ► ► n Selects records that meet given criteria Does not change the data Parameter Query ► Prompts for query criteria values (parameters) to run a dynamic query 5

Access Queries: Sort & Filter n Datasheet View ► Sorting • Click column and right-click ► Filtering • Filter Tool (Home tab) à Filter by Selection à Filter by Form à Advanced Filter/Sort n Design View ► Sort using the Sort Row • Sort priority is from left to right for multiple sort ► Filter using the Criteria Row • Criteria in multiple rows make OR query • Criteria in single row make AND query • Criteria in a single cell à à AND/OR Wildcards (*) in Like and Between – e. g. Like “A*”, Like “[A-C]*”, Like “*av*”, Like “ave? ”, Not Like “A*” – e. g. Between 1950 and 1960, >1960 6

Access Queries: Query Criteria n Query Criteria Expressions Expression Returns Between #1/1/99# and #12/31/99# Dates from 1/1/99 to 12/31/99 In (“John”, ”Mary”, ”James”) Records with John, Mary, or James Is Null Records with no entry Like “Acc? ss” ‘Acc’ followed by any character, followed by ‘ss’ Like “*s” Ends in ‘s’ Like “v*” Starts with ‘v’ <1000 Less than 1000 Equal to 1000 Like “[A-C]? ? ” Starts with A through C and has two more characters ? ? Any four characters Len([Surname])= Val(4) Any Surname of 4 characters Right([Surname], 2)= “ss” Any Surname ending in “ss” Left([Surname], 2)= “ac” Any Surname starting with “ac” 7

Access Queries: Expressions n Using expressions to create a calculated column ► Enter expressions in blank column of query design view • NAME: [Field 1] operator [Field 2] ► n Format the display in Format property of the field Examples ► Simple math • Discount. Price: [Discount] * [Standard. Price] ► String Concatenation • Name: [First. Name] & “ ” & [Lastname] ► Date & Time math • Date. Diff(Interval, Begin. Date, End. Date) à Hire. Age: Date. Diff(“yyyy”, [Birth. Date], [Hire. Date]) • Date. Add(Interval, Number, Date) à ► Retire. Date: Date. Add(“yyyy”, 25, [Hire. Date]) Customized Sorting • Switch(expr 1, value 1, expr 2, value 2, etc. ) à Switch([City]=“Seatle”, 1, [City]=“Redmond”, 2, etc. ) 8

Access Queries: Dynamic Criteria n Parameterized Queries ► Dynamic query based on varying criteria value • ► e. g. retrieve books written by a given author Enter the parameter name in square brackets in criteria • • Do not use existing field names Can use expressions à n e. g. Like "*" & [Last Name] & "*“ Query Criteria from Form Entries ► Use a form to enter query parameter values • good for multiple parameter entries 1. 2. 3. Create a parameter query Create a form with unbound input control for each of query parameters Add a command button that will run the parameter query • 4. Run Query Action of Miscellaneous Category Reference the form controls that hold parameter values from the query • i. e. change the criteria to form controls • Parameter name = fully qualified name of form control 9

Access Queries: Joins n Inner Join ► ► n Left Outer join ► n Default join in Access (i. e. Natural Join) Returns only the records where joined fields are equal in both tables Returns all records from the left table Right Outer join ► Returns all records from the right table 10

Access Queries: Advanced Types n Action Queries ► Append Query • Appends table rows to an existing table ► Delete Query • Deletes table rows ► Update Query • Modifies the values of particular fields for particular records ► Make Table Query • Creates a new table from rows of other tables/queries à will overwrite existing table of the same n Crosstab Query ► n Performs mathematical operations on intersection of two fields SQL-Specific Queries ► Data-definition Query • Defines/Changes the definition of a database object (e. g. , create/modify a table) ► Union Query • Combines multiple SELECT queries ► Pass-through Query • Send command directly to ODBC database server to run server-side SQL 11

Access Queries: Action Queries n Append Query Appends table rows to an existing table 1. 2. 3. 4. n Delete Query Deletes table rows 1. 2. 3. n Create a select query. Convert the select query to a Delete query. • Delete Tool in Query Type group of Design tab Run the query to delete rows/records. Update Query Modifies the values of particular fields for particular records 1. 2. 3. 4. n Create a select query. Convert the select query to an Append query • Append Tool in Query Type group of Design tab Choose the destination fields for each column in the append query. Run the query to append rows/records. Create a select query. Convert the select query to an Update query. • Update Tool in Query Type group of Design tab Set Update To: (and Criteria when appropriate) rows Run the query to update fields/columns. Make Table Query Creates a new table from rows of other tables/queries 1. 2. 3. Create a select query. Convert the select query to a Make Table query. • Make Table Tool in Query Type group of Design tab Run the query to update fields/columns. 12

Access Queries: Crosstab Query n Crosstab Query Performs mathematical operations on intersection of two fields ► Using the Crosstab Query Wizard 1. 2. 3. 4. 5. 6. 7. ► Create a select query to join tables (if needed). Click Query Wizard in the Other group of the Create tab. Select Crosstab Query Wizard in the New Query dialog box & click OK. Choose table/query with which to create a crosstab query & click Next. Choose the field(s) whose values will be used as row headings & click Next. Choose the field whose values will be used as column headings & click Next. Choose a field and a function to use to calculate summary values. à The data type of the field that you select determines which functions are available. Using the Crosstab Tool 1. 2. Create a select query. Convert the select query to a Crosstab query à 3. Set Row Headings à 4. Crosstab = Column Heading, Total = Group By Set the calculation field and function à 6. Crosstab = Row Heading, Total = Group By, Sum, Avg, etc. Set Column Headings à 5. Crosstab Tool in Query Type group of Design tab Crosstab = Value, Total = Sum, Avg, etc. Set Criteria to filter data (if needed). à Total = Where, Criteria = expression 13

Access Queries: SQL Queries n Data-definition Query Defines/Changes the definition of a database object ► Create a table • ► Modify a table • n ALTER TABLE table_name ADD/ALTER COLUMN field type(size) DROP COLUMN field Union Query Combines multiple SELECT queries ► Select queries must have the same fields (i. e. , Union compatible) • 1. 2. n CREATE TABLE table_name ( field 1 type(size), field 2 type(size), … , PRIMARY KEY (field)) SELECT field 1, field 2, …. FROM table 1 UNION SELECT field. A, field. B, …. FROM table 2 Create the select queries in Design view Copy & paste SQL statements into a union query Pass-through Query Send command directly to ODBC database server to run server-side SQL 1. 2. Configure the server DBMS as an ODBC data source Create a Pass-through query 14