Question 1

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

Accepted Answer

The answer of The ____________________ aggregate function returns the average...

Question 2

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

Accepted Answer

The answer of ____________________ perform arithmetic operations on selected records...

Question 3

Case Based Critical Thinking Questions Case 5-1
Janise is creating a database to keep track of her customers for her pet sitting business. She is in the process of creating the Customer table. She would like to design the table so that data entry is as consistent and accurate as possible. Using the figures below, determine which Access features Janise used to ensure consistent and accurate data entry.

Figure Case 5-1-1

Figure Case 5-1-2
According to the Field Properties in Figure Case 5-1-2, to which field has Janise assigned an input mask?
A) ID
B) State
C) Zip
D) PrimaryPhone

Accepted Answer

Janise has assigned an input mask to the Zip field to ensure consistent and accurate data entry. The input mask restricts the user to entering only five digits, which is the correct format for a ZIP code. She has also set the field size to five, which reinforces this restriction. Additionally, she has set the Validation Rule for the field to "Is Not Null," which ensures that a ZIP code is entered for each customer record. There is no input mask assigned to the ID, State, or PrimaryPhone fields.

Question 4

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

Accepted Answer

The answer of A select query that finds duplicate records...

Question 5

Case Based Critical Thinking Questions Case 5-2
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

Accepted Answer

Carlos should create a parameter query, which allows the user to input a value before the query runs. This will enable him to type in the name of the cooking demonstration before the query is executed, allowing him to see all customers who have registered for that specific demonstration.

Question 6

Case Based Critical Thinking Questions Case 5-2
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

Accepted Answer

Creating a top values query based on the field in the CookingDemo table that lists the number of participants would be the best method for Carlos to use to find out which cooking demonstrations are most popular. This method would allow him to easily identify the top 5 demonstrations with the highest number of participants. Using a calculated field or manually counting attendees would be inefficient and time-consuming, while creating a pattern match in a query based on the Customer table would not provide the relevant information about the popularity of specific cooking demonstrations.

Question 7

Case Based Critical Thinking Questions Case 5-2
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

Accepted Answer

Carlos will create a crosstab query because he wants to list the categories in rows and the product totals as columns in the datasheet when the query is run. A crosstab query is designed to summarize data and display the results in a compact, easy-to-read format suitable for analysis.

Question 8

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.

Accepted Answer

The answer of The ____________________ button in the Sort &...

Question 9

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.

Accepted Answer

The answer of When creating a find unmatched query using...

Question 10

Case Based Critical Thinking Questions Case 5-2
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

Accepted Answer

The answer of Case Based Critical Thinking Questions Case 5-2
Carlos...

Question 11

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

Accepted Answer

The "In" operator allows you to specify a list of values for a field and is used to define a condition with multiple values. For example, "WHERE color IN ('red', 'blue', 'green')" would return results where the color field is either red, blue or green.

Question 12

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

Accepted Answer

The answer of A standard language used in querying, updating,...

Question 13

Case Based Critical Thinking Questions Case 5-3
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

Accepted Answer

Marie should use the IIf function for the calculated field. The IIf function tests for a condition and returns one value if the condition is true and a different value if the condition is false. In this case, Marie can use the IIf function to test if there is data in any of the Mother, Father, or Guardian fields. If there is data, she can display it in the calculated field. If not, she can display a message such as "No Data Available." The Input function is not appropriate for this scenario, as it is used to convert a value to a specific data type. The nonnull function is not a valid function in Microsoft Access. The IsError function is used to determine if a value is an error value, and is not necessary for this scenario.

Question 14

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

Accepted Answer

The answer of An expression that calculates the values of...

Question 15

Case Based Critical Thinking Questions Case 5-1
Janise is creating a database to keep track of her customers for her pet sitting business. She is in the process of creating the Customer table. She would like to design the table so that data entry is as consistent and accurate as possible. Using the figures below, determine which Access features Janise used to ensure consistent and accurate data entry.

Figure Case 5-1-1

Figure Case 5-1-2
The 9999 included in the Input Mask property in Figure Case 5-1-2 indicates that data entry is ____ for that part of the field.
A) required
B) optional
C) automatic
D) a default value

Accepted Answer

The answer of Case Based Critical Thinking Questions Case 5-1
Janise...

Question 16

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) ____________________.

Accepted Answer

The answer of The Top Values query property allows the...

Question 17

Case Based Critical Thinking Questions Case 5-1
Janise is creating a database to keep track of her customers for her pet sitting business. She is in the process of creating the Customer table. She would like to design the table so that data entry is as consistent and accurate as possible. Using the figures below, determine which Access features Janise used to ensure consistent and accurate data entry.

Figure Case 5-1-1

Figure Case 5-1-2
The State field in Figure Case 5-1-1 illustrates which of the following?
A) an Autofilter
B) a conditional value
C) a lookup field
D) an input mask

Accepted Answer

The answer of Case Based Critical Thinking Questions Case 5-1
Janise...

Question 18

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

Accepted Answer

Lookup fields are based on values from a table, so the most appropriate object to use when creating a lookup field is a table. Forms and reports can also be used to display lookup values, but they are not suitable for creating the lookup field itself.

Question 19

Case Based Critical Thinking Questions Case 5-1
Janise is creating a database to keep track of her customers for her pet sitting business. She is in the process of creating the Customer table. She would like to design the table so that data entry is as consistent and accurate as possible. Using the figures below, determine which Access features Janise used to ensure consistent and accurate data entry.

Figure Case 5-1-1

Figure Case 5-1-2
To ensure that all telephone numbers are entered in a consistent manner, Janise assigned a(n) ____ to the PrimaryPhone field as illustrated in Figure Case 5-1-2.
A) lookup field
B) input mask
C) data type
D) propagation property

Accepted Answer

Janise used an input mask to ensure consistent entry of telephone numbers in the PrimaryPhone field. The input mask is illustrated in Figure Case 5-1-2, and ensures that all phone numbers are entered in the format of (999) 999-9999.

Question 20

Case Based Critical Thinking Questions Case 5-3
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

Accepted Answer

The expression created by Marie is an example of concatenation, which combines two or more values or fields into one string. A is incorrect because IsNull checks if a field contains a null value. C is incorrect because nonnull is not a known database term. D is incorrect because the expression does not use a logical operator.

Quiz 1: Access: Creating Advanced Queries and Enhancing Table Design

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

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

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

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.

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.

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

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

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

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) ____________________.

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