Write a Visual Basic program that uses the three tables to do the two tasks.

timer Asked: Jun 29th, 2014

Question description

Adapted from Introduction to Programming Using Visual Basic 2012, 9/E, David I. Schneider.

The database 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:

1.  Display in a listbox the items that are out of stock and those that must be reordered to satisfy today’s orders.

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

1.  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 itemIDs.

2.  Use a for-each loop to go through query1 once per itemID.  For each loop you want to do the following:

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

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

c.  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 description.

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


Tutor Answer

(Top Tutor) Studypool Tutor
School: Cornell University
Studypool has helped 1,244,100 students
flag Report DMCA
Similar Questions
Hot Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors