[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. 

Scene 1

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.. 

Scene 2

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 ONLY 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. 

Scene 3

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.. 

Scene 4

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.

ELECTIONS
Constituency Number
Percentage Voter Turnout Click to Add
$0.75
$0.85
$0.90
$0.80
$0.75
$0.88
$0.82

CONSTITUENCY
Constituenc
EAST CENTRAL
NORTH
NORTH WEST
SOUTH
SOUTH WEST
WEST
Constituency Number •
2
3
4
5
6
7
Number of Voters Click to Add
7511
10789
9093
6290
8532

[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.. 

Scene 5

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.

FILE
HOME
CREATE
GlobalDistributors : Database- C:\Users\GAT\Downloads\GlobalDistributors.accdb (Access
EXTERNAL DATA
DATABASE TOOLS
Compact and
Repair Database
Tools
Visual Run
Basic Macro
Macro
Relationshis Object
Dependencies
Relationships
Database Documenter
Analyze Performance
Analyze Table
Analyze
Access SharePoint
Database
Move Data
Add-
Add-Ins

Show Table
Tables Queries Both
CONSTITUENCY
ELECTIONS

[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.. 

Scene 6

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.

Relationships
CANDIDATE DETAILS
Candidate Name
Votes Received
Party
Address
DOB
Gender
Profession
Constituency Number
CONSTITUENCY
Constituency Name
Constituency Number
Number of Voters
ELECTIONS
'8 Constituency Number
Percentage Voter Turnout

Edit Relationships
Table/Query:
ELECTIONS
Related Table/Query:
CANDIDATE DETAILS •
Constituencyö • Constituency Nur
@Enforce Referential Integrity
@Cascade update Related Fields
@Cascade Delete Related Records
Relationship Type: One-To-Many
Create New..

[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.. 

Scene 7

Note, that the Table names and Field names of both tables in the proposed relationship are displayed. Enforce Referential Integrity: Referential integrity (RI) 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.

Edit Relationships
Table/Query•.
ELECTIONS
Constituency
ce e ren
Related Table/Query:
CANDIDATE DETAILS
Constituency Nur
grl
Create New..
@Cascade Llpdate Related Fields
@Cascade Delete Related Records
Relationship Type: One-TQ-Many

[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.. 

Scene 8

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.

Relationships
CANDIDATE DETAILS
Candidate Name
Votes Received
Party
Address
DOB
Gender
Profession
Constituency Number
1
ELECTIONS
Constituency Number
Percentage Voter Turnout

[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.. 

Scene 9

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.. 

Scene 10

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.

GlobalDistributors : Database- (Access 2007 - 2013 file format) - Access (Pr
FILE
Application
Parts
Templates
HOME
Table
CREATE
EXTERNAL DATA
DATABASE TOOLS
Form Wizard
Navigation •
Form Form Blank
Report
Design Form More Forms
Forms
Cl Module
Table SharePoint Query Query
Design Lists
Tables
Wizard Design
Queries
Report
Design
[9 Labels
Blank
Report
Reports
Class Module
Macro
Visual Basic
Macros & Code

Queryl
Show Table
Tables Queries Both
CONSTITUENCY
ELECTIONS
Field:
Table:
Sort:
Show:
Criteria:

[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.. 

Scene 11

This query is to show all Candidates from the WNA 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 TWO distinct Views. Design View and Datasheet View.

DATABASE TOOLS
QUERY TOOLS
DESIGN
GlobalDistributors : Database- (ACQ
FILE
View
HOME
CREATE
EXTERNAL DATA
Insert Rows
Insert Columns
Delete Rows Delete Columns
Run
Select Make Append Update Crosstab
Table
Query Type
(D Union
pass-Through
Delete
Data Definition
Show
Table
Totals
Builder
Return: All
Query Setup
Property Sheet
Parameters
Show/Hide

Query2
Field:
Table:
Sort:
Show:
Criteria:
CANDIDATE DETAILS
Candidate Name
Votes Received
Party
Address
DOB
Gender
Profession
Constituency Number
Candidate Name
CANDIDATE DETAILS
Votes Received
CANDIDATE DETAILS
Party
CANDIDATE DETAILS
DOB
CANDIDATE DETAILS
Gender
CANDIDATE DETAILS
Profession
CANDIDATE DETAILS
CANDIDATE DETAILS

[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. 

Scene 12

The results are displayed, however this query shows ALL CANDIDATES of ALL PARTIES. To refine a query, one needs to be able to differentiate what is required from everything else. We require ONLY candidates from the WNA 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

TRINBARJAM ELECTIONS COMMISSION : Database- ELECTIONS COMMISSION.accdb (Access 200
FILE
HOME
CREATE
EXTERNAL DATA DATABASE TOOLS
Compact and
Repair Database
Tools
Visual Run
Basic Macro
Macro
Relationships Object
Dependencies
Relationships
Candidate Name •
Mumma Yammy
Bimma Slimmaz
Rachel Ray
Ray Wray
Bare Almshouse
Joe Johncrow
John Mine
Joe Ghrine
Shelly Dashout
Ding Dong
Ravers Clavers
Dozen Anone
Fatima Pusey
Gyal Clown
Village Bicycle
Batta Ears
Waste Man
Cane Bush
Baby Madda
Dutty Man
Fluffy Buffy
Betweenie Man
Bounty Chilla
Chip Skull
Changa Banga
Database Documenter
Analyze Performance
Analyze Table
Analyze
Votes Recei' • Party
2132 PDR
1848 DAP
924
WNA
2156 UPM
1232 PDR
2832 DAP
1214 WNA
1618 UPM
2428 PDR
1023 DAP
1364 WNA
2046 UPM
1023 PDR
802 DAP
1871 WNA
1069 UPM
1604 PDR
1479 DAP
2219 WNA
1109 UPM
2589 PDR
1024
2389 WNA
1365 UPM
2048 PDR
Access SharePoint
Database
Move Data
Address
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
Add-
Add-Ins
• Gendel •
All Access Obje... @
Tables
CANDIDATE DETAILS
CONSTITUENCY
ELECTIONS
Profession
• Constituenc •
Farmer
1
Driver
2
Bartender
2
Businessman
2
Doctor
2
Banker
3
Businessman
3
Businessman
3
Vendor
3
Dancer
4
Driver
4
Farmer
4
Model
4
Entertainer
5
Trainer
5
Saleswoman
5
Salesman
5
Hairdresser
6
Taxi Operator
6
Bus Driver
6
Model
6
7
Musician
Musician
7
Mortician
7
Herbalist
7

[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.. 

Scene 13

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 WNA. 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.

QUERY TOOLS
TRINBAR.JAM ELECTIONS COMMISSION : Database- ELECTIONS C
FILE
Run
Results
HOME
CREATE EXTERNAL DATA
DATABASE TOOLS
Select Make Append Update Crosstab Delete
Table
Query Type
CD Union
Pass-Through
Data Definition
DESIGN
Show
Table
et:: Insert Rows
Delete Rows
Builder
un Insert Columns
Delete Columns
Return: All
Query Setup
E Property Sheet
Table Names
Totals Parameters
Show/Hide
All Access Obje... @ <
Tables
CANDIDATE DETAILS
CONSTTTUENCY
ELECTIONS
CANDIDATE DETAILS
Candidate Name
Votes Received
Party
Address
DOB
Gender
Profession
Constituen
Candidate Name
CANDIDATE DETAILS
Field:
Table:
Sort:
Criteria:
Votes Received
CANDIDATE DETAILS
Party
CANDIDATE DETAILS
Address
CANDIDATE DETAILS
Gender
CANDIDATE DETAILS
Profession
CANDIDATE DETAILS
Constituency Number
CANDIDATE DETAILS

[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.. 

Scene 14

There are seven candidates from the WNA 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.

Queryl
Candidate Name
John Mine
Village Bicycle
Nikki Bendova
Bounty Chilla
Ravers Clavers
Baby Madda
Rachel Ray
Votes Recei' • Party •
1214 WNA
1871 WNA
1066 WNA
2389 WNA
1364 WNA
2219 WNA
924 WNA
Address
I Second Street
8 Seventh Street
2 Second Street
6 Fourth Street
4 Third Street
5 Third Street
6 Sixth Street
Gendel
Profession • Constituenc
Businessman
Trainer
Dancer
Musician
Driver
Taxi Operator
Bartender
3
5
1
7
4
6
2

TRINBARJAM ELECTIONS COMMISSION : Database- ELECTIONS COMMISSION.accdb (Access 200
HOME
CREATE EXTERNAL DATA
DATABASE TOOLS
FILE
View
Views
Cut
Copy
Paste
'S Format Painter
Clipboard
Ascending Yf Selection
Descending Advanced •
Filter
Z. Remove Sort Toggle Filter
New Totals
c. Save Spelling
Refresh
Delete • More •
eb Replace
Find
Select*
Find
Calibri
All Access Obje... @
Searchm
Tables
CANDIDATE DETAILS
CONSTITUENCY
ELECTIONS
Vill
SQL
Sort & Filter
Close
Close All
Qesign View
SQL View
Datasheet View
Recei' Party
1214 WNA
1871 WNA
1066 WNA
2389 WNA
1364 WNA
2219 WNA
924 WNA
Records
Address
1 Second Street
8 Seventh Street
2 Second Street
6 Fourth Street
4 Third Street
5 Third Street
6 Sixth Street
Gendel
Rachel Ray
Profession
Businessman
Trainer
Dancer
Musician
Driver
Taxi Operator
Bartender
Constituenc
3
5
1
7
4
6
2

[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.. 

Scene 15

OBVIOUSLY you will select Yes.


 Notice now in the Object Pane, a NEW HEADING, QUERY appears with the created Query.

Microsoft Access
Do you want to save changes to the design of query 'Queryl'?

TRINBARJAM ELECTIONS COMMISSION : Database- ELECTIONS COMMISSION.accdI
FILE
View
Views
HOME
CREATE
Cut
Copy
P aste
•Y Format Painter
Clipboard
EXTERNAL DATA
DATABASE TOOLS
Z Ascending Selection
A I Descending Advanced •
Filter
Remove Sort Toggle Filter
Sort & Filter
Spelling
Refresh
X Delete
All •
Records
aL
"ac Replace
Go To •
Find
Select
Find
B
1
All Access Obje... @ «
Search—
Tables
CANDIDATE DETAILS
CONSTITU
ELECTIONS
Queries
QryCandidate

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

Scene 16

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

Relationships, Queries & Criteria

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

avatar

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.

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.

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.

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.

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

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.

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.

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.