Table Relationships ACCESS Queries

1. Table RelationshipsACCESS Queries Chapters 4 & 8

2. Study Objectives • Understanding table relationships • Understanding integrity rules • Adding keys to tables • Understanding queries • Creating queries • Working with query field • Adding Criteria to queries • Creating multi-table queries Michael alexander, dick kusleika: access 2016 bible

3. One-to-one relationships • Each record of one side is associated with one and only one record on the other side, in both directions • Most of the time when one-to-one relationships exist, the data ends up stored in a single table • E.g.: Canadian Citizen and Canadian Passport are in a one-to-one relationship; you can store the passport related info in the Canadian Citizen table • In some cases, separate tables might be preferable: • Info like passport data, security codes, passwords, etc. are confidential, and as such, even though they may belong to one entity, it may make sense to store it separately in order to apply encryption, restricted access, etc. In general, if different subsets of fields in a table need to be shared differently with different groups, one may split the data into more tables • Another example would be subtyping. If you have two kinds of businesses you deal with, say Customers and Vendors, the two entities may share a lot of information, and also have some specific fields, which are different between Customers and Vendors. In this case you may have three tables: one for shared data (tblBusiness) one for data specific to customers (tblCustomers) and one for data specific for Vendors (tblVendors). Michael alexander, dick kusleika: access 2016 bible

4. One-to-many relationships • Each record of one side is associated with one or more records on the other side; E.g.: • A Customer (called the “one side”) places many Orders (called the many-side”), but an Order has exactly one Customer • For a given class, a Teacher (“one side”) has many Students (“many-side”), but Students have exactly one Teacher • An Employee (“one side”) has many Paychecks (“many side”), but a Paycheck belongs to a single Employee • One-to-many relationships are implemented by: • Ensuring the entity on the “one side” has a primary key • Including a field in the table of the “many side” that contains a value of the Primary Key • This field is called a Foreign Key • A Foreign Key has to be the same type and size of data as the Primary Key • Once you select a given record on the “one side”, you find the related records on the “many side” by looking in the many table for records that store in the Foreign Key field the value present in the Primary Key field of the “one side” Michael alexander, dick kusleika: access 2016 bible

5. Many-to-many relationships • Each record of each side is associated with one or more records on the other side; E.g.: • A Doctor has many Patients and an Patient has many Doctors • A Magazine has many Subscribers and a Subscriber has many Magazines • Many-to-many relationships are implemented by introducing a third (join) table with two or three fields: • A Foreign Key for the first side of the relationship • A Foreign Key for the second side of the relationship • A primary key for the association (usually present although not mandatory) • The presence of a Doctor’s Primary Key and a Patient’s Primary Key in one of the records of the join table (tblDoctorPatient) indicates the specific doctor and the specific patient are part of the relationship Michael alexander, dick kusleika: access 2016 bible

6. Relationships Example Michael alexander, dick kusleika: access 2016 bible

7. Referential Integrity • When relationships exist between tables, it is important that certain rules be followed for the data to make sense • In a payroll application, any check must refer to an employee; it is essential that such an employee exists (as in the employee id referenced in the check record must exist in the employee table) • You cannot delete an employee record while check records exist referring to that employee • If for some reason the id of an employee changes, all checks issued to that employee have to also change to use the new id • Referential integrity rules ensure that operations that would result in corrupted or meaningless data cannot occur Michael alexander, dick kusleika: access 2016 bible

8. Referential Integrity Rules • Referential integrity operates based on key fields • RI Rule #1: No Primary Key can contain a null value (i.e.: you must provide a unique value for the Primary Key of each record • RI Rule #2: All Foreign Key values must be matched by a corresponding Primary Key value • More specifically: • Rows cannot be added to the “many side” table if a corresponding record does not exist in the “one side” table • The Primary Key value on the “one side” cannot be changed if the “many side” already contains records containing that value; • A record on the “one side” cannot be deleted if the “many side” already contains records containing the Primary Key value of that record • Benefits of Primary Keys: • Primary Keys are always indexed, so searches in keyed tables are faster • Access forces you to enter a unique value in a Primary Key field, ensuring integrity Michael alexander, dick kusleika: access 2016 bible

