Transcript Lecture
DB Implementation:
MS Access Queries
1
Access Queries
► ► ► ►
Query Creation Sorting & Filtering Query Types Dynamic Query Outline
2
MS Access Queries
Database Queries
► ► 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 for forms and reports
How does it work?
► ► ► Access translates QBE to SQL 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
Datasheet view ► ► 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 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
Simple Query ► ► 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 Select Query ► ► 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
Datasheet View ► ► Sorting • Click column and right-click Filtering • Filter Tool (Home tab) Filter by Selection Filter by Form Advanced Filter/Sort 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
Query Criteria Expressions
Expression
Between #1/1/99# and #12/31/99# In (“John”,”Mary”,”James”) Is Null Like “Acc?ss” Like “*s” Like “v*” <1000 1000 Like “[A-C]??” ????
Len([Surname])= Val(4) Right([Surname],2)= “ss” Left([Surname],2)= “ac”
Returns
Dates from 1/1/99 to 12/31/99 Records with John, Mary, or James Records with no entry ‘Acc’ followed by any character, followed by ‘ss’ Ends in ‘s’ Starts with ‘v’ Less than 1000 Equal to 1000 Starts with A through C and has two more characters Any four characters Any Surname of 4 characters Any Surname ending in “ss” Any Surname starting with “ac” 7
Access Queries: Expressions
Using expressions to create a calculated
► ►
column
Enter expressions in blank column of query design view • NAME: [Field1] operator [Field2] Format the display in Format property of the field
Examples
► ► ► ► Simple math • DiscountPrice: [Discount] * [StandardPrice] String Concatenation • Name: [FirstName] & “ ” & [Lastname] Date & Time math • DateDiff(Interval, BeginDate, EndDate) • HireAge: DateDiff(“yyyy”,[BirthDate],[HireDate]) DateAdd(Interval, Number, Date) RetireDate: DateAdd(“yyyy”,25,[HireDate]) Customized Sorting • Switch(expr1, value1, expr2, value2, etc.) Switch([City]=“Seatle”, 1, [City]=“Redmond”, 2, etc.) 8
Access Queries: Dynamic Criteria
► ►
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 Do not use existing field names • Can use expressions in criteria 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.
4.
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 •
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
Inner Join
► ► Default join in Access (i.e. Natural Join) Returns only the records where joined fields are equal in both tables
Left Outer join
► Returns all records from the left table
Right Outer join
► Returns all records from the right table 10
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 11
Access Queries: Action Queries
Append Query Appends table rows to an existing table 1.
2.
3.
4.
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 1.
2.
3.
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.
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 4.
Run the query to update fields/columns.
Make Table Query 1.
Create a select query.
Creates a new table from rows of other tables/queries 2.
3.
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
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.
3.
4.
5.
6.
Create a select query.
Convert the select query to a Crosstab query Crosstab Tool in Query Type group of Design tab Set Row Headings Crosstab = Row Heading, Total = Group By, Sum, Avg, etc.
Set Column Headings Crosstab = Column Heading, Total = Group By Set the calculation field and function Crosstab = Value, Total = Sum, Avg, etc.
Set Criteria to filter data (if needed).
Total = Where, Criteria = expression 13
Access Queries: SQL Queries
Data-definition Query Defines/Changes the definition of a database object ► ► Create a table •
CREATE TABLE
table_name
( field1 type(size), field2 type(size), … ,
PRIMARY KEY
(field)) Modify a table •
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) •
SELECT
field1, field2, ….
FROM
table1
UNION SELECT
fieldA, fieldB, ….
FROM
table2 1.
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