Database Applications/Queries

This lesson assists users in creating select queries from single tables, creating table relationships followed with the creation of queries using multiple tables.

Objectives and Skills
Objectives and skills for this lesson include:

Readings

 * 1) McFadyen: Relational Databases and Microsoft Access - Chapter 4 (Microsoft Access Queries)
 * 2) McFadyen: Relational Databases and Microsoft Access - Chapter 5 (Relationships)
 * 3) Supportive resource:  Wikibooks: Microsoft Office/Create queries
 * 4) Supportive resource:  Microsoft: Create a simple select query
 * 5) Supportive resource: https://edu.gcfglobal.org/en/access2013/sorting-and-filtering-records/1/ GCFGlobal:Sorting and Filtering Records
 * 6) Supportive resource: https://www.techrepublic.com/article/10-plus-tips-for-working-efficiently-in-access-query-design-window/ TechRepublic 10+ Tips for working efficiently in Access' Query Design window
 * 7) Supportive resource: https://support.office.com/en-us/article/use-wildcards-in-queries-and-parameters-in-access-ec057a45-78b1-4d16-8c20-242cde582e0b Microsoft Office: Use Wildcards in Queries
 * 8) Microsoft: Append Query

Multimedia

 * 1) GCF Global: Access 2016 Designing a Simple Query
 * 2) Microsoft Access 2016 videos and tutorials
 * 3) Youtube: What is a Query in Microsoft Access?
 * 4) Youtube: How to Create Access Relationships
 * 5) Youtube: GCF video - Creating Multi-table Queries

Activities

 * 1) Complete the tutorial GCF Global: Access 2016 Designing a Simple Query.
 * 2) Review McFadyen: Relational Databases and Microsoft Access - Chapter 4 (Microsoft Access Queries). Complete the exercises in Chapter 4.
 * 3) Review McFadyen: Relational Databases and Microsoft Access - Chapter 5 (Relationships). Complete the exercises in Chapter 5.

Lesson Summary

 * A database is an organized collection of data.
 * Select queries can be created by using the Access Query Wizard or Access Design view.
 * The creation of a query should be properly planned with the inclusion of the following steps:
 * Plan how the information and query results should be displayed.
 * Identity and select the database table(s).
 * If there is more than one table, verify that the tables display a database join between the key fields.
 * Identify, select and organize the required fields (columns) in the query design grid.
 * Apply the criteria in the query design for the selected field(s).
 * Sort the applicable field(s) in either ascending or descending order.
 * Verify the query results for accuracy and elimination of any syntax or logic errors.
 * A query can obtain its data from one table, multiple tables or from an existing query.
 * Queries can be sorted to display fields in either ascending or descending order.
 * Relationships can be One-To-Many, One-To-One, and Many-To-Many.

Key Terms

 * AND operator
 * The value of an AND expression is true only if both input values are true.


 * calculated field
 * A field that involves a calculation utilizing existing fields from a table.


 * datasheet view
 * to see many records of data in your MS Access app at the same time.


 * field name
 * In computer science, a field name identifies a field in a database record.


 * join
 * Combines columns from one or more tables in a relational database.


 * null value
 * A null value indicates that a data value does not exist in the database.


 * OR operator
 * The value of an OR expression is when at least one of the input values is true.


 * parameter query
 * A parameter query lets the user answer the question each time the query is executed to get to the records that they want.


 * query by example (QBE)
 * Query by Example (QBE) is a database query language used to create queries for relational databases.


 * query criteria
 * Query criteria are field values entered in the criteria row in the Access design grid. Access compares to query field values with the records to determine if the record that contains the criteria value.


 * relationship
 * Organizes data into one or more tables (or "relations") of columns and rows, with a unique key identifying each row.


 * select query
 * A fundamental Access query that displays information from database object(s) in the Access Datasheet view. This allows you to specify the data you want from one or more sources which can help you get only the data you want and can even help with combining data from more than one source.


 * simple query
 * The simplest query in Microsoft Access, which displays all the rows and columns in the table.


 * projection query
 * A query that requires the user to specify the fields of the table to be displayed.

one-to-many relationship

 * When there is one record (also known as parent record) in a table which must be associated with one or more records (child or children records) in a second table.

one-to-one relationship

 * If you drag a primary key field of one table to another table, and if the foreign key has unique values (a unique index exists for it) then you are creating a one-to-one relationship.

many-to-many relationship

 * If you create a relationship in Microsoft Access where both fields you associated (via the click, hold, and drag sequence) do not have unique values (i.e. neither have unique indexes) then Access creates an ‘indeterminate’ relationship. Most database designers would avoid this in their database designs.