Deck 5: Creating Advanced Queries and Enhancing Table Design

Question 1

A nonnull field is a field that contains any data at all,including a single space.

Answer 1

True

Question 2

An input mask with a 0 character indicates that only a digit can be entered as the value and that the entry is optional.____________________

Answer 2

False
mandatory

Question 3

Tables used in a find unmatched query must have a common field.____________________

Answer 3

True

Question 4

A parameter query displays a dialog box that prompts the user to enter one or more values when the query is run.____________________

Question 5

When using the IIf function,a condition is tested,and if the condition is false,the first value of the condition is returned by the function.____________________

Question 6

A value of true will be returned if a field is empty when the IsNull function is used.

Question 7

The Like comparison operator is used to select records by matching field values to a specific pattern that includes one or more of the wildcard characters (*,?,#)in a query.____________________

Question 8

A summarized record for each unique field value is displayed in a crosstab query.

Question 9

Yes/No field values are stored in an Access database as a --1 for a checked value and a 0 for an unchecked value.

Question 10

A calculated query uses aggregate functions to perform arithmetic operations on selected records.

Question 11

A pattern match selects records whose values for the designated field matches one of two or more simple condition values.

Question 12

The ____ function tests a condition and returns one of two values;the function returns the first value if the condition is true and the second value if the condition is false.

A)IIf
B)Pmt
C)IsNull
D)NPV

Question 13

Which of the following illustrates a calculated field with the name of the calculated field in the proper format?

A)[Donation]>=2500
B)DonationsGreaterThan2500: [Donation]>=2500
C)>=2500[Donation]
D)[Donation]:DonationsGreaterThan2500:>=2500

Question 14

The standard naming convention prefix tag for a query used to identify the object type is ____.

A)Query
B)QURY
C)qry
D)QRY#

Question 15

The format field command freezes a field (column)in a datasheet so that the field stays visible when the user scrolls to the right while viewing the datasheet.____________________

Question 16

An input mask property cannot be changed after the Input Mask Wizard closes.

Question 17

When using the IIF function,each part of the function is separated by a colon.____________________

Question 18

Any type of Access object may be deleted from the Tables Pane using the Delete command on the shortcut menu.____________________

Question 19

Max is an example of a(n)logical function used in a crosstab query.____________________

Question 20

Joining two or more fields in a calculated field using an expression such as =Title &" "&FirstName &" " &LastName is an example of concatenation.____________________

Question 21

A Social Security Number which appears in the format 123-456-7891 is an example of a(n)____.

A)field value
B)predefined input mask
C)aggregate function
D)variance

Question 22

Validation Rules are entered in the ____

A)Navigation Pane
B)datasheet
C)dependent object
D)Field Properties Pane

Question 23

Which comparison operator defines a condition with a list of two or more values for a field and includes records when the field matches one value from the list in the query results?

A)Like
B)Between
C)In
D)Or

Question 24

A criterion may be negated by using the ____ logical operator.

A)And
B)Or
C)Like
D)Not

Question 25

To add more flexibility and functionality to a parameter query,comparison operators,such as ___,may be added to the original criterion.

A)Like
B)Unlike
C)IsIn
D)IsNull

Question 26

A ____ query uses functions,such as Sum and Count,to perform arithmetic operations on selected records.

A)parameter
B)select
C)crosstab
D)simple

Question 27

The value of a field containing no information is a ____ value.

A)blank
B)zero
C)concatenated
D)null

Question 28

In an input mask,the letter ____ indicates that a letter or digit must be entered,and the entry is required.

A)A
B)a
C)C
D)L

Question 29

Which of the following is an example of a parameter query prompt criterion?

A)[Type the state:]
B)Prompt: "Type the state:"
C)*Type the state:*
D){Type the state:}

Question 30

The input mask character ____ indicates that a letter only must be entered,and the entry is required.

A)A
B)C
C)L
D)&

Question 31

Rich text formatting available for Long Text fields includes ____.

A)paragraph styles
B)font options
C)themes
D)borders

Question 32

In crosstab query results,the value of a checked Yes/No data type field will display as ____.

A)0
B)1
C)-1
D)+1

Question 33

The calculated expression =City &"," &State &" " &ZipCode is an example of ____.

A)concatenation
B)ampersand
C)AutoFilter
D)a parameter query

Question 34

The office manager at Riverview Veterinary Care Center would like to create a query which displays all patients who have not yet received their yearly check up.She will use the qryAnimalsAndOwners and tblVisit table to create the query.Which type of query will most likely create the desired results?

A)crosstab query
B)find unmatched query
C)simple query
D)find duplicates query

Question 35

Crosstab queries may use which of the following aggregate functions?

A)Like
B)Not
C)Var
D)Between

Question 36

Setting the ____ field property to Yes instructs Access to keep an historical record of all versions of the Long Text field value.

A)Append Only
B)Validation Rule
C)Input Max
D)Default Value

Question 37

