Deck 9: Using Action Queries and Advanced Table Relationships

Question 1

What is an action query

Answer 1

Access allows users to query its database to access, modify, update and delete the data stored in its tables.
The Queries used by Access to modify, update and delete data from multiple tables or multiple records from a table at once are called as Action Queries.
Types of Action Queries:
• Make-Table Query: This query is used to view the data in a table. In other words, a make-table action query creates a new table from existing tables by satisfying the criteria specified in the query. Here the records in the old tables are not deleted.
• Append Query: This query simply appends the selected fields and records from an existing table to another table. For example, consider a supermarket is maintaining its yearly sales. Using an append query it can append the present half-yearly sales to previous half-yearly sales and thus maintain the whole year sales in one table. Advantage of this query is data that is appended to another table is not deleted from the existing table.
• Update Query: Update Query is used to update field values in records for a table.
• Delete Query: Delete Query is for deleting the records from the tables. Records once deleted are permanently erased.

Question 2

What precautions should you take before running an action query

Answer 2

Access allows users to query its database to access, modify, update and delete the data stored in its tables.
The Queries used by Access to modify, update and delete data from multiple tables or multiple records from a table at once are called as Action Queries.
Before creating an action query, first it is necessary to test the query on the tables. This can be done by creating a select query to the action that must be implemented.
The select query must be executed first. Once the result is as expected then convert the select query to action query. Then it can be executed against the database.

Question 3

What is the difference between a make-table query and an append query

Answer 3

• Make-Table Query is used to view the data in a table. In other words, a make-table action query creates a new table from existing tables by satisfying the criteria specified in the query. Here the records in the old tables are not deleted.
Append Query simply appends the selected fields and records from an existing table to another table.
• Make-Table query does not delete the records from original tables. These queries are used to create customized tables.
Append Query are used to create history tables. Therefore, once data is appended from a table to another, the user can delete the records.
• The tables created using Make-Table query represent current data.
In Append query, the data will change as updates occur.

Question 4

What does a delete query do

Question 5

What does an update query do

Question 6

How does the design grid change when you create an update query

Question 7

What are the three types of relationships you can define between tables

Question 8

What is an entity subtype

Question 9

What is the difference between an inner join and an outer join

Question 10

What is a self-join

Question 11

What is an index

Question 12

Figure 9-30 lists the field names from two tables: tblDepartment and tblEmployee.