Databases - MS Access - Inventory Control Demo
MS Access provides a number of demo databases.
I stongly suggest using these as learning aids.
This page discusses the pros and cons of the
(Caveat: The analysis presents my opinions,
not facts.
Others may disagree.)
Creating the Demo
From the menu, select
File / New Database... / Databases / Inventory Control.mdz
You will be presented with a list of possible tables
and optional fields.
You can specify the basic form and report styles.
When you select Finish, the wizard will create
| 9
| Tables
|
| 11
| Forms
|
| 5
| Reports
|
On my system, the mdz files are located at
C:\Program Files\Microsoft Office\Templates\Databases
Overview
If you elected to have sample data, you can
see how it all plays together.
Right click on the list of tables and select Relationships...
(or use Tools / Relationships...).
This will display the links between the 7 main tables.
(One additional table controls the switchboard
and the other stores company informatiion.)
Navigation
Application navigation is based on the Switchboard.
Basically, this is a data-driven menu system -
the menu text and related forms are stored in
the Switchboard Items table.
With very little effort, you can customize this for other applications
(mainly, get rid of the hideous graphic).
There are several command types
| 0
| Heading
| Identifies a section in the table, but it is not displayed.
Also used to define the default page.
|
| 1
| Change Menu Options
| This displays a different set of options
|
| 2
| Form
| Open the form in add mode
|
| 3
| Open Form
|
|
| 4
| Open Report
|
|
| 5
| Edit Options
| This displays an application to edit the available options -
if it is loaded. (It actually works pretty good.)
|
| 6
| Exit
| This closes the application
|
| 7
| Run Macro
|
|
| 8
| Run Code
|
|
As it stands, the switchboard handles up to 8 selections.
It also calls a start-up form the first time it is executed.
I personally prefer menus to buttons
(faster, easier to see the total picture),
but these should also be data-driven.
Analysis
- The Purchase Order table should be a master/detail.
- SerialNumber should not be a part of the Products table.
- Units Ordered and Unit Price
should be part of Purchase Order Items,
not Inventory Transactions.
The concept of an Inventory Transaction record is a good idea,
however, several of the fields should be combined into a pick list.
- From vendor
- To work order
- Shrinkage
- Number of units (can be plus or minus)
- Serial Number (the manufacturer assigns this)
- Tracking number (we assign that)
The requirement to have individual tracking numbers for all items
greatly modifies the data architecture.
More
parts - general description
items - one record per item, unique id, may have serial number
has part number, Purchase Order, testing record, repair,
outgoing work order - each item has many disposition records
Author: Robert Clemenzi -
clemenzi@cpcug.org