Which of the following pattern match selection criteria would return zip codes with the characters 704 in the first three spaces.

A)IIf (704*)
B)Like "704*"
C)Like *704*
D)Find "704*"

Question 38

The aggregate function which returns the number of nonnull field values is the ____ function.

A)Max
B)Sum
C)Avg
D)Count

Question 39

The ____ comparison operator is used to select records by matching field values specific to a pattern that includes one or more wildcard characters.

A)Between
B)More
C)In
D)Like

Question 40

Which of the following examples shows an input mask for a telephone number with the first three digits optional and dashes separating the parts of the telephone number?

A)999\-000\-0000
B)999-000-9999
C)999\000\0000
D)000\-999\-9999

Question 41

An expression that calculates the values of the data in a field is contained in a ____________________ field.

Question 42

____________________ perform arithmetic operations on selected records in a crosstab query.

Question 43

Carlos has developed a database for his organic farm.The farm sells organic produce and meat.In addition to the retail sales of his products,he offers cooking demonstrations five times each month for a small fee.The demonstrations are limited to 10 people.The database contains the following tables: Products,ProductPrices,ProductCosts,Suppliers,Customers,and CookingDemos.The Products,Product Prices,Product Costs and Suppliers tables are related using the ProductID field.The Customers and CookingDemos tables are related on the CustomerID field.Carlos needs to make some decisions using his database.Determine the best method for Carlos to use to achieve the results he wants.
Carlos needs to decide which cooking demonstrations to offer next month.He would like to offer his five most popular demonstrations.Which method would be best for Carlos to use to find this information from his database?

A)use a calculated field in the CookingDemo table to calculate how many people signed up for each demonstration
B)manually count the number of people who attended each demonstration
C)create a top values query based on the field in the CookingDemo table that lists the number of participants
D)create a pattern match in a query based on the Customer table

Question 44

The ____________________ aggregate function returns the average of the field values.

Question 45

A standard language used in querying,updating,and managing relational databases is ____________________.

Question 46

Which Access object(s)may be used when creating a lookup field to select values?

A)table
B)form
C)report
D)both table and report

Question 47

Carlos has developed a database for his organic farm.The farm sells organic produce and meat.In addition to the retail sales of his products,he offers cooking demonstrations five times each month for a small fee.The demonstrations are limited to 10 people.The database contains the following tables: Products,ProductPrices,ProductCosts,Suppliers,Customers,and CookingDemos.The Products,Product Prices,Product Costs and Suppliers tables are related using the ProductID field.The Customers and CookingDemos tables are related on the CustomerID field.Carlos needs to make some decisions using his database.Determine the best method for Carlos to use to achieve the results he wants.
As Carlos plans for the fall planting season,he would like to bundle all of his shipments from the Organic Opportunities supplier into one shipment.He needs to know which products he has previously ordered from Organic Opportunities.Which method will quickly produce the results Carlos needs?

A)create a calculated query using the Products table
B)create a find unmatched query using the Suppliers table
C)use a lookup field in the Products table
D)use the AutoFilter option in the Supplier table for the appropriate field

Question 48

When creating an input mask,the ____ input mask character is used to indicate that the following character will be a literal display of that character.

A)#
B)&
C)\
D)!

Question 49

Which query wizard allows the user to choose the field that will be calculated for each row and column intersection?

A)Find Duplicates Query Wizard
B)Crosstab Query Wizard
C)Simple Query Wizard
D)Find Unmatched Query Wizard

Question 50

To avoid the display of a security warning when opening a database,place the database ____.

A)on a flash drive
B)only on a hard drive
C)in an external folder on an external drive
D)in a trusted folder

Question 51

A select query that finds duplicate records in a table or query is a ____________________ query.

Question 52

Carlos has developed a database for his organic farm.The farm sells organic produce and meat.In addition to the retail sales of his products,he offers cooking demonstrations five times each month for a small fee.The demonstrations are limited to 10 people.The database contains the following tables: Products,ProductPrices,ProductCosts,Suppliers,Customers,and CookingDemos.The Products,Product Prices,Product Costs and Suppliers tables are related using the ProductID field.The Customers and CookingDemos tables are related on the CustomerID field.Carlos needs to make some decisions using his database.Determine the best method for Carlos to use to achieve the results he wants.
Carlos would like to create a query based on the Customer table that allows him to type in the name of the cooking demonstration for which the query will run.Which type of query should Carlos create so that he can type in the name of the cooking demonstration before the query runs?

A)parameter query
B)top values query
C)crosstab query
D)pattern match query

Question 53

The ____________________ button in the Sort &Filter group on the Home tab is used to remove the current filter in a dataset or to reapply a filter.

Question 54

