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.

Review Questions

1. An Access query is initially created by selecting the ___________ followed by _____________.

   An Access query is initially created by selecting the Create tab followed by Query Design button.
2. To add criteria to an Access query, you will need to add the criterion in the __________ row.

   To add criteria to an Access query, you will need to add the criterion in the Criteria row.
3. To get an in dept look at your query you must use the ___ view

   To get an in dept look at your query you must use the SQL view
4. A query that is made up of more than one table is called a __________ query.

   A query that is made up of more than one table is called a multitable query.
5. "And" criteria is listed on the _____ row and "Or" criteria is listed on _______ row.

   "And" criteria is listed on the SAME row and "Or" criteria is listed on A DIFFERENT row.
6. Relationships help to maintain the _________ of data.

   Relationships help to maintain the integrity of data.
7. To find a no value within the database, type _________ in the query conditional field.

   To find a no value within the database, type Null in the query conditional field.
8. The overall completeness, accuracy and consistency of data is referred to_______.

   The overall completeness, accuracy and consistency of data is referred to data integrity.

See Also

• Wikibooks: Database Design/Fundamental Concepts
• YouTube: MS Access Queries quick examples
• Microsoft Office: Table Relationships

References

← Tables

Database Applications

Advanced Queries →