9. Natural Keys vs. Surrogate Keys • Natural keys are fields with a logical meaning outside of the DB system • E.g.: SIN, Passport Number, etc. • Surrogate keys are unique values that do not mean anything outside the DB system • E.g.: AutoNumber • When natural keys exists, it is usually a good idea to use them, but there are many situations when it may be difficult to do so: • The value is not universal (some people do not have a SIN) • The value may not be known at the time the record must be created (recall a key value MUST be provided when the record is created) • Legal and Privacy issues • E.g.: some countries limit or regulate the use of PII (Personally Identifiable Information) Michael alexander, dick kusleika: access 2016 bible

10. Single-field vs. Composite Keys • A Single-field PK is a PK consisting of a single field in the table • Sometimes a single field is not a good natural PK, but adding a second or third field might do • First and Last name rarely make a good PK, but adding a third field like Date of Birth can help • Considerations with composite PK • None of the fields in the composite key can be null • Each of the fields in the composite key can have duplicates elsewhere in the table, but in aggregate the combined value must be unique • Often this uniqueness is difficult to achieve • Disadvantages of composite keys: • Increases the number of fields in other tables (on the “many side”) • Required even more values to be known upfront at the time of creating a record Michael alexander, dick kusleika: access 2016 bible

11. Creating Relationships in Access • Select Database Tools tab and click the Relationships tool • You will see the list of tables to the right • Click Add to add tables to the relationship window • Drag the Primary Key from the “one side” to the Foreign Key of the “many side” to open the Edit Relationship window below • Select the Integrity Rules you want • Click Create Michael alexander, dick kusleika: access 2016 bible

12. Cascading • Choosing the Cascade Update Related Fields option • If you check this option, changing a Primary Key value would change all the values of the corresponding Foreign Keys in all the related tables • Generally, there are few valid reasons to ever change the value of a Primary Key • Changing the value of a Primary Key can create confusing situations (changing history) • Best not to change keys, hence no need to cascade updates • Choosing the Cascade Delete Related Records option • If you check this option, deleting a record on the “one side” will delete all related records on the “many side” • This can be useful, when the design of your DB system calls for deleting old records (as opposed to archiving them) • This can also be dangerous and/or cause misleading data (e.g.: deleting a customer causes deletion of all sales to that customer, causing the system to provide understated sales volumes) Michael alexander, dick kusleika: access 2016 bible

13. What is a Query • The term query is derived from the Latin word “quaerere” meaning to ask or inquire • A Query is a way to extract and structure information from a DB • Queries transform data into information • Why do we need queries? • Data in tables in not in any particularly useful order, and even if it were, different uses benefit from different orders • Data might be spread in different tables due to normalization and consistency, but needed together for a business purpose • How do queries present data? • Datasheet • Form • Report Michael alexander, dick kusleika: access 2016 bible

14. What Can Queries Do? • Chose one table, or combine data from several tables • Chose specific fields needed for a purpose, while omitting fields that are not needed • Select only certain records in the table(s) based on specific criteria • Sort the selected records in some specific order • Perform calculations (over and above calculated fields in a table) • E.g.: averages, totals, counts, etc. • Create new tables based on their output • Feed their output to a form or report • Use the output of another query as a data source for a new query • Modify data in a table based on their results (these queries are called action queries) Michael alexander, dick kusleika: access 2016 bible

15. How Queries Work • The data output of a query is called a recordset • By default, recordsets are displayed in the datasheet view • You cannot tell a table from a recordset just by looking at the output • Recordsets are dynamic • Only the defining structure of the query is stored in the query object • The actual data is collected anew every time the query runs • So if you run a query, then change data in tables and run the same query again, your table changes will be reflected in the recordset • When the recordset is bound to a form or report, the query is re-run every time the form or report is opened • Recordsets can be also used in macros and VBA procedures (out of scope) Michael alexander, dick kusleika: access 2016 bible

16. Types of Queries • In scope for this course • Select Queries • Basic type of queries, used to generate a recordset (as a subset of the data in the DB) for a specific purpose • Does not cause any change in the underlying data • Parameter Query • This query is like a select query, but it includes a dialogue box for entering a parameter that can modify the selection criteria of the query • Very useful for writing a single query to select similar but different sets of data • Not in scope for this course • Action Query • This type of query execute an action with the generated recordset(deletes them, appends them to an existing table, creates a new table, or updates existing records) • Aggregate Query • This query takes the recordset of another query and does a variety of calculations on it (averages, totals, sums, counts, min, max, etc.) Michael alexander, dick kusleika: access 2016 bible

17. Creating a Query • The query object is essentially a program-like structure written in a specialized language • SQL (Standard Query Language) is the universal query language used in all databases • In Access we have: • A tool called Query Designer • It is a visual tool, guiding you step by step through the decisions needed for making a query • Provides a set of specialized tools in the ribbon • Allows later modifications of the query • Allows you to view the results as you design the query • Generates the SQL (query object) required • A tool called Query Wizard • It takes you through a succession of questions using predefined models • Generates the SQL (query object) required Michael alexander, dick kusleika: access 2016 bible

18. Query Design View • Go to the Create tab and click the Query Design tool icon to get to the design view to the right • Show Table dialogue allows you to select the tables needed for the query • Once selected, a box with all the fields in the selected table appears in the upper pane (just like with the relationships) • Property Sheet allows you to set query specific properties • The grid at the bottom is called QBE (Query By Example) and will be filled with fields and criteria Michael alexander, dick kusleika: access 2016 bible

19. The QBE Rows • Field • Shows the name of every field selected (you select fields by double clicking their name in the pane above, or by dragging them from the pane above to an empty column in the QBE grid) • Table • Shows the table the field belongs to and is filled automatically when you select a field • Useful when multiple tables are involved in a query • Sort • Drop down with three values: Ascending, Descending and (not sorted) with the obvious meanings • Show • When checked, this data shows in the view of the recordset; otherwise this field is hidden • Criteria • Here is where we specify criteria for selecting the records (more on that later) • Or • This and subsequent rows allow composite criteria to be created Michael alexander, dick kusleika: access 2016 bible

20. The Query Design Ribbon • Results Section • View tool switches between Datasheet, design and SQL views • Run allows you to test run the query • Query Type Section • We won’t use this, because parameter queries are a subtype of select queries, and the rest are out of scope • Query Setup Section • Show Table opens the table list dialogue when you want to add more tables • Insert/Delete Row inserts or deletes a row in the criteria section of the QBE • Insert/Delete Column inserts or deletes a column in the QBE • Builder is a wizard to help build expressions for the criteria fields • Return is used to display only a limited portion of the recordset • Show/Hide Section • Totals tool inserts a row labelled Totals in the QBE and allows you to select a calculation on any field in the query (of course, it must be a field type consistent with the requested calculation) • Parameters tool opens up a dialogue to allow setting data type limitations on the parameters (if any) of the query • Property Sheet tool toggles on and off the window showing the query properties • Table Names tool toggles on and off the Table row in the QBU (useful when you are designing a single table query) Michael alexander, dick kusleika: access 2016 bible

21. Adding Fields to a Query • Add them one by one by: • Double clicking the field name in the table view above the QBE • Dragging a field name from the table view above the QBE to an empty column in the QBE • Adding multiple fields at once by: • Hold the Ctrl key while clicking on successive field names in the table view above the QBE • Release the Ctrl key and drag all the fields to the QBE • Add all the fields in a table by: • Double clicking the * symbol at the top of the table view above the QBE Michael alexander, dick kusleika: access 2016 bible

22. Working with Fields in the QBE • Select a field by clicking in any place in its column, or the field selector (the grey box above the field’s name in the QBE) • Reorder fields by dragging the field selector to a new location • Resize the column of a field by positioning the cursor on the vertical line between field columns until the cursor becomes a double arrow and drag the separating line to where you want it • Remove a field by: • Selecting the field and clicking the Delete Column icon • Right clicking in the field selector and selecting Cut • Insert a field by: • Clicking in an existing field, and clicking the Insert Column tool (empty column inserted at the left of the existing field, then you drag a field to that column) • Drag a field name over a column already in use; the new field is inserted before the field you dropped it on Michael alexander, dick kusleika: access 2016 bible

23. Criteria for Text Fields • Record selection criteria for text fields fall into three kinds: • Equality, where records selected contain in that field the text specified in the Criteria row • E.g.: Entering the text “car” in the criteria for a Category field of a Product table will select only the cars from that table • Inequality, where records selected contain in that field any text but the text specified in the Criteria row • E.g.: Entering the text “not car” in the criteria for a Category field of a Product table will select only the records for items that are not cars from that table • List of values, which works like equality, but with multiple values • Each subsequent value is entered in a a new row in the or section of the QBE, or • Use In(“a”, “b”, “c”) • You can use Like and Not Like with strings containing the * symbol • * is called a wild card, and it “matches” any string • Like *ina selects any record that has a text value ending in “ina” • Like [A-D]* selects any record that has a text value starting with the letter A, B, C, D • You can use < or > before a string, to select records containing text lexically “less” or “more” then the following string Michael alexander, dick kusleika: access 2016 bible

24. Criteria for other fields • Criteria for Number, Currency and AutoNumber fields • Pretty much the same as for text fields, except that strings here must be numbers • Criteria for Date/Time fields • Pretty much like numbers, but the date must be in the #mm/dd/yyyy# format • You can use the Date() function which return today’s date • E.g.: Between Date() and Date()-6 returns all records dated in the last week • You can use Year(), Month() and Day() with any date field as an argument to select based on that respective portion of a date • E.g.: Year([SalesDate]) = Year(Date()) selects records with the date in the current year • Use Is Null to find all records that have no value in a field Michael alexander, dick kusleika: access 2016 bible

25. Multi-table queries • A query can contain data from multiple fields, provided the tables are related • When you select multiple related tables, the “join line” shows how the tables are related • See example in image below • Select the fields you want as for a single table query, specify criteria, etc. as usual • Access will use the relationship to decide which records from each table are selected Michael alexander, dick kusleika: access 2016 bible

26. Using the Query Wizard • For a few basic kinds of queries you can use the Query Wizard • Select the Create tab and click the Query Wizard icon to get to the screen on the top right • Click OK to get to the screen at the bottom right • Select the tables you want, successively, using the Tables/Queries dropdown • For each table select the fields you want by clicking on a filed in the Available Fields box and then on the right arrow in the middle Michael alexander, dick kusleika: access 2016 bible

27. Using the Query Wizard – cont’d • … • Click Next when you are done, to get to the screen at the right • Enter the name of your query at the top • Decide if you want to run the query first or go straight to modifying the design • Note that you did not have an opportunity to put in criteria, sorting, show, etc. so in most cases further design work is needed • Click Finish to complete the process • While it can be a modestly faster way to put together a query, since most of the time additional design is needed the wizard is not all that useful Michael alexander, dick kusleika: access 2016 bible

28. Parameter Queries • Sometimes we need to design queries that are very similar • E.g.: A sales database selling across Canada may want to determine at different times how many customers they have in a given province • Sometimes that would be Ontario, some other times Quebec, etc. • Dilemma: Do I need to design a query for every province? • Solution: design a parameter query • To transform a query into a parameter query you do this: • In the Criteria for the field in question (in the example above the Province field) enter a text in square brackets containing the prompt to the user indicating what to provide as a parameter • E.g.: [Enter province name: ] • Now when you run the query, a pop up shows the text you entered and provides a space for the user to enter a value, say Ontario • The effect is the same as if you entered Ontario in the criteria field instead of the prompt • Now the query can be used to find customers in any one province Michael alexander, dick kusleika: access 2016 bible