Deck 10: Using Action Queries and Advanced Table Relationships

Question 1

When a new table is created using a make-table query, selected fields and records are not deleted from the existing table.

Answer 1

True

Question 2

Text expressions included in the Update To row of an update query should always be enclosed in quotation marks . ____________________

Answer 2

True

Question 3

It is not possible to verify a Join Type once a relationship has been established between two tables.

Answer 3

False

Question 4

The only field that is automatically indexed by AutoIndex settings is the primary key field. ____________________

Question 5

In the accompanying figure, arrow #1 indicates an icon representing a select query. ____________________

Question 6

Access databases may only contain one-to-one relationships between tables. ____________________

Question 7

Two tables joined using a common field forming a one-to-many relationship allow the two tables to be used for other database objects as if they are one large table.

Question 8

All indexes for a table may be viewed by clicking on the Indexes button in the Show/Hide group on the DESIGN tab.

Question 9

Remove a table from the Query window by right-clicking in the field list and choosing delete table. ___________________

Question 10

To maintain current data in a table created by an append query, run the query periodically. ___________________

Question 11

To quickly access the Join Properties dialog box, right-click the join line between two tables in Design view. ____________________

Question 12

A table created using the make-table query will not maintain the primary key designation of the underlying table.

Question 13

Only one existing table may be used in the creation of a delete query.

Question 14

Records deleted using a delete query cannot be restored using the Undo command.

Question 15

When creating an append query, selected fields in the query must match the fields in the table to which the records will be appended. ____________________

Question 16

Inner, outer, and self are all types of queries .

Question 17

An append query overwrites an existing query with new data.

Question 18

An action query can only modify one record in a table at a time.

Question 19

An index may be added to or deleted from tables at any time except for primary key indexes.

Question 20

It is not recommended that a select query be created before creating a make-table query.

Question 21

In the accompanying figure, arrow #1 indicates an icon representing a(n) ____ query.

A) make-table
B) append
C) delete
D) update

Question 22

Two tables related by a common field with the same value in the common field in each table is an example of a(n) ____ join.

A) inner
B) outer
C) self
D) related

Question 23

In the accompanying figure, the join indicated by the green arrow shows which type of relationship between the two tables?

A) one-to-one
B) one-to-many
C) many-to-many
D) many-to-one

Question 24

The type of join in a relationship that selects all records from one table and only the records from a second table that have matching common field values is a(n) ____ join.

A) inner
B) outer
C) self
D) relational

Question 25

In the accompanying figure, arrow #2 indicates an icon representing a(n) ____ query.

A) make-table
B) append
C) delete
D) update

Question 26

To create an action query, click the ____ button from the Create tab in Access 2013.

A) Make-Table
B) Action Query
C) Query Design
D) Add New

Question 27

Select all fields in a table to add to a query design grid by double clicking the ____ of the table field list.

A) field names
B) title bar
C) property sheet
D) primary key

Question 28

The relationship table in a(n) ____ relationship contains a primary key that is a composite key with a unique pair of values.

A) one-to-one
B) one-to-many
C) many-to-many
D) many-to-one

Question 29

A one-to-many relationship is formed when two tables are joined using ____.

A) a primary key field
B) two primary key fields
C) all table fields
D) a common field

Question 30

New tables created with the make-table query retain the field ____ of the underlying table(s).

A) data type
B) validation rule
C) format
D) lookup properties

Question 31

An action query that changes selected field values from selected records in one or more tables is a(n) ____ query.

A) update
B) delete
C) append
D) select

Question 32

In the accompanying figure, arrow #3 indicates the icon that represents a(n) ____ query.

A) make-table
B) append
C) delete
D) update

Question 33

A(n) ____ query creates a new table that is a combination of fields and records from two or more tables.

A) update
B) make-table
C) append
D) SQL

Question 34

Many-to-many relationships are created using a third table that forms ____ relationships with the two original primary tables.

