SQL200A Microsoft Access SQL Design

Orange Coast Database Associates Course (800)355-9855 or http://ocdatabases.itgo.com SQL200A Focus on Microsoft Access SQL Orange Coast Database Associates Specializing in Microsoft Office, Access, SQL, and related technologies Classes custom designed forWorking Professionals http://ocdatabases.itgo.com San Juan Capistrano, CA (800)355-9855 Accelerated Computer Training for Working Professionals Admissions Case

Welcome to Orange Coast Database Associates, Inc. Accelerated Computer Training for Working Professionals P.O. Box 6142 Laguna Niguel, CA 92607 949-489-1472 http://www.d2associates.com 1 About Us Orange Coast Database Associates  Loose association of local University instructors and IT Professionals.  Training – Open Enrollment (public classes in San Juan Capistrano using standardized material, some of it proprietary) – On-site (classes for groups, may be custom tailored) – Private desk side training for individuals  Consulting & Programming (incl. offshore) 2 About Us

3 About Us Contact Information  Contact: Dan D’Urso  Phone: 800-355-9855  Fax: 949-485-6284  Email: slides.1@dhdursoassociates.com  Course listing: – http://www.dhdursoassociates.com/courses.shtml  Course calendar: – http://www.dhdursoassociates.com/xcalendar/calen dar.htm 4 About Us

SQL200A – Access SQL Introduction to Access SQL for experienced Windows users P.O. Box 6142 Laguna Niguel, CA 92607 949-489-1472 http://www.dhdursoassociates.com 1 Admissions SQL200A SQL200A – Access SQL  Introduction (s)  Facilities  Course Packet – Student questionnaire – PowerPoint handouts for all sessions – Evaluation form – Training Certificate 2 SQL200A Admissions

SQL200A – Access Query Design (SQL)  Quick pace for experienced windows users  Assumes some prior knowledge of Access  End-user, not programmer, oriented  Somewhat structured towards personal use for analysis and reporting 3 SQL200A Admissions SQL200A – Access Query Design Course Topics:  Select Queries – Filter Criteria – Joins – Grouping  Action Queries – Maketable – Insert (Append) – Update – Delete  Union 4 SQL200A Admissions

SQL200A – Access Query Design Approach: Access has many ways to accomplish tasks. Queries can be constructed using SQL View or the Query Designer. This course focuses on the former. SQL View Used in code; but helpful in understanding what a query does Query Designer Use to construct queries 5 SQL200A Admissions SQL200A – Access Query Design Course Format:  2 Sessions  Lecture  Demo  Extensive student “hands-on”  Exercises are cumulative – later examples build on objects created earlier 6 SQL200A Admissions

SQL200A – Access Query Design Course Schedule (3 hour sessions):  Session 1  Session 2 7 SQL200A Admissions Notes 8 SQL200A Admissions

1 SQL200A Access SQL Query Design Module 1 – Relational Database Background, Basic Single Table Retrieval Operations P.O. Box 6142 Laguna Niguel, CA 92607 949-489-1472 http://www.d2associates.com SQL200A  Session 1 – Review of Relational Databases and Single Table Retrieval, Functions and Operators  Session 2 – Multi-table retrieval, Subqueries, Unions, Action Queries, Indexes Admissions 2 SQL200A Module 1

2 SQL200A  Lecture/discussion with demonstrations – Mostly practical, but a dose of “theory”  Assumes some familiarity with MS Access  Hands-on  Exercises (Mostly cumulative)  About 3 hours per module  Frequent breaks Admissions 3 SQL200A Module 1 Module 1  Concepts – Relational databases – SQL – Access Query Designer  SELECT Query – Basic – Filter criteria – Complex criteria – Parameters • Field Operations • Functions • Operators • Calculated fields • Concatenation 4 Admissions SQL200A Module 1

3 Microsoft Access Query Design  Focus of this class will be on SQL view, with the query designer used from time-to-time to explain concepts  SQL is widely used for: – Database administration – Enterprise application development – Data driven web sites – Retrieving and modifying data in MS Access databases  A foundation skill for eBusiness and almost all major business applications that use relational databases Admissions 5 SQL200A Module 1 Access Query Design and SQL  The SQL engine Access uses is called JET  It support both MS Access and Visual Basic  It uses a dialect of SQL which is very close to standard SQL 6 Admissions SQL200A Module 1

