Relationships, Queries & Criteria

[Audio] Relationships, Queries & Criteria Disclaimer. This was done under the assumption that everyone: 1. Understands datatypes 2. Has completed creating their database and populating the relevant tables 3. Read the first PowerPoint on Access.

[Audio] Relationships A relationship, in the context of databases, is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationships allow relational databases to split and store data in different tables, while linking disparate data items. In other instances, a relationship can exist between fields in two tables which have the data whether they one is a primary key. A relationship is O-N-L-Y possible if each field is of the same data type and the data is of the same length. Consider the following database, Trinbarjam Elections, that has been normalized. The database has 3 tables: Candidate Details, Constituency & Elections..

CANDIDATE DETAILS Candidate Name • Votes Received Party • Tami Chin 2487 DAP Nikki Bendova 1066 WNA Kitty Hero 1421 UPM Mumma Yammy 2132 PDR Bimma Slimmaz 1848 DAP Rachel Ray 924 WNA Ray Wray 2156 UPM Bare Almshouse 1232 PDR Joe Johncrow 2832 DAP John Mine 1214 WNA Joe Ghrine 1618 UPM Shelly Dashout 2428 PDR Ding Dong 1023 DAP Ravers Clavers 1364 WNA Dozen Anone 2046 UPM Fatima Pusey 1023 PDR Gyal Clown 802 DAP Village Bicycle 1871 WNA Batta Ears 1069 UPM Waste Man 1604 PDR Cane Bush 1479 DAP Baby Madda 2219 WNA Dutty Man 1109 UPM Fluffy Buffy 2589 PDR Betweenie Man 1024 DAP Bounty Chilla 2389 WNA Chip Skull 1365 UPM Changa Banga 2048 PDR Address 1 First Street 2 Second Street 3 Third Street 4 Fourth Street 5 Fifth Street 6 Sixth Street 7 Seventh Street 8 Eighth Street 2 First Street 1 Second Street 2 Third Street 2 First Street 3 Second Street 4 Third Street 5 Fourth Street 6 Fifth Street 7 Sixth Street 8 Seventh Street 9 Eighth Street 3 First Street 4 Second Street 5 Third Street 9 First Street 8 Second Street 7 Third Street 6 Fourth Street 5 Fifth Street 4 Sixth Street Gendel DOB 1/1/1981 F 2/2/1982 F 3/3/1983 M 4/4/1984 5/5/1985 F 6/6/1986 7/7/1987 M 8/8/1988 9/9/1989 M 1/10/1990 2/11/1991 M 3/12/1992 F 2/14/1987 M 11/9/1993 M 6/19/1983 M 10/5/1980 F 12/24/1990 12/12/1989 F 6/15/1988 F 11/9/1991 M 2/11/1991 F 3/12/1992 F 2/14/1987 M 11/9/1993 F 6/19/1983 M 10/5/1980 M 12/24/1990 M 12/12/1989 M Profession Singer Dancer Mechanic Farmer Driver Bartender Businessman Doctor Banker Businessman Businessman Vendor Dancer Driver Farmer Model Entertainer Trainer Saleswoman Salesman Hairdresser Taxi Operator Bus Driver Model Musician Musician Mortician Herbalist Constituenc 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 7 7 7 7.

[Audio] Having seen the data, we should have a pretty good idea of what fields/data reside in multiple tables. To facilitate relationships, we need to be able to identify which tables are tables of origin for pieces of data. For example, the table of origin for Constituency Number, would be the Constituency Table..

[Audio] To create Relationships, one would have to go to the Database Tools tab, and the Relationships Group and hit the Relationships button. The following pop-up will appear. Double click on a table name to add each table to the relationship panel..

[Audio] We can now see which fields are common. Be reminded though, even though fields may contain the same data, they may have different field names. To create a relationship between fields, simply click and drag a field from its table of origin and drop it on the corresponding/related field in the other table. A pop up will appear..

[Audio] Note, that the Table names and Field names of both tables in the proposed relationship are displayed. Enforce Referential Integrity: Referential integrity (R-I---) is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree (data type & length) with the primary key that is referenced by the foreign key. Cascade Update Related Fields: When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key. Cascade Delete Related Records: When you enforce referential integrity and choose the Cascade Delete Related Records option, and you then delete a record on the primary key side of the relationship, Access automatically deletes all records that reference the primary key..

[Audio] Select all three options, then hit Create. A relationship is now created. A 1-to-1 relationship is created. There are three types of relationships: One-to-Many relationship An order tracking database that includes a Customers table and an Orders table as an example. A customer can place any number of orders. It follows that for any customer represented in the Customers table, there might be many orders represented in the Orders table. The relationship between the Customers table and the Orders table is a one-to-many relationship. Many-to-Many relationship A Products table and an Orders table. A single order can include more than one product. On the other hand, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. In addition, for each record in the Products table, there can be many records in the Orders table. This relationship is called a many-to-many relationship. Note that to detect existing many-to-many relationships between your tables, it is important that you consider both sides of the relationship One-to-One relationship In a one-to-one relationship, each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table. A one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. When you do identify such a relationship, both tables must share a common field. Create relationships between the tables in the Trinbarjam Elections Database..

[Audio] Queries When you want to review, add, change, or delete data from your database consider using a query. Using a query, you can answer very specific questions about your data that would be difficult to answer by looking at table data directly. You can use queries to filter your data, to perform calculations with your data, and to summarize your data. You can also use queries to automate many data management tasks and to review changes in your data before you commit to those changes. A query is a request for data results, for action on data, or for both. You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data. Queries that you use to retrieve data from a table or to make calculations are called select queries. Queries that add, change, or delete data are called action queries. You can also use a query to supply data for a form or report. In a well-designed database, the data that you want to present by using a form or report is often located in several different tables. By using a query, you can assemble the data that you want to use before you design your form or report..

[Audio] To create a query, go to the Create tab, ten to the Queries Group. Hit Query Design. The Show Table Dialog comes up. Here you select the tables you wish your query to get data from. Select Candidate Details by double clicking or by selecting then pressing Add..

[Audio] This query is to show all Candidates from the W-N-A Party Double click a field name in a Table to add it to your query. Having done that, look at the top left corner of the Ribbon in the Results group. Press View or Run to execute a query. A query has T-W-O distinct Views. Design View and Datasheet View..

[Audio] The results are displayed, however this query shows ALL CANDIDATES of A-L-L parties. To refine a query, one needs to be able to differentiate what is required from everything else. We require O-N-L-Y candidates from the W-N-A party, and as such the criteria that differentiates the candidates we want from the others in this case is the Party. In the top left of the Ribbon, Press Views to return to the Design View of the Query.

[Audio] In the Design section of the query there is a CRITERIA row, where query criteria is placed. Under the Party Column, in the criteria cell, type W-N-A--. Criteria need to be written exactly as the appear in the database. Access will often ForMAT the criteria by putting in double quotes or #s where appropriate. Run the query..

[Audio] There are seven candidates from the W-N-A Party. A query can be saved by simply right clicking on the Query Tab that says Query 1 in the Query Design Window, and selecting Save. A pop-up will Appear..

[Audio] OBVIOUSLY you will select Yes. Notice now in the Object Pane, a N-E-W heading, Q-U-E-R-Y appears with the created Query..

[Audio] Criteria vary according to the data types and requirement of the query. Types & use of some criteria are listed below..