Deck 11: Creating Advanced Queries

Question 1

If you use the Top Values option to show the top 5 records and there is a tie for fifth place, the datasheet will display ____.

A) Only four records
B) The first four records plus all records that tie for fifth place
C) The first four records plus one record for fifth place determined by the value in the key field of the records that tie
D) An error message indicating that the Top Values entry must be changed

Answer 1

B

Question 2

In the figure above, what Top Values option is chosen?

A) Top 5
B) Top 100
C) Top 5%
D) All

Answer 2

D

Question 3

To prevent running an action query accidentally, use the Run button to view the selected records.

Answer 3

False

Question 4

When you run an action query Access prompts you with an "Are you sure?" message before actually changing the data.

Question 5

Double-clicking an action query in the Navigation Pane runs the query.

Question 6

The Top Values feature creates summarized calculations on groups of records.

Question 7

In the figure above, item 1 refers to which feature?

A) Group By
B) Totals
C) Top Values
D) Query Type

Question 8

In the figure above, what does item 3 refer to?

A) Count of EnrollmentID field
B) Sum of EnrollmentID field
C) Subtotal of records with numeric values
D) Total of EnrollmentID field

Question 9

Action queries select but do not change data.

Question 10

In the figure above, what does item 2 toggle on and off?

A) Criteria row
B) Totals row
C) Show row
D) Sort row

Question 11

In Query Design View, the asterisk in the field list represents all the fields in the table.

Question 12

In the figure above, which field is being counted?

A) Enrollments
B) EnrollmentID
C) Description
D) CourseID

Question 13

In the figure above, which field is being grouped?

A) Enrollments
B) EnrollmentID
C) Description
D) CourseID

Question 14

In the figure above, how will the records be presented?

A) Smallest to largest by count of EnrollmentID
B) Largest to smallest by count of EnrollmentID
C) Smallest to largest group by Description
D) Largest to smallest group by Description

Question 15

The datasheet shown in the figure above sorts records on the EnrollmentID field.

Question 16

It is wise to make a current backup of the database before running any action query.

Question 17

This kind of operator selects all records between 2 specified values.

A) Find Unmatched
B) Make Table
C) Between...And
D) Yes/No

Question 18

In the figure above, the parameter criteria is ____.

A) Descending
B) Group By
C) 5
D) There is no parameter criteria in the figure.

Question 19

An action query changes data as soon as the type of action query is chosen from the Query Type list.

Question 20

If you modify a field's properties in Table Design View, they are modified for the entire database.

Question 21

Parameter criteria are always entered within ____.

A) (Parentheses)
B) {Curly brackets}
C) [Square brackets]
D).

Question 22

All action query icons display a(n) ____ in the icon.

A) Asterisk (*)
B) At symbol (@)
C) Percent sign (%)
D) Exclamation point (!)

Question 23

Which of the following is a valid parameter criterion?

A) >=Type date here
B) >=(Type date here:)
C) >={Type date here}
D) >=[Type date here:]

Question 24

Which of the following is NOT a type of action query?

A) Select
B) Delete
C) Update
D) Make-table

Question 25

Which of the following characters is a wildcard?

A) Ampersand (&)
B) Asterisk (*)
C) Plus Sign (+)
D) Percent Sign (%)

Question 26

Which query criteria are equivalent to "Marketing" ?

A) "marketing"
B) "MARKETING"
C) "mArKeTiNg"
D) All of the above

Question 27

In the figure above, item 6 refers to the ____ query button.

A) Append
B) Delete
C) Make Table
D) Update

Question 28

If a parameter query prompts and searches for any name that begins with the entered character, then which of the criteria cell entries below is the correct one?

A) >=[Enter start date:] and
B) LIKE [Enter the first character of the last name:] & "*"
C) >=[Enter start date:]
D) None of the above

Question 29

Which button do you use to complete an action query?

A) View
B) Select
C) Show
D) Run

Question 30

Access automatically adds ____ around date criteria.

A) Quotation marks
B) Pound signs
C) Square brackets
D) Asterisks

Question 31

The ____ entry in the Recordset Type property locks the recordset.

A) Snapshot
B) Dynaset
C) Dataset
D) Lockset

Question 32

If a parameter query prompts and searches for dates on or after the entered date, then which of the criteria below is the correct one?

A) LIKE [Enter start date:]
B) LIKE [Enter start date and end date:]
C) >=[Enter start date:] and
D) None of the above

Question 33

In the figure above, item 5 refers to the ____ query button.

A) Append
B) Delete
C) Make Table
D) Update

Question 34

The criteria cell entry: LIKE [Enter the first character of the company name:] & "*" searches for companies that do which of the following?

A) Contain the parameter entry anywhere in the field
B) End with the specified letter
C) Have only one character
D) Begin with the specified character

Question 35

Which action query would you use if you wanted to export records to another Access database?

A) Make-Table
B) Top Values
C) Update
D) Delete

Question 36

In the figure above, item 4 refers to the ____ query icon.

A) Append
B) Delete
C) Make Table
D) Update

Question 37

If a parameter query prompts and searches for words that contain the entered characters anywhere in the field, then which of the criteria below is the correct one?

A) LIKE [Enter the first character of the last name:] & "*"
B) LIKE "*" & [Enter any character(s) to search by:] & "*"
C) [Enter start date:]
D) None of the above

Question 38

When building an action query, data will not be changed until you click the ____ button.

A) Datasheet
B) Query Type
C) Run
D) Update

Question 39

Which of the following is true about the criteria cell entry: LIKE [Enter the first character of the company name:] & "*"?

A) [Enter the first character of the company name:] is the parameter prompt.
B) & is the wildcard.
C) "*" is the second parameter prompt.
D) All of the above.

Question 40

In the figure above, item 7 refers to the ____ query button.

A) Append
B) Delete
C) Make Table
D) Update

Question 41

When you click the Append button from the Query Type list, the _________________________ To row is added to the query design grid.

Question 42

A(n) _________________________ query displays a dialog box prompting you for information to enter as limiting criteria each time the query runs.

Question 43

List the four types of action queries and give an example of what each would do.

Question 44

A table has a(n) _________________________ record when information in the foreign key field of the "many" table doesn't have a matching entry in the primary key field.

Question 45

Items 3 through 7 shown in the figure above are Query _________________________ buttons displayed on the DESIGN tab.

Question 46

In the figure above, item 1 refers to the _________________________ Values list arrow.

Question 47

Explain what a parameter query does.

Question 48

A(n) _________________________ query is one that makes changes to the data. There are several types of these queries.

Question 49

If you wish to increase the price of a product in a particular category by 10%, you could use a(n) ____ query.

A) Select
B) Make-table
C) Append
D) Update

Question 50

Which action query would you use if you wanted to add records from one table to a backup table?

A) Delete
B) Update
C) Append
D) Make-Table

Question 51

If you type the last 2 digits of a year as 31, such as 1/1/31, Access assumes that they signify:

A) 1931
B) 2031
C) 1831
D) You must type all 4 digits for a year.

Question 52

Use the Find _________________________ Query Wizard to find records in one table that do not have matching records in a related table.

Question 53

In Query Design View, double-click the _________________________ at the top of a table's field list to quickly add all fields within the table to the query design grid.

Question 54

When you click the Update button from the Query Type list, the _________________________ To row is added to the query design grid.

Question 55

Which action query would you use if you wanted to change the name of a manager from "Smith" to "Jones" for all selected records?

A) Delete
B) Update
C) Append
D) Make-Table

Question 56

Use the Find _________________________ Query Wizard to find records in one table that have the same values in a field.

Question 57

Which action query would you use if you wanted to remove product records that are discontinued from a Products table?

A) Delete
B) Update
C) Append
D) Make-Table

Question 58

When you click the Delete button from the Query Type list, the _________________________ row is added to the query design grid.

Question 59

In a one-to-many relationship with referential integrity, the "one" table contains ____ records and the "many" table contains ____ records.

A) parent ˜ child
B) orphan ˜ parent
C) orphan ˜ child
D) child ˜ parent

Question 60

If you type the last 2 digits of a year as 13, such as 1/1/13, Access assumes that they signify:

A) 1813
B) 1913
C) 2013
D) You must type 4 digits for year data.

Question 61

Like [Enter the first character of the last name:]& "*" is an example of the Like operator used with parameter criteria. ____________________

Question 62

Refer to the figure above. Describe what you would do to show all of the records in the Courses table even if there were no related Enrollments records.

Question 63

To enter a long criterion in Query Design View, right-click the Criteria cell, then click Zoom . ____________________

Question 64

Explain how to query for null and zero-length string values.

Question 65

A prompt query displays a dialog box that prompts you for field criteria. ____________________

Question 66

Refer to the figure above. What type of join would be created if you clicked option 2?

Question 67

The Description property appears on the Database Documenter reports. ____________________

Question 68

You can build a form or report based on a parameter query. ____________________

Question 69

Explain null and zero-length string values.

Question 70

A backup is a copy of the database that you could use if an error occurs in the current database that cannot be fixed. ____________________

Question 71

Records are characteristics that define the appearance and behavior of items in the database such as objects, fields, sections, and controls. ____________________

Question 72

You can keep an action query in the Navigation Pane but hide it using the Action property. ____________________

Question 73

To enter a long criterion, right-click the Criteria cell then click Expand . ____________________

Question 74

The Like operator is used with the asterisk wildcard in query criteria. ____________________

Question 75

Read/write is the default value for the Recordset Type property which allows you to update data. ____________________

Question 76

Referring to the figure above, describe what you would do to show all of the records in the Enrollments table even if there were no related Courses records.

Question 77

Referring to the figure above, explain what records will be selected.

Question 78

Referring to the figure above, what is the relationship between the Courses and Enrollments tables?

Question 79

Enter parameter query criteria in curly brackets. ____________________

Question 80

Click the VIEW tab on the ribbon to create a backup. ____________________