BMIS 208 Programming Assignment 9 Microland Computer Warehouse

Description

BMIS 208 Programming Assignment 9 Microland Computer Warehouse

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

This assignment is due by 11:59 p.m. (ET) on Monday of Module/Week 6.
Adapted from Schneider, D. I. (2010). Introduction to Programming Using Visual Basic 2010 (8th ed.).

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.