Adapted from Introduction
to Programming Using Visual Basic 2012, 9/E, David I. Schneider.
Microland.accdb is maintained by the Microland Computer Warehouse, a mail-order
computer-supply company. The tables below show data in the three tables in the
database. The table Customers identifies each customer by an ID number and
gives, in addition to the name and address, the total amount of purchases during
the current year prior to today. The
table Inventory identifies each product in stock by an ID number and gives, in
addition to its description and price (per unit), the quantity in stock at the
beginning of the day. The table Orders gives the orders received today. Assume
that it is now the end of the day. Write a Visual Basic program that uses the three tables to do the following two tasks:
Display in a listbox the items
that are out of stock and those that must be reordered to satisfy today’s
Display in a listbox bills for
all customers who ordered during the day. Each bill should show the customer’s
name, address, items ordered (with costs), and total cost of the order.
Design your form based on
the diagram below.
The database that contains
the tables for this assignment is contained within your Instructions folder and
should be placed in your project’s bin\debug folder.
Below are the data that are
contained in the tables in the database.
(Note: you do not have to
reproduce these tables in your Visual Basic program. You should only create the
screens shown above.)
Hints – the hints below are
for the “Out of Stock” button function and will hopefully give you some clues
as to how you can accomplish the other button’s work.
Create a query1 from the order
table to get the itemIDs ordered. You
will want to order your query by itemID and use Distinct to eliminate duplicate
Use a for-each loop to go
through query1 once per itemID. For
each loop you want to do the following:
Create a query2 using the order
table Where the itemID in the order table equals the itemID from query1. The purpose of this query is to get the
quantities ordered for each itemID.
Use a for-each loop and a
variable (let’s call it intTotalOrdered) to iterate through query2 and sum up
the quantities ordered for each itemID.
Create a query3 using the
inventory table and select those records Where the intTotalOrdered is equal to
or greater than the quantity on hand – these are the records that will need to
be reordered. Use a Let statement with
quantity on hand and intTotalOrdered to determine the reorder quantity. Select the itemID, reorder quantity, and item
Print out itemID, reorder
quantity, and item description to your list box. Use Math.Abs() on your reorder quantity to
convert negative numbers to positive ones.