A) many-to-one
B) one-to-many
C) many-to-many
D) one-to-one

Question 35

In the accompanying figure, the PartID field is the ____.

A) primary key in both tables
B) conjoint
C) common
D) restricted

Question 36

A make-table query is a(n) ____ query that creates a new table by copying records from one or more existing tables.

A) SQL
B) select
C) action
D) cross-tab

Question 37

When each record in the first table matches at most one record in the second table, and each record in the second table matches at most one record in the first table, a(n) ____ relationship exists between the two tables

A) one-to-one
B) one-to-many
C) many-to-many
D) many-to-one

Question 38

In a table relationship, the ____ option requires Access to change the appropriate foreign key values in the related table when the primary key value is changed in the primary table.

A) Edit Relationships
B) Enforce Referential Integrity
C) Cascade Delete Related Records
D) Cascade Update Related Records

Question 39

A(n) ____ query adds records form existing tables or queries to the end of another table.

A) make-table
B) SQL
C) delete
D) append

Question 40

In a table relationship, the ____ option requires Access to delete all records in the related table that have a foreign key value related to the primary table when a record is deleted in the primary table.

A) Edit Relationships
B) Enforce Referential Integrity
C) Cascade Delete Related Records
D) Cascade Update Related Fields

Question 41

To access the Edit Relationships dialog box from a shortcut menu, ____ the join line between tables.

A) click and drag
B) delete
C) left-click
D) right-click

Question 42

Records deleted using a Delete query may not be restored by using the ____ command.

A) Redo
B) Undo
C) Unhide
D) Select

Question 43

Which of the following table names would appear in Design view to indicate a table being used in a self-join relationship and distinguish it from the first instance of the table in the relationship?

A) 1_tblPatient
B) tblPatient2
C) tblPatient_1
D) 1tblPatient

Question 44

A(n) ____ value is not allowed in an index if the setting for the Primary property is Yes.

A) date
B) null
C) data type
D) input mask

Question 45

In the accompanying figure, which field is the foreign key in the tblBilling table?

A) InvoiceNum
B) VisitID
C) EmployeeID
D) PatientID

Question 46

A multiple-field index must be created using ____.

A) the Field Properties pane
B) the Data Type drop-down list
C) a subdata sheet
D) the Indexes dialog box

Question 47

In the design grid of an append query, the ____ row identifies the fields that the query will append to the table that was designated.

A) Show
B) Field
C) Append To
D) Criteria

Question 48

A relationship that joins a table to itself is a(n) ____ join type.

A) inner
B) outer
C) self
D) related

Question 49

The ____ and ____ query types give the user an option to modify data in the Current Database or Another Database using the query dialog box.

A) append, delete
B) append, make-table
C) make-table, delete
D) delete, update

Question 50

The default join type for table relationships in Access is a(n) ____ join.

A) inner
B) self
C) outer
D) append

Question 51

Join types may be changed using the Join Types button available in the ____ dialog box.

A) Query Tools
B) Properties
C) Join Wizard
D) Edit Relationships

Question 52

A primary key index is indicated when the Index Property - Primary - has a setting of ____.

A) Yes
B) No
C) Allow Nulls
D) Ignore Nulls

Question 53

In the accompanying figure, the Join Properties dialog box shows the ____ join type option selected.

A) outer
B) self-join
C) inner
D) referential

Question 54

When creating a self-join relationship it is necessary to add the same table to the Relationship window twice in ____ view.

A) Datasheet
B) Design
C) SQL
D) Properties

Question 55

A new table created by a(n) ____ query may contain a subset of the fields and records in an existing table.

A) append
B) make-table
C) delete
D) update

Question 56

Use the Field Properties pane to create an index for ____.

A) a single field
B) multiple fields
C) a single query
D) multiple data types

Question 57

In the accompanying figure, to define a one-to-many relationship between the tblBilling and tblTreatment tables, the ____ common field should be used.

