Programming practice quiz 10

Question 1

In a query to get details of over payments to vendors, someone suggested entering explicit StoreID and PO# criteria instead of joining to the query that identified the StoreID and PO# associated with over payments. The suggestion is a:

Question options:

good idea because it avoids an additional join in the query
good idea because it allows users to recognize over payments
bad idea because it maximizes the complexity of the query
bad idea because of the potential for user mistakes
Hide Feedback
Any instance that requires user input creates the potential for user mistakes, which makes the suggestion a bad idea. The more instances of over payments, the more criteria the user would have to specify, increasing the potential for user errors.

Question 2

Which of the following would be the least likely query objective pertaining to the risk of paying for more food items than EatFresh received?

Question options:

verify that order date occurs before invoice date
verify that PO and invoice dollar totals match
verify that PO and invoice item costs match
verify that PO and invoice quantities match
Hide Feedback
The least likely query objective is the one comparing order data and invoice date because it compares neither dollars nor item quantities.

Question 3

The purpose of the criterion “< > 0” on an expression giving the difference between invoice total amount and PO total amount is to select for, i.e., show in the results of the query, rows where invoice total is:

Question options:

greater than PO total
less than PO total
not equal to PO total
equal to PO total
Hide Feedback
The criterion “< > 0” selects for non-zero differences. A criterion of “< > 0” means not equal to zero.

Question 4

To identify differences in quantities ordered and quantities received, EatFresh should examine results from a query based on these tables:

Question options:

POItem and ReceivingSlip
POItem, PurchaseOrder, and ReceivingSlip
POItem, ReceivingSlip, and VendorInvoiceItem
POItem, ReceivingSlip, VendorInvoice, and VendorInvoiceItem
Hide Feedback
Quantity ordered is in POItem. Quantity received is in ReceivingSlip. Because PO# is in both tables, no other tables are needed.

Question 5

Suppose VendorInvoiceItem.Cost values are correct. What tables would be required to compute the dollar amount that EatFresh would be over charged for items it did not receive?

Question options:

ReceivingSlip and VendorInvoiceItem
ReceivingSlip, VendorInvoice, and VendorInvoiceItem
POItem, ReceivingSlip, VendorInvoice, and VendorInvoiceItem
PurchaseOrder, ReceivingSlip, VendorInvoice, and VendorInvoiceItem
Hide Feedback
The quantity received is in ReceivingSlip. The quantity the vendor is charging for and the cost are in VendorInvoiceItem. To link ReceivingSlip and VendorInvoiceItem requires the table VendorInvoice, where ReceivingSlip is linked to VendorInvoice on PO# and VendorInvoice is linked to VendorInvoiceItem on Invoice#.

Question 6

If the following expression is used in a query to find the dollar amount of customer bills that were not paid, the results will show:

custBillNotPaid: [Total]-[Payment Amount]

Question options:

correct values for paid and unpaid bills
correct values for unpaid bills
unpredictable values for unpaid bills
null values for unpaid bills
Hide Feedback
Because it is missing the “Nz” function on Payment Amount, the expression gives null values for custBillNotPaid when Payment Amount is null. The Nz function is required to direct Access to treat null values as zeroes.

Question 7

To find the stores with the largest dollar amount of unpaid customer bills, the best strategy is to:

Question options:

join (right outer) CustomerBill and CustomerPayment, enter an expression for unpaid bills, open the Totals row, and set the expression for unpaid bills to Sum
join (left outer) CustomerBill and CustomerPayment, enter an expression for unpaid bills, open the Totals row, and set the expression for unpaid bills to Sum
join (inner) CustomerBill and CustomerPayment, enter an expression for unpaid bills, set the expression for unpaid bills to Sum, and open the Totals row
join (left outer) CustomerBill and CustomerPayment, enter an expression for unpaid bills, set the expression for unpaid bills to Sum, and open the Totals row
Hide Feedback
To select for all CustomerBills in the result, join (left outer) CustomerBill and CustomerPayment. The Totals row must be opened before the unpaid bills can be summed.

