Transaction Processing Assignment Guide

This guide is a"dummy" version for external users of the Access tutorials. All those enrolled in a course should access their particular assignment guide through the course home page.

This document provides you with the details of the transaction processing system that you will implement in Microsoft Access. See the on-line tutorials for more information on specific skills.

Last Updated: 12 Sep 2000

Introduction

The purpose of this assignment is to give you experience developing a simple transaction processing system. The assignment itself is structured as a simulation: you are provided with a simple scenario, the initial state of the system, and a number of input transactions (six orders from your customers and two shipments from your suppliers). Your task is to create an information system that stores inventory information, keeps track of backorders, and creates the necessary output transactions (customer invoices).

Scenario

You and your partner run a small, but growing, company (you should provide it with a name) that distributes kitchen supplies. Your customers are primarily small retailers such as kitchen specialty stores, hardware stores, and so on.

You currently keep track of all your customers, orders, and inventory using a spreadsheet. Although this works reasonably well, there are two major problems:

  1. With your current system, you create all your invoices by cutting and pasting from your spreadsheet. This is error prone and tedious.
  2. You often lose track of your backorders and forget to fill them. When your customers phone you to inquire about their backorders, you spend a great deal of time tracking down the information you need and making excuses.

As a result, you are interested in building a small transaction processing system that will allow you to input your orders, track your inventory, and automatically generate invoices. There are other functions that you want to add later but you have decided to move slowly since you do not have a great deal of experience with database software.

You have decided to use Microsoft Access to develop your application. Although you examined other packages, you chose Access because it was reasonably priced (it was bundled in an office suite) and because the reviews you read were favorable.

Assignment Package

You are provided with the following information and documents:

  1. You took a complete inventory as of the first of the month and copied this information into an Excel spreadsheet. This file is available via the Internet at:
    http://mis.bus.sfu.ca/tutorials/MSAccess/assignment/inventor.zip
  2. You have gone through all your old orders and created a list of the outstanding backorders as of the first of the month. This list can be downloaded from:
    (http://mis.bus.sfu.ca/tutorials/MSAccess/assignment/backship.pdf)
  3. Your sales representative faxes you sales orders every couple of days. Orders are normally filled and invoiced in the sequence that they arrive. Backorders are recorded and filled (if possible) on the next order from that customer. The orders you receive can be downloaded from:
    http://mis.bus.sfu.ca/tutorials/MSAccess/assignment/orders.pdf)
  4. You have the details of two shipments that arrive at your warehouse on the 15th and 25th of the month. This information can be downloaded from:
    http://mis.bus.sfu.ca/tutorials/MSAccess/assignment/backship.pdf)

Items 2-4 are included in the printed assignment package. Note that the files available on the Internet have been "zipped" (compressed to save space and download time) and must be "unzipped" before you can use them. Alternatively, you can download the non-zipped versions from the Commerce PC lab using the "copy course data" feature.

In the latter stages of the assignment, you may choose to use a "shortcut" for implementing the backorder management aspect of the system. For this, you will need one of the following:

Requirements

You are required to create a transaction processing system with the functionality described in the requirements table. Since you may eventually hire someone to take care of order entry for you, you have to make the system as user-friendly as possible. All actions are initiated through a "switchboard" interface. The user is not assumed to have any understanding of the underlying database structure or any knowledge of Access apart from general Windows commands.

View the table of requirements

Design Issues

  1. As is the case with any complex software, there are many different ways of accomplishing the same thing in Access. The following is a list of design issues that we recommend that you keep in mind.
  2. When in doubt, make the assumption that will result in the least amount of work. Do not get caught up on "what if the user does this?" issues. Stick to the requirements and assume that your users are perfect.
  3. Intelligent table and relationship design is the key to the whole project. Do not waste time on purely cosmetic things like perfect alignment, gratuitous clip-art, and so on.
  4. Push all features to the lowest level:
    1. make use of the "inherited" properties such as captions and formats to minimize the time you spend editing forms and reports
    2. use relationships, validation rules, and input masks to reduce the amount of coding required to enforce data and referential integrity
  5. Use the report and form wizards but do not waste your time with the table or query wizards.
  6. When possible, use action queries instead of procedural code. Queries are easier to create and maintain.
Questions, queries, comments to Michael Brydon (brydon@unixg.ubc.ca)