DB Implementation: MS Access Queries

1. DB Implementation:MS Access Queries

2. MS Access Queries Database Design • Database Queries • Core DBA skill → A way of searching for & extracting data from tables table에서 data를 검색하거나 추출하는 방법 • What does it do? • Find target information → Retrieve, Filter, Sort, Aggregate/Summarize • Manipulate data → Add, Change, Delete, Combine (& calculate) data in tables • Assemble/Supplydata → for Forms and Reports • How does it work? • Query object SQL & a dynaset(“live” view of table) • Data changes made in query is reflected in underlying tables

3. Access Queries: Views Object Relationship Pane Add tables/queries Add fields to the Design Grid Design Grid Sort row → ascending/descending sort Show row → show/hide fields Criteria row → apply data filter Totals row → compute aggregate stats Database Design • Datasheet view • For displaying the result of the query → Useful for reviewing/validating the query • Design View • For creating/modifying a query via drag & drop GUI

4. Access Queries: Basic Types Database Design • Simple Query • Uses a single table/query • To generate a subset (row/column) of a table • Multi-table Query • Joins multiple tables/queries • Joining tables in Query Design does not permanently link tables • Select Query • Selects records that meet given criteria • Criteria row ← Selection Criteria • Parameter Query • Prompts for query criteria values (parameters) to run a dynamic query • Criteria row ← [Query Prompt] (or Search Form control name)

5. Access Queries: Sort & Filter - Gcflearnfree.org - Database Design • Design View • Sort using the Sort Row • Sort priority is from left to right for multiple sort • Filter using the Criteria Row • AND query ← criteria in a single row • OR query ← criteria in multiple rows • Criteria in a single cell → AND/OR • Query Criteria • Determines which record to include • “expressions” that consist of • Constants (e.g., 2000, “Adams”) • Operators (e.g., >, +, *, AND) • Functions (e.g., DateDiff) • Field references (e.g., [Lastname])

6. Access Queries: Query Criteria Database Design • Date/Time fields • Number fields

7. Access Queries: Query Criteria Database Design • Text fields

8. Access Queries: Expressions Database Design • Using expressions to create a Calculated Field • Enter expressions in blank column of query design view • NAME: [Field1] operator [Field2] • Examples • Simple math • DiscountPrice: [Discount] * [StandardPrice] • String Concatenation • Author: [FirstName] & “ ” & [Lastname] • Date & Time math • HireAge: DateDiff(“yyyy”,[BirthDate],[HireDate]) • DateDiff(Interval, BeginDate, EndDate) • RetireDate: DateAdd(“yyyy”,25,[HireDate]) • DateAdd(Interval, Number, Date) • Customized Sorting/Format • Branch: Switch([City]=“Seattle”, 1, [City]=“Redmond”, 2, [City]=“New York”, 3) • Switch(expr1, value1, expr2, value2, etc.)

9. Access Queries: Sort & Filter Database Design • Datasheet View • Common Filters →Filter for specific values • Click the icon on the right side of the field label • Check the values for the filter OR • Click the Range Filter (e.g. Date Filter) & specify the filter value • Filter by Selection →Filter by selected value • Select a specific value in the datasheet • Right-click & select a filtering option

10. Access Queries: Sort & Filter Database Design • Datasheet View • Filter by Form → Filter on several fields on a form • Click Advanced in Sort & Filter group of Home tab • Select Filter by Form • Select multiple filter values as needed • Advanced Filter → Define custom filters→ Learn how to write query criteria • Create a filter by Common Filter, Selection, or Form • Click Advanced in Sort & Filter group of Home tab • Select Advanced Filter/Sort

11. Access Queries: Dynamic Criteria Database Design • Parameter Queries • Dynamic query based on varying criteria value • Enter the parameter name in square brackets in criteria • Do not use existing field names • Can use expressions → e.g. Like "*" & [Last Name] & "*“

12. Access Queries: Dynamic Criteria Database Design • Query Criteria from Form Entries • Use a form to enter query parameter values →good for multiple parameter entries • Create a parameter query • Create a search form with unbound input control for each of query parameters • Add a command button that will run the parameter query • Run Query Action of Miscellaneous Category • Change the query criteria to search form control names

13. Access Queries: Joins Database Design • Inner Join • Default join in Access (i.e. Natural Join) • Returns only the records where joined fields are equal in both tables • Outer join • Returns all records from one of the tables

14. Access Queries: Advanced Types • 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 name • Crosstab Query • 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 L546 Session 7, IU-SLIS

15. Access Queries: Action Queries • Append Query  Appends table rows to an existing table • 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. • Delete Query  Deletes table rows • 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 • 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 • 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. L546 Session 7, IU-SLIS

16. Access Queries: SQL Queries • Data-definition Query  Defines/Changes the definition of a database object • Create a table • CREATE TABLEtable_name( field1 type(size), field2 type(size), … , PRIMARY KEY(field)) • Modify a table • ALTER TABLEtable_nameADD/ALTER COLUMNfield type(size)DROP COLUMNfield • Union Query  Combines multiple SELECT queries • Select queries must have the same fields (i.e., Union compatible) • SELECT field1, field2, …. FROM table1UNIONSELECT fieldA, fieldB, …. FROM table2 • 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 • Configure the server DBMS as an ODBC data source • Create a Pass-through query L546 Session 7, IU-SLIS