Question 8

A query was created to find all the unpaid customer bills. It is successful in calculating the dollar amount of unpaid bills, but the results show one row for every bill, paid and unpaid. To make the query show only the unpaid bills, the query needs:

Question options:

a Group By function
an Nz function
a criterion
cell formatting
Hide Feedback
The query needs the following criterion set on the expression that calculates the amount of unpaid bills: “< > 0”, which selects bills with a non-zero unpaid amount.

Question 9

Suppose your assistant got query results showing that every customer bill had been paid. Given that you believe that there are some unpaid bills, what would you tell your assistant to do first to the query?

Question options:

Redo the expression for dollar amounts
Reconfigure the join between the tables
Open the Totals row to enable Group By
Set a criterion to eliminate the paid bills
Hide Feedback
The thing to do first to the query is to reconfigure the join between the tables (CustomerBill and CustomerPayment) so that all CustomerBill rows appear in the result. The “results showing that every customer bill had been paid” indicates that the other choices had been implemented correctly in the query.

Question 10

To compare sales revenue with customer bills by day requires an attribute representing the year, month, and day of the sale. The best expression for creating such an attribute from BillTimestamp is:

Question options:

DatePart([BillTimestamp], “yymmdd”)
DateAdd([BillTimestamp], “yymmdd”)
DateSerial([BillTimestamp], “yymmdd”)
Format([BillTimestamp],{"yymmdd"})
Hide Feedback
The function for this purpose is Format([BillTimestamp],{"yymmdd"}), which converts the timestamp, which has hours, minutes, and seconds, into a “yymmdd” format.

Question 1

To find sales revenue exclusive of tax billed by store by day requires which tables?

Question options:

CustomerBill
CustomerBill, CustomerPayment
CustomerBill, CustomerBillItem, CustomerPayment
CustomerBill, CustomerBillItem, CustomerPayment, FoodItem
Hide Feedback
Sales revenue exclusive of tax as billed coincides with the attribute Subtotal in CustomerBill. No other tables are needed.

Question 2

The joins required to determine revenue billed and revenue collected by store are:

Question options:

join (left outer) CustomerBill and CustomerPayment on StoreID; join (inner) CustomerBill

and CustomerPayment on BillID

join (left outer) CustomerBill and CustomerPayment on StoreID;

join (left outer) CustomerBill and CustomerPayment on BillID

join (inner) CustomerBill and CustomerPayment on StoreID;

join (left outer) CustomerBill and CustomerPayment on BillID

join (inner) CustomerBill and CustomerPayment on StoreID;

join (inner) CustomerBill and CustomerPayment on BillID

Hide Feedback

Outer joins are required on StoreID and BillID from CustomerBill to CustomerPayment to show all customer

bills so that collected revenue can be distinguished from revenue billed but not collected.

Two joins are required because the composite primary keys contain two attributes: StoreID and BillID.

Question 3

The best expression for an attribute representing the week that food is received so it can be matched with food already in inventory is:

Question options:

recWeek: (Format(DateAdd({"ww"},2,[ReceivedTimestamp]),{"yymmdd"}))
recWeek: (Format(DateAdd({"d"},2,[ReceivedTimestamp]),{"yymmdd"}))
recWeek: (Format(DateAdd({"ww"},-2,[ReceivedTimestamp]),{"yymmdd"}))
recWeek: (Format(DateAdd({"d"},-2,[ReceivedTimestamp]),{"yymmdd"}))
Hide Feedback
The best choice uses the DateAdd function to substract two days from the date of ReceivedTimestamp, always on Wednesdays. Subtracting two days gives the date of the preceding Monday, which would allow the receipts to be joined to the physical counts of inventory on Mondays. The choices with “ww” would add or subtract weeks rather than days.

Question 4

The best order for the following queries to determine shrinkage costs by week is:

1. Add counts of food items served and wasted and convert the counts to dollar costs
2. Subtract costs for food items served and wasted from costs for purchases and inventory
3. Designate the week for purchases received
4. Add purchases and inventory counts and convert the counts to dollar costs

Question options:

1, 4, 2, 3
2, 3, 1, 4
3, 1, 4, 2
4, 1, 2, 3
Hide Feedback
The week has to be designated for purchases received (query 3) before they can be combined with inventory. Query 2 must be last because it subtracts the costs, giving shrinkage.

Question 5

The purpose of the following expression is to reset the day value of the attribute to the:

dayToMonday: IIf(Weekday([WasteTimestamp])=1,DateAdd({"d"},- 6,[WasteTimestamp]),
(IIf(Weekday([WasteTimestamp])=2,[WasteTimestamp],
(IIf(Weekday([WasteTimestamp])=3,DateAdd({"d"},-1,[WasteTimestamp]),
(IIf(Weekday([WasteTimestamp])=4,DateAdd({"d"},-2,[WasteTimestamp]),
(IIf(Weekday([WasteTimestamp])=5,DateAdd({"d"},-3,[WasteTimestamp]),
(IIf(Weekday([WasteTimestamp])=6,DateAdd({"d"},-4,[WasteTimestamp]),
DateAdd({"d"},-5,[WasteTimestamp]))))))))))))

Question options:

most recent Monday
next Monday
first Monday of the month
last Monday of the month
Hide Feedback
The function of the expression is to reset the day value of [WasteTimestamp] to the most recent Monday, corresponding to the day inventory is taken each week. The reset day values allow food wastes to be joined to other supplies and uses of food items to determine, for example profitability and shrinkage.

Question 6

To calculate the sum of food items served and food items wasted by week by store, the proper join would be:

Question options:

join (inner) from a query with sums of waste quantities to a query with sums of food items served
join (left outer) from a query with sums of waste quantities to a query with sums of food items served
join (right outer) from a query with sums of waste quantities to a query with sums of food items served
join (Cartesian product) from a query with sums of waste quantities to a query with sums of food items served
Hide Feedback
Because the waste table contains only a few rows, the join needs to be a right outer join to include all the rows from the query with sums of food items served.

Question 7

The best approach for reducing losses due to servers voiding customer bills and pocketing the cash would be to:

Question options:

change the system to make it record voided customer bills and analyze them by server
analyze voided customer bills by server by store to identify excessive voiding of bills
establish norms for an acceptable level of voiding and required servers to comply with them
eliminate voiding by requiring customers to pay their bills only with credit or debit cards
Hide Feedback
Because the system does not now record attributes for voided customer bills, the best approach is to change the system to make it record voided customer bills and analyze them by server, a feature that most restaurant systems have. Voiding should be a function of the situation rather than a norm for servers. Making customers pay by credit or debit card is likely to reduce the number of customers.

Question 8

If the prices EatFresh pays to vendors have been increasing, but EatFresh has not kept its costs per serving updated, then profitability analyses based on costs per serving will show:

Question options:

higher or lower profits depending on the food items
accurately stated profits
understated profits
overstated profits
Hide Feedback
Profits will be overstated to the extent that costs per serving are understated relative to actual costs.

Question 9

Suppose the following queries are available that determine costs for food items:

1. In inventory
2. Received from vendors
3. Served to customers
4. Wasted

What is the smallest number of queries required to subtract the sum of food items served and food items wasted from the sum of food items in inventory and food items received from vendors?

Question options:

1
2
3
4
Hide Feedback
Because outer joins are required, Access will only permit joining two queries at a time. Thus, one query is needed to sum food items served to customers and wasted. A second query is needed to sum food items in inventory and received from vendors. A third query is needed to combine the first and second queries.

Question 10

Your assistant has a query intended to group food items wasted by day that shows many more rows than it should. You tell your assistant to:

Question options:

use an Nz function

format an attribute

set a criterion

change Group By

Hide Feedback

Grouping on timestamps that contain hours, minutes, and seconds leads to many rows because the grouping

is by every row with a different timestamp. To fix the problem, format the timestamp to “yymmdd”.

Quiz Submissions - Practice 3 EatFresh

Top of Form

Hao Do (username: hdo8)

Attempt 4

Written: Apr 22, 2018 9:47 PM - Apr 22, 2018 9:48 PM

Submission View

Your quiz has been submitted successfully.

Question 1

CustomerBills are timestamped when the server takes customers’ orders and

CustomerPayments are timestamped when customer payments are recorded.

The tables required to calculate how long customers stay for a meal are:

Question options:

CustomerBill, FoodItem
CustomerBillItem, CustomerPayment
CustomerBill, CustomerBillLineItem
CustomerBill, CustomerPayment
View Feedback

Question 2

To designate food categories, someone suggested using criteria on the FoodID attribute. This is a:

Question options:

good idea because the criteria are easy to implement
good idea because the criteria can be changed easily
bad idea because it cannot be made to work correctly
bad idea because it multiplies the number of queries
View Feedback

Question 3

The EatFresh database has insufficient data for:

Question options:

calculating the dollar amount of shrinkage of food in storage by week
distinguishing between dining-and-dashing and servers pocketing cash
determining whether paleo or traditional diners are more profitable
identifying when vendors increase prices for the food items ordered
View Feedback

Question 4

To determine amounts that have not been paid to vendors, your assistant joined (left outer) VendorInvoice to VendorPayment and summed the amounts from the following expression. This approach:

unpaidAmount: [InvoiceAmount]-Nz([PaymentAmount])

Question options:

gives the correct value for unpaid amounts
has an incorrect join between the tables
overstates the total of unpaid amounts
understates the total of unpaid amounts
View Feedback

Question 5

A query is available that determines profitability by store.

To edit this query to show profitability overall requires:

Question options:

removing an attribute from the query
adding an attribute to the query
adding a criterion to an attribute
removing a criterion from an attribute
View Feedback

Question 6

After seeing weekly values for shrinkage, the managers asked for daily values too.

The best response to this request would be to:

Question options:

edit the queries to replace the week designation with a day designation
create a new query sequence to calculate the daily shrinkage values
compute a shrinkage value for the four weeks and divide by 28 (4 weeks times 7 days)
explain that daily values would simply be one seventh of the weekly values
View Feedback

Question 7

Your assistant proposed a query strategy for determining shrinkage with the

following operations for shrinkage by store, by week, by food:

shrinkage = beginning inventory + purchases – food served – food wasted – ending inventory

If implemented correctly, the query results would show:

Question options:

correct values
understated values
overstated values
understated or an overstated values for shrinkage depending on the circumstances
View Feedback

Question 8

The purpose of creating an attribute with the day (yymmdd)

of PITimestamp and an attribute with the day (yymmdd) of ReceivedTimestamp

set to the same day of the week is to enable:

Question options:

grouping physical inventory and inventory receipts by week
grouping physical inventory and inventory receipts by day
designating inclusive criteria on timestamp attribute values
preserving timestamp attribute values for subsequent analysis
View Feedback

Question 9

Determining the quantity and dollar amount differences between purchase

orders and vendor invoices is a query objective for the risk of:

Question options:

paying for food not received
determining shrinkage by store
detecting food item stock outs
calculating waste food by store
View Feedback

Question 10

Your assistant wanted to know if there was a way to avoid the processing

overhead of using a sequence of queries, each of which had an outer join,

in determining shrinkage. You reply that an outer join would not be

needed to join PhysicalFoodCount and VendorInvoiceItem by week by store

if record counts by store by week from the join are all:

Question options:

not less than the counts in VendorInvoiceItem

equal to the counts in VendorInvoiceItem

equal to the number of rows in FoodItem

equal to the number of rows in FoodItem minus 1

Bottom of Form