A) EmployeeID
B) VisitID
C) InvoiceNum
D) InvoicePaid

Question 58

Field values related to records that contain those field values may be listed in a(n) ____.

A) relationship
B) index
C) table
D) catalog

Question 59

The choice to enforce referential integrity is indicated in Design view by a thick ____

A) border around the table name
B) border around the primary key of the primary table
C) join line
D) red join line

Question 60

In an auto parts inventory database, one car manufacturer may have many auto parts, and a part may be used for many different vehicle models. This is an example of a(n) ____ relationship.

A) one-to-one
B) one-to-many
C) many-to-one
D) many-to-many

Question 61

Case-Based Critical Thinking Questions Case 9-2
Latham works in the logistics department for a large city bus system.  As routes are changed, Latham accesses the internal database to ensure all records are accurate.  The Bus Route System database includes a one-to-many relationship between the Bus Number and the Bus Routes with Bus ID as the common field.  This allows the two tables to be joined to create a query based on data from both tables:  Bus Number and Bus Routes.
The relationship between the Bus Number and the Bus Routes tables is one-to-many which means that each Bus Route record is connected to _____ Bus Number record(s).

A) at most one
B) at least one
C) exactly one
D) zero, one, or more

Question 62

Case-Based Critical Thinking Questions Case 9-2
Latham works in the logistics department for a large city bus system.  As routes are changed, Latham accesses the internal database to ensure all records are accurate.  The Bus Route System database includes a one-to-many relationship between the Bus Number and the Bus Routes with Bus ID as the common field.  This allows the two tables to be joined to create a query based on data from both tables:  Bus Number and Bus Routes.
The relationship between the Bus Number and Bus Routes tables is one-to-many which means that each Bus Number record is related to ____ Bus Routes record(s).

A) at most one
B) at least one
C) exactly one
D) zero, one, or more

Question 63

Case-Based Critical Thinking Questions Case 9-2
Latham works in the logistics department for a large city bus system.  As routes are changed, Latham accesses the internal database to ensure all records are accurate.  The Bus Route System database includes a one-to-many relationship between the Bus Number and the Bus Routes with Bus ID as the common field.  This allows the two tables to be joined to create a query based on data from both tables:  Bus Number and Bus Routes.
Latham often has to look up bus routes based on specific bus stops.  To speed up the search process in this large database, he might want to create a(n) ____ for the Bus Stop field in the Bus Routes table.

A) update query
B) outer join
C) index
D) self-join

Question 64

When each record in the primary table matches zero, one, or more records in the related table, and when each record in the related table matches at most one record in the primary table, a(n) ____________________ relationship exists.

Question 65

Case-Based Critical Thinking Questions Case 9-3
Taylor manages a database for a regional hardware enterprise with 10 stores in 2 states.  The hardware store wholesales to contractors. The database includes these tables (among others): tblContractor, tblProducts, tblBilling, tblInvoices.
Taylor wants to create a query that shows all contractors and identifies invoices assigned to those contractors.  However, he also wants to see contractors who might not have an invoice assigned for this billing period.  What type of join between tables will give Taylor the desired results?

A) inner join
B) outer join
C) self-join
D) many-to-many

Question 66

Case-Based Critical Thinking Questions Case 9-3
Taylor manages a database for a regional hardware enterprise with 10 stores in 2 states.  The hardware store wholesales to contractors. The database includes these tables (among others): tblContractor, tblProducts, tblBilling, tblInvoices.
Taylor has added the tables tblContractor, tblBilling, and tblInvoices to the relationships window. He has created a relationship between the tblBilling and tblContractor based on the ContractorID field.  What type of relationship is this?

A) one-to-one
B) one-to-many
C) many-to-many
D) one-to-none

Question 67

A query type may be changed from the Query Type group on the DESIGN tab in ____________________ view.

Question 68