4 Relational Database Basics  Storage  Databases  Tables  Rows  Columns  Indexes  Views  SQL interface Admissions 7 SQL200A Module 1 Storage  In Access one file with extension mdb. I.e mydatabase.mdb.  Database splitter can be used to split it into two parts – a front end and a back end.  Interface to physical storage via the “JET” database engine. This is the behind-the scenes-workhorse. 8 Admissions SQL200A Module 1

5 Databases  In the Access world refers to all objects stored in the mdb. – Tables – Queries – From – Reports – Macros – Code modules Admissions 9 SQL200A Module 1 Relational Database Table 10 Admissions SQL200A Module 1

6 Sample Access Database Admissions 11 SQL200A Module 1 Database Relationships 12 Admissions SQL200A Module 1

7 Approaching SQL Query Design  Try to build queries a step at a time  Save existing queries that are working as you like; modify them with a new name when building a modified query  Try looking at the design view from time-to-time Admissions 13 SQL200A Module 1 Query Types  Select  Action – Update – Delete – Insert – Maketable  Special use queries – Data Definition Language (DDL) – Union – SQL pass-through 14 Admissions SQL200A Module 1

8 DDL  Data definition language (DDL) to create and modify tables, etc. – Create, alter, drop, etc. – Must be “hand-coded” in Access – But can be useful for database administration  Done in MS Access via SQL Specific Queries – Will not be covered in the course Admissions 15 SQL200A Module 1 MS Access DDL Queries 16 Admissions SQL200A Module 1

9 DCL  Data Control Language (DCL) to control user rights, etc. – Grant – Revoke – Constraints  Used primarily in enterprise databases  Can be done in Access but not covered in this course Admissions 17 SQL200A Module 1 DML  Data Manipulation Language (DML) – Select Query – Append Query – Update Query – Delete Query – Maketable Query (a special type of Select) – Union Query (really combined Selects)  MS Access Query Designer will create these statements “behind the scenes” 18 Admissions SQL200A Module 1

10 MS Access Action Queries Admissions 19 SQL200A Module 1 Other Access Queries  Crosstab  Pass Through – used to pass an SQL statement directly to a back end database w/out processing by Access 20 Admissions SQL200A Module 1

11 Sample Database  Before we continue…  Load the sample database if you haven’t already Admissions 21 SQL200A Module 1 Query Designer Right click anywhere for SQL View Double click or drag to add a field Double click to add a table 22 Admissions SQL200A Module 1

12 SELECT Queries Basic Syntax: Select column-list or * From table-list * Means all columns Admissions 23 SQL200A Module 1 MS Access SELECT SQL 24 Admissions SQL200A Module 1

13 MS Access SELECT Query Drag and Drop or double click Admissions 25 SQL200A Module 1 Simple Select Query Results 26 Admissions SQL200A Module 1

14 SELECT w/ Where Filters retrieved rows Syntax: SELECT column-list FROM table-list WHERE selection-criteria Admissions 27 SQL200A Module 1 SELECT w/ WHERE 28 Admissions SQL200A Module 1

15 SELECT w/ WHERE (Filter) Admissions 29 SQL200A Module 1 SELECT w/ WHERE Results 30 Admissions SQL200A Module 1

16 Complex Filters Follows normal boolean logic Select PatID, LastName, etc… From patients Where (LastName = “Thomas” or birthdate < #1/1/95#) and city = “Corona”) Admissions 31 SQL200A Module 1 Select w/ Complex Where 32 Admissions SQL200A Module 1

17 Complex Filter AND Admissions 33 SQL200A Module 1 Complex Filter Results 34 Admissions SQL200A Module 1

18 Special Operators  LIKE  IN  BETWEEN  IS NULL Admissions 35 SQL200A Module 1 Like (“Wild Card Matches”)  Where customer_last_name like “Jo*” – *= string of characters  Where customer_last_name like “Jo?” – ? = exactly one character Access actually allows more sophisticated Unix style search patterns as well – see help. 36 Admissions SQL200A Module 1

19 LIKE Names ending in “S” Admissions 37 SQL200A Module 1 LIKE Names ending in “S” 38 Admissions SQL200A Module 1

20 LIKE Results Admissions 39 SQL200A Module 1 IS NULL SQL (including JET) uses three valued logic. Must use IS NULL to test for unknowns.A null is NOT the same as blank or empty. Select columnns From clients Where zip IS NULL 40 Admissions SQL200A Module 1

21 IS NULL Admissions 41 SQL200A Module 1 IS NULL 42 Admissions SQL200A Module 1

22 IS NULL Results OK. Now try finding all patients with a blank address. Was the above row retrieved? Why or why not? Admissions 43 SQL200A Module 1 IN Select * From patients Where lastname IN (“Smith”, “Thomas”, “Juarez”) Note: what is inside parentheses is a list. Later we will replace the list with a subquery which generates the list items. 44 Admissions SQL200A Module 1

23 IN List of values Admissions 45 SQL200A Module 1 IN 46 Admissions SQL200A Module 1

24 SELECT w/in IN Results Admissions 47 SQL200A Module 1 BETWEEN SELECT * FROM admissions WHERE admit_date BETWEEN #10/1/2001# and #12/31/2001# Note: between is inclusive 48 Admissions SQL200A Module 1

25 BETWEEN Admissions 49 SQL200A Module 1 BETWEEN Results 50 Admissions SQL200A Module 1

26 Removing Duplicates List once each employee who has a labor ticket SELECT DISTINCT PatNo FROM admissions Removes duplicate rows from result set Admissions 51 SQL200A Module 1 DISTINCT 52 Admissions SQL200A Module 1

27 Distinct Results W/out Distinct With Distinct Admissions 53 SQL200A Module 1 Sorting – ORDER BY Basic syntax: SELECT column list FROM table list WHERE selection criteria ORDER BY column list [DESC] DESC will sort in descending order 54 Admissions SQL200A Module 1

28 Sorting – ORDER BY Example: List patients in Pacific Coast sorted by youngest to oldest Select * From patients Where state in(“CA”, “OR”, “WA”) Order by birthdate desc Admissions 55 SQL200A Module 1 Sorting – ORDER BY 56 Admissions SQL200A Module 1

29 Sorting – ORDER BY Note: modify the “select_in” query Admissions 57 SQL200A Module 1 Sorting – Results 58 Admissions SQL200A Module 1

30 Query/SQL Exercises  List all patient numbers, etc. for patients who co-paid more than $20; sort by co-pay amount, largest first  List diagnostics that contain b, d, or q in the diagnostic code  List the patient id’s for admissions in the fall (Sep, Oct, Nov) of 2001 and 2002 Admissions 59 SQL200A Module 1 Parameter Queries  Can parameterize queries  Use unknown names, example: Select * From patients Where patno =[please input patient no] 60 Admissions SQL200A Module 1

31 Calculated Fields Syntax: SELECT expression AS new column name Result is a “column alias” Example: Select a + b as result Admissions 61 SQL200A Module 1 Calculated Fields 62 Admissions SQL200A Module 1

32 Calculated Fields New column name Follow name with “:” Admissions 63 SQL200A Module 1 Calculated Fields - Results 64 Admissions SQL200A Module 1

33 Concatenation  “Adds” two character fields using “&” symbol  Useful for formatting names, addresses, etc.  Example: – Select firstname & “ “ & lastname as fullname Admissions 65 SQL200A Module 1 Concatenation 66 Admissions SQL200A Module 1

34 Concatenation Sort, but don’t show Admissions 67 SQL200A Module 1 Tip: handy for reports Concatenation Single column with full name, sorted by last name 68 Admissions SQL200A Module 1

35 SQL Exercises  List all the patient names sorted by last name. Add a calculated field which shows their address concatenated together in “written” format with commas and blanks.  Create a second query by modifying the above. Parameterize the query with a wildcard parameter on street. Admissions 69 SQL200A Module 1 End Session 1 Next session: grouping and aggregates, multi-table operations, subqueries, unions, action queries 70 Admissions SQL200A Module 1

36 Notes Admissions 71 SQL200A Module 1 Notes 72 Admissions SQL200A Module 1

1 SQL200A Access SQL Query Design Module 2 P.O. Box 6142 Laguna Niguel, CA 92607 949-489-1472 http://www.dhdursoassociates.com Admissions 1 SQL200A Module 2 Module 2  Part 1 – Miscellaneous Functions  Strings  Dates – Summaries (Grouping)  Part 2 – Joins, subqueries – Inner join – Outer joins – Subqueries  Multi-valued  Single-valued • Part 3 – Unions, Action Queries, Indexes • Unions • Maketable Query • Delete • Append • Update • Indexes 2 Admissions SQL200A Module 2

2 String Manipulation  Trim  Substring  UCase, LCase  Left, Right  See help for others Admissions 3 SQL200A Module 2 String Manipulation 4 Admissions SQL200A Module 2

3 String Manipulation Admissions 5 SQL200A Module 2 String Example 6 Admissions SQL200A Module 2

4 Date Functions  Numerous date functions – DatePart – DateDiff – DateAdd – Etc.  Often used: – Year – Month  Ex: where year(birthdate) = 1999 Admissions 7 SQL200A Module 2 DateDiff  How long did patients stay in weeks? 8 Admissions SQL200A Module 2

5 DateDiff results Note: Access has many date functions with many options. Admissions 9 SQL200A Module 2 Ex: Date Function – Month() 10 Admissions SQL200A Module 2

6 Ex: Date Function – Month() Admissions 11 SQL200A Module 2 Result of Month Function 12 Admissions SQL200A Module 2

7 Summary Functions  Count  Sum  Min  Max  Avg  Often used in conjunction with grouping Admissions 13 SQL200A Module 2 Summary Functions in Access Click the sum symbol Adds a total row 14 Admissions SQL200A Module 2

8 Summary Functions - Syntax Basic syntax: SELECT function(column) FROM table WHERE filter-condition GROUP BY column-list HAVING group-filter Group by all columns to left of one(s) you to want aggregate Admissions 15 SQL200A Module 2 Simple Column Summaries This query counts patient admissions In the year 2001 16 Admissions SQL200A Module 2

9 Simple Column Summaries Admissions 17 SQL200A Module 2 Simple Record Count 18 Admissions SQL200A Module 2

10 “The COUNTS”  Count(*) – counts records  Count(fieldname) – counts non–null occurrences of field name  Count (distinct fieldname) – counts distinct occurrences, but not supported in access Admissions 19 SQL200A Module 2 Grouping  Organizes results into summary rows, one per group  Groups can have sub groups which have sub groups and so on…. 20 Admissions SQL200A Module 2

11 GROUP BY in Query Designer Admissions 21 SQL200A Module 2 GROUP BY Problem Not an aggregate or group You will see this error a lot. Not to worry. It happens to everyone! 22 Admissions SQL200A Module 2

12 Group By Results Admissions 23 SQL200A Module 2 Having  Restricts the groups returned  Operates on the groups after they have been formed Having the count > 1 24 Admissions SQL200A Module 2

13 Having – SQL View  Restricts the groups returned  Operates on the groups after they have been formed Admissions 25 SQL200A Module 2 HAVING 26 Admissions SQL200A Module 2

14 HAVING Results Admissions 27 SQL200A Module 2 SQL200A Access SQL Query Design Part 2 – Joins, Subqueries 28 Admissions SQL200A Module 2

15 Database Design Diagram of the database for reference in joins Admissions 29 SQL200A Module 2 Joins  Used to combine columns from more than one table  Several types – Inner – Outer  Left  Right – Others (not covered)  Full Outer (Not supported in Access)  Cross  Self  Non equal 30 Admissions SQL200A Module 2

16 Inner Join  Pairs each row from first table with corresponding row from second table over the “join column” or “linking column”  The result only contains rows where there is a match over the join column in both tables  The default join in most databases Admissions 31 SQL200A Module 2 Inner Join Syntax Basic SQL 92 Syntax: SELECT column-list FROM table1 [AS alias] INNER JOIN table2 [AS alias] ON join-condition 32 Admissions SQL200A Module 2

17 Table Aliases  Shorthand name for a table  Used in more complex queries Table alias Select t.id, s.lname From _traveler as t Inner join xLU_Staff as s On t.staffid = s.staffid Admissions 33 SQL200A Module 2 Inner Join Basic SQL Example Basic Example: Add patient names to admissions data Two join tables Join condition 34 Admissions SQL200A Module 2

18 Inner Join Query Design Admissions 35 SQL200A Module 2 Inner Join Results 36 Admissions SQL200A Module 2

19 Inner Join over Multiple columns  Note that that the join condition can apply to multiple columns if desired  Used with composite keys Select …. From tablea as ta Inner join tableb as tb On ta.key1 = tb.key1 And ta.key2 = tb.key2 Admissions 37 SQL200A Module 2  Can join several tables in one select  Try to limit to three or four  Demonstration example Joining More than Two Tables 38 Admissions SQL200A Module 2

20 3 Table Query Results Admissions 39 SQL200A Module 2 More on Aliases  Can be set in Query Designer, too, by right clicking on a table  Useful for debugging top level queries as we shall see later 40 Admissions SQL200A Module 2

21 Outer Joins  Left – selects all rows from the left or first table, even if no match exists in the other table – Widely used in commercial practice, esp. for reporting  Right – same idea but all rows from right table  Full – all rows from both tables; not supported in Access Admissions 41 SQL200A Module 2 Left Outer Join Basic SQL 92 Syntax: SELECT column-list FROM table1 LEFT JOIN table2 ON join-condition 42 Admissions SQL200A Module 2

22 Left Outer Join (Right click on relationship to edit) Admissions 43 SQL200A Module 2 Left Outer Join  Modify your prior inner join to use a left join  Save as qryLeft_Admit  Now run both the inner and left joins  What is the difference? 44 Admissions SQL200A Module 2

23 Left Outer Join Results Admissions 45 SQL200A Module 2 Subqueries  One select statement embedded in another  Can be nested multiple levels deep  In Access query designer can be placed in criteria row or field row 46 Admissions SQL200A Module 2

24 Multi-valued Subquery  A type of subquery that compares to a list  Ex: find all diagnostic codes with no admissions  Commonly encountered in commercial practice Admissions 47 SQL200A Module 2 Multi-valued Subquery  Place subquery in criteria row 48 Admissions SQL200A Module 2

25 Multi-valued Subquery Result No one was ever admitted with these diagnoses. Admissions 49 SQL200A Module 2 Single-valued Subquery Find all admissions with stays greater than the average stay Subquery that returns a single value 50 Admissions SQL200A Module 2

26 Single-valued Subquery Subquery that returns a single value Admissions 51 SQL200A Module 2 Single-valued Subquery Result 52 Admissions SQL200A Module 2

27 Queries using Queries  Queries can be read just like a table  Some are updateable  Using a query as a source for another query very common in Access – especially for complicated queries  In a “back end” database such as Oracle or SQL Sever you would use a View Admissions 53 SQL200A Module 2 Queries using Queries Filter the prior query ( a little on the complicated side) for the year 2001 54 Admissions SQL200A Module 2

28 Queries using Queries Result Admissions 55 SQL200A Module 2 SQL200A Access Query Design Part 3 – Unions, Action Queries 56 Admissions SQL200A Module 2

29 Special Queries  Most are “Action Queries”  Reached through Query pull-down menu Admissions 57 SQL200A Module 2 Special Queries  Special action queries have distinct symbols 58 Admissions SQL200A Module 2

30 Admissions Database Diagram of the database for reference in part 3 Admissions 59 SQL200A Module 2 Data Modification Queries SQL Action Query INSERT Append UPDATE Update DELETE Delete SELECT…INTO Maketable 60 Admissions SQL200A Module 2

31 Creating Action Queries  First set up the select query based on the table you are selecting from  Test select query  Then convert to action query  When you run an action query there is no result displayed – must look at the affected table to see the result Admissions 61 SQL200A Module 2 Select Into…Maketable  Creates a new table “on the fly” 62 Admissions SQL200A Module 2

32 Select Into…Maketable New table Existing table Admissions 63 SQL200A Module 2 Archived Labor History 64 Admissions SQL200A Module 2

33 Delete  Deletes one or more rows Basic Syntax: DELETE FROM table-name WHERE filter-criteria Admissions 65 SQL200A Module 2 Delete Example: Delete all archived admissions_history newer than 12/31/01 66 Admissions SQL200A Module 2

34 Delete in Query Designer Example: Delete all archived records newer than 12/31/2001 Admissions 67 SQL200A Module 2 Archive after DELETE 68 Admissions SQL200A Module 2

35 More complex DELETE with FROM clause  Can delete based on matching records in other tables  Uses from for criteria instead of where – uses the matched rows as an implicit filter condition  Covered in advanced class Admissions 69 SQL200A Module 2 Insert – Append Query  Adds new rows to an existing table  Two forms: – Single Row – Multi-Row 70 Admissions SQL200A Module 2

36 Single Row Insert Basic Syntax: Insert [into] table-name Values (value-list) Admissions 71 SQL200A Module 2 Single Row Insert Basic Example: INSERT into xLU_BuildStyle values(6, “Thick”) Not really used this way in MS Access. Typical MS Access use would be Insert…Select as shown in following slides. 72 Admissions SQL200A Module 2

37 Multi-row Insert Basic Syntax: INSERT [INTO] table-name SELECT select-statement Admissions 73 SQL200A Module 2 Append Query (Multi-row insert) 74 Admissions SQL200A Module 2

38 Multi-row Insert (Append) Add back archived admissions_history newer than 12/31/01 Admissions 75 SQL200A Module 2 Multi-Row Insert 76 Admissions SQL200A Module 2

39 Insert (Append) Results Admissions 77 SQL200A Module 2 Update  Updates fields in an existing row Basic Syntax: UPDATE table-name SET field1 = new value, field2 = new value,… WHERE selection-criteria 78 Admissions SQL200A Module 2

40 Update  Increase the admission_history copays by 10% (unrealistic to change history but we don’t want to step on our good tables) Admissions 79 SQL200A Module 2 Update in Query Designer New value 80 Admissions SQL200A Module 2

41 Update Results New values Remember: you have to look at the affected table. No “result” is displayed. Admissions 81 SQL200A Module 2 Unions  Combines the results of two queries – Ex: current records and history  Tables must be union compatible (at least in theory!)  There is no designer in Access; must be done in SQL view 82 Admissions SQL200A Module 2

42 Union Syntax SELECT column-list FROM table1 UNION [ALL] SELECT same-columns FROM table2 Admissions 83 SQL200A Module 2 Union Example 84 Admissions SQL200A Module 2

43 Union Result Admissions 85 SQL200A Module 2 If you want to know more  This is the last class in our standard offerings  However, we are happy to provide custom classes or tutoring specifically for your needs  You can also outsource some or all of your Microsoft Access project work to us 86 Admissions SQL200A Module 2

44 End Session 2 End of Class! Thank you for your time. Admissions 87 SQL200A Module 2 Notes 88 Admissions SQL200A Module 2

Orange Coast Database Associates Course (800)355-9855 or http://www.d2associates.com Accelerated Computer Training Orange Coast Database Associates Specializing in Microsoft Office, Access, SQL, and related technologies Computer Training, Programming & Consulting 32422 Alipaz St., Suite A-15 San Juan Capistrano, CA (800)355-9855 (Toll Free) | (949)489-1472 (Direct) | (949)485-6284 (Fax) http://www.d2associates.com | sales@dhdursoassociates.com Accelerated Computer Training for Working Professionals

SQL200A Microsoft Access SQL Design

More Related Content

What's hot

Viewers also liked

Similar to SQL200A Microsoft Access SQL Design

More from Dan D'Urso

Recently uploaded

SQL200A Microsoft Access SQL Design