Table Relationships ACCESS Queries CHAPTERS 4 & 8 1 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 2
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 3
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
4 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 Doctors Primary Key and a Patients 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 5
Relationships Example MICHAEL ALEXANDER, DICK KUSLEIKA: ACCESS 2016 BIBLE 6 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 7 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 8 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 9 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 10 Creating Relationships in Access 1. Select Database Tools tab and click the Relationships tool 2. You will see the list of tables to the right 3. Click Add to add tables to the relationship window 4.
Drag the Primary Key from the one side to the Foreign Key of the many side to open the Edit Relationship window below 5. Select the Integrity Rules you want 6. Click Create MICHAEL ALEXANDER, DICK KUSLEIKA: ACCESS 2016 BIBLE 11 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 12 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 13 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 14 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 15 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 16 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 17
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
18 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 19 The Query Design Ribbon Results Section Show/Hide Section View tool switches between Datasheet, design and SQL views Run allows you to test run the query Query Type Section We wont 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 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) 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
MICHAEL ALEXANDER, DICK KUSLEIKA: ACCESS 2016 BIBLE 20 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 21
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 fields 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 22
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 23 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 todays 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 24 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 25 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 26 Using the Query Wizard contd 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 27 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 28
review for the unit 1 test A government is the institution through which a state (a country or nation) makes and enforces laws that run the state A "state" is a body of people living in a defined territory; they...
Mesure de vitesses: La comparaison entre la fréquence de l'onde perçue et celle de l'onde émise permet de déterminer la vitesse de l'émetteur par rapport au récepteur par exemple. Astronomie : effet Doppler-Fizeau
Natural Environments: The Atmosphere GG 101 - Spring 2005 Boston University Natural Environments: The Atmosphere GG 101 - Spring 2005 Boston University Myneni Jan-19-05 Course Info (2 of 6) Three main topical areas (1) basic astronomical relationship between the Earth...
Who's/Whose) is going to be Benjamin Raoul Lopez for the play? Use this, that, these, and those.1. laundry (far) Identify the pronouns and whether they are Intensive or Reflexive1.My mom just got herself into another argument about house rules. ......
Buyers expect to pay even-numbered prices for prestigious items and odd-numbered prices for commonly available goods. The greater the number of customer benefits the seller can convey about a product, the less will be the price resistance.
Mr Green sees the shorter, straight, green path and Mr. Red sees the longer, curved, red path. In an accelerated frame, time runs slow compared to a non-accelerated frame. The Equivalence Principle tells us that there is no difference between...
E2 Tech Forum November 15, 2011 Andrew Wilson, Executive Director About NECEF * New England Clean Energy Foundation (NECEF) Mission: Accelerate clean energy innovation in and enhance the development of New England's clean energy business cluster NECEF provides organizational leadership...
Ready to download the document? Go ahead and hit continue!