Programming practice quiz 10

Question 1

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

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

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

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

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

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

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 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

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

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

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

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

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

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

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

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

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

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

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

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.

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:

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

Question options:

The EatFresh database has insufficient data for:

Question options:

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:

A query is available that determines profitability by store.

To edit this query to show profitability overall requires:

Question options:

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

The best response to this request would be to:

Question options:

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:

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:

Determining the quantity and dollar amount differences between purchase

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

Question options:

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:

Bottom of Form