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