In the query design grid for a(n) ____________________ query, the Show and Sort rows are replaced with the Update To row.

Question 69

Case-Based Critical Thinking Questions Case 9-1
Keisha is completing an internship with the local parks and recreation department.  She has been asked to update and maintain the beginning soccer league database for children ages 3-4.  The league consists of over 150 children and 10 soccer teams.  The teams play at two different soccer fields in the city.  The database already exists from last year so Keisha will not be developing the database.  She will only make changes as necessary to the current database.
Keisha's supervisor has asked that she provide her with a table showing all of the soccer teams and the team schedules.  This table will only be used once to schedule coaches.  Which type of query should Keisha run?

A) make-table
B) append
C) delete
D) update

Question 70

Records deleted using a Delete query are permanently removed from the database when the database is ____.

A) closed
B) opened
C) compacted
D) saved

Question 71

In Design view, the Delete row replaces the ____________________ row(s) in the design grid when creating a delete query.

Question 72

____ queries are often used to append records to history tables.

A) Select
B) Append
C) Delete
D) Update

Question 73

Case-Based Critical Thinking Questions Case 9-1
Keisha is completing an internship with the local parks and recreation department.  She has been asked to update and maintain the beginning soccer league database for children ages 3-4.  The league consists of over 150 children and 10 soccer teams.  The teams play at two different soccer fields in the city.  The database already exists from last year so Keisha will not be developing the database.  She will only make changes as necessary to the current database.
Keisha does not want information about beginning soccer players from the past year in the Beginner Soccer League database for the current year. She may run a(n) ____ query with selected criteria such as year and team name to remove last year's players.

A) update
B) append
C) delete
D) make-table

Question 74

Case-Based Critical Thinking Questions Case 9-1
Keisha is completing an internship with the local parks and recreation department.  She has been asked to update and maintain the beginning soccer league database for children ages 3-4.  The league consists of over 150 children and 10 soccer teams.  The teams play at two different soccer fields in the city.  The database already exists from last year so Keisha will not be developing the database.  She will only make changes as necessary to the current database.
After the first week of beginning soccer, some children have decided not to continue playing and children on the wait list have been added to teams with openings.  What type of query would help Keisha add the new players to her current Participant table?

A) make-table
B) append
C) delete
D) update

Question 75

A table with a primary key that is a foreign key to a second table and whose fields are additional fields for the second table is a(n) ____________________.

Question 76

Case-Based Critical Thinking Questions Case 9-2
Latham works in the logistics department for a large city bus system.  As routes are changed, Latham accesses the internal database to ensure all records are accurate.  The Bus Route System database includes a one-to-many relationship between the Bus Number and the Bus Routes with Bus ID as the common field.  This allows the two tables to be joined to create a query based on data from both tables:  Bus Number and Bus Routes.
A join that relates records from the Bus Number table with the Bus Routes table based on the same Bus ID is an example of a(n) ____ join.

A) inner
B) outer
C) self
D) common

Question 77

The type of relationship between two tables when each record in the first table matches many records in the second table, and each record in the second table matches many records in the first table, is a(n) ____________________ relationship.

Question 78

In a many-to-many relationship the relationship table will contain two ____________________ keys.

Question 79

The third table created for a many-to-many relationship is called a(n) ____________________ table.

Question 80

Case-Based Critical Thinking Questions Case 9-1
Keisha is completing an internship with the local parks and recreation department.  She has been asked to update and maintain the beginning soccer league database for children ages 3-4.  The league consists of over 150 children and 10 soccer teams.  The teams play at two different soccer fields in the city.  The database already exists from last year so Keisha will not be developing the database.  She will only make changes as necessary to the current database.
Parents of some of the beginning soccer players have requested that their children be moved to a team that plays at the field closest to their home.  To modify the team field in the current Beginner Soccer League database, Keisha can run a(n) ____ query.

A) delete
B) update
C) make-table
D) append