Marie works for a busy pediatrician's office.The practice has a sophisticated database for all records and information that pertain to the practice.The doctor for whom she works is participating in an academic study,and she needs a report that lists the patient's name,date of birth,and the names of parents or guardians.The doctor would like to see the patient's full name in the format Lastname,Firstname even though they are separate fields in the database.She would also like to see the data for the fields Mother,Father,and Guardian,regardless of whether or not data is actually entered in those fields.  Based on the doctor's requirements for the report,Marie must create a query on which to base the report.
Marie creates the following expression as a new field in the query that she will use to create the report requested by the doctor PatientName: [LastName] &"," [FirstName].  This expression is an example of ____.

A)IsNull
B)concatenation
C)nonnull
D)an expression using a logical operator

Question 55

It is a good idea to ____ the database before using compact and repair to keep the database safe from hardware and software malfunctions.

A)use a second computer to save
B)run a virus check on
C)make a backup copy of
D)proofread

Question 56

Carlos has developed a database for his organic farm.The farm sells organic produce and meat.In addition to the retail sales of his products,he offers cooking demonstrations five times each month for a small fee.The demonstrations are limited to 10 people.The database contains the following tables: Products,ProductPrices,ProductCosts,Suppliers,Customers,and CookingDemos.The Products,Product Prices,Product Costs and Suppliers tables are related using the ProductID field.The Customers and CookingDemos tables are related on the CustomerID field.Carlos needs to make some decisions using his database.Determine the best method for Carlos to use to achieve the results he wants.
Carlos has decided to market his cooking demonstrations based on his most abundant products.To do that he needs to know the quantity of each product by category (such as beef,green vegetables,peppers,etc. )Because each product in the Products table is assigned a category in the Category field,he will create a query that lists the categories in rows in the datasheet and the product totals as columns in the datasheet when the query is run.Which type of query will Carlos create?

A)crosstab query
B)parameter query
C)simple query
D)top values query

Question 57

Which type of query would show results indicating two different student ID numbers have been assigned to the same student?

A)simple query
B)find unmatched query
C)find duplicates query
D)crosstab query

Question 58

Marie works for a busy pediatrician's office.The practice has a sophisticated database for all records and information that pertain to the practice.The doctor for whom she works is participating in an academic study,and she needs a report that lists the patient's name,date of birth,and the names of parents or guardians.The doctor would like to see the patient's full name in the format Lastname,Firstname even though they are separate fields in the database.She would also like to see the data for the fields Mother,Father,and Guardian,regardless of whether or not data is actually entered in those fields.  Based on the doctor's requirements for the report,Marie must create a query on which to base the report.
Because the doctor wants all data entered for parents and guardians regardless of the data in the field,Marie must create a calculated field in the query with a function that tests the Mother,Father,and Guardian fields for a condition that is either true or false.If the condition is true,one result will be displayed,and if the condition is false a different result will be displayed.Which function should Marie use for this calculated field?

A)Input
B)IIf
C)nonnull
D)IsError

Question 59

An input mask may be created for any field with the ____ or ____ data type.

A)Short Text,Number
B)Number,Currency
C)Long Text,Currency
D)Currency,Autonumber

Question 60

You can define a condition with a list of two or more values for a field by using the ____ comparison operator.

A)Like
B)While
C)Between
D)In

Question 61

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
The capability of a database to update field properties in objects automatically when a table field property is modified.

Question 62

Fields and criteria that will be used in a query are contained in the ____________________ when creating a query.

Question 63

Table Validation Rules are entered using the ____________________ for a table.

Question 64

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
A rule that is set when the validation involves multiple fields.

Question 65

When creating a find unmatched query using the query wizard,Matching fields will be joined automatically if a ____________________ is already defined between the two selected objects.

Question 66

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
Selects records for the designated field when the value matches one of two or more condition values.

Question 67

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
A field data type that allows text values to be entered up to 65,535 characters.

Question 68

Valid values that may be entered in a field are specified using the ____________________ propoerty.

Question 69

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
A select query that finds duplicate records in a table or query.

Question 70

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
A field property option that verfies a field value by comparing the value entered to a constant or set of constants.

Question 71

The Top Values query property allows the user to limit the number of records in the query results by using a preset value,by entering an integer,or by selecting a(n)____________________.

Question 72

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
A standard query language used in querying,updating,and managing relational databases.

Question 73

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
Selects a record for the designated field with a value that matches the pattern of a simple condition value.

Question 74

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
A select query that finds all records in a table or query that have no related records in the second table or query.

Question 75

a.find unmatched query
b.SQL (Structured Query Language)
c.list-of-values match
d.table validation rule
e.Long Text
f.lookup field
g.pattern match
h.Validation Rule
i.find duplicates query
j.property propagation
REFERENCES: Creating a Find Unmatched Query
Creating a Crosstab Query
Using a List-of-Values Match in a Query
Defining Data Validation Rules
Working with Long Text Fields
Creating a Lookup Field
Using a Pattern Match in a Query
Defining Data Validation Rules
Session 5.2 Visual Overview
Using the Input Mask Wizard
A field that allows the user to select a value from a list of possible values to enter data in the field.