WIXLIB

Journal of Information Systems Education, Vol. 26(2) Spring 2015Create PurchaseRequisitionCreate & SendPurchase Order toVendorReceiveMerchandise fromVendorReceive Invoicefrom VendorSend Payment toVendorFigure 3.1. Procurement ProcessCreate PurchaseRequisitionCreate & SendPurchase Order toVendorReceiveMerchandise fromVendorReceive Invoicefrom VendorSend Payment toVendorFigure 3.2. Fulfillment ProcessProcurement activities relate to acquiring inventorywhile fulfillment activities relate to selling inventory,including recording merchandise movement as well asfinancial transactions. It should be noted that the currentNorthwind database is not configured to record financialtransactions because it does not provide the design orstructure required to fulfill most financial or managerialaccounting requirements, e.g., tables to record yable/receivable, expenses, etc., or reports for incomestatements and balance sheets, etc.The basic Northwind business processes includeemployees purchasing products from suppliers, placing themin inventory, and reselling to customers. To purchaseproducts from suppliers, an employee submits a purchaseorder to the supplier. Following receipt of the orderedproducts, the products are added to inventory. To sellproducts to customers, an employee creates a customerorder. If the products are not in inventory, an employee caninitiate a purchase order to fill the customer order. Once thecustomer order is filled, the order is invoiced and shipped.The shipped products are removed from inventory.Throughout the remainder of the paper it is suggested thatthe reader open and view the Northwind database in Access.4. NORTHWIND TABLESThe various tables in Northwind can be described as Master,Supporting, and Transaction. Master tables are those thatrecord data that does not change frequently, like employees,vendors, products, customers, and shippers. By “does notchange frequently” means that while new records may beappended to master tables, the existing records are seldomedited. A typical new record many include a new employee,supplier, or product, etc. A typical record edit might includeediting an employee’s address or a supplier’s contact person.Supporting tables, also known as organizational tables, arethose that typically support master or transaction tables, andlike master tables, the data changes infrequently. Examplesinclude lookup table links to master tables, like anemployee’s department, title, state of residence, or acustomer’s tax status or tax rate.On the other hand, transaction tables tend to involvefrequent adding of new process transaction records, likerecording procurement and fulfillment transactions related tomovement of inventory and money. Examples includecreating new purchase orders, recording new inventory, orposting customer payments. Transaction tables can also oftenexperience editing of existing records, like changing aninvoice payment status field from unpaid to paid. Often, a87master table will also be a supporting table for a transactiontable, which is a typical one-to-many (1:M) relationshipbetween the master and transaction table. For example, acustomer ID from the master table CUSTOMERS will belinked to the ORDERS transaction table to record thecustomer for which the order was placed.Figure 4.1 reflects the complete entity relationship model(ERM) for the Northwinds database, including primary keys,while figures 4.2 and 4.3 reflect the entity relation diagrams(ERDs) for both the complete procurement and fulfillmentprocesses, respectively. Note that Figure 4.1 omits two tables(EMPLOYEE PRIVILEGES and PRIVILEGES) for thesake of space constraints, while the ERD relating the twoomitted tables to the EMPLOYEES table is shown in Figure5.1. The ERM shown below has been modified for clarity bythe authors (only in appearance) since the default ERM inAccess is a near indecipherable entanglement of tables andrelationships. Note also that, inasmuch as possible, themodified ERM places tables on the “1” side of a 1:Mrelationship on the left, and tables on the “Many” side of therelationship on the right, with the exception of thePRODUCTS table (on the far right).The ERDs are more readily representative of the twoprimary processes without entanglement of the entire ERM.Even Best STL (An Essential Guide to Using the NorthwindDatabase in Access 2010, 2010) related that the ERM “maygive the impression of a tangle of tables and links resemblingspaghetti,” hence the ERM is broken into separate ERDsthroughout this paper. Appendix A, Tables 1 and 2, describethe database tables in the Northwind database. Table 1 listseach database table, the primary key(s), as well as a table’srelationship with other tables, like 1:1 or 1:M. Table 2summarizes master, transaction, and supporting databasetables.Since purchasing and selling products are the company’sprimary activities, six very important transaction tablesfollow. Note that table names are formatted as UPPER lettercase and bold font-style, while attribute/field names areformatted in italics. The six transaction tables are thenPRODUCTS, PURCHASE ORDERS, PURCHASEORDER DETAILS, ORDERS, ORDER DETAILS, andINVENTORY TRANSACTIONS. The details for eachproduct that can be purchased and sold are reflected inPRODUCTS. When the company purchases products, thepurchase summary is recorded in PURCHASE ORDERSwhile the items ordered are recorded in PURCHASEORDER DETAILS. When products are sold, the salessummary is recorded in ORDERS while the items orderedare recorded in ORDER DETAILS. When inventory isreceived or shipped, the transaction is recorded in

Journal of Information Systems Education, Vol. 26(2) Spring 2015INVENTORY TRANSACTIONS. It should be noted thatthere is no direct end-user interaction with the tables, butinstead, forms are used to add, update and delete records inunderlying tables, via direct link to the tables or throughmulti-table queries.Figure 4.1. Northwind ERMFigure 4.2. Procurement ERDFigure 4.3. Fulfillment ERD88

Journal of Information Systems Education, Vol. 26(2) Spring 2015The following section describes the tables andrelationships for the procurement process (Section 5) and thefulfillment (Section 6). Appendix A, Table 3, enumeratesand describes the business rules, including relationships (1:1,1:M, M:N) and participations; optional versus mandatory.Appendix A, Table 4, provides relevant notes whereindicated in Table 3. Figures are also provided depicting theappropriate ERD connectivities for each business rule.5. PROCUREMENT TABLES5.1 Employee PrivilegesEMPLOYEE PRIVILEGES is a bridge/composite entityrecording an employee with a specific privilege, hence in thetable an employee may have many privileges, and a privilegemay be granted to many employees, but each employeeprivilege composite will be a separate record in the table.Figure 5.1 reflects the ERD for business rule 1.5.2 Purchase OrdersPURCHASE ORDERS reflects a summary of eachpurchase order, but not the products purchased. Instead, thedetails of each product on a single purchase order arerecorded in one or more records in PURCHASE ORDERDETAILS. A single purchase order initiated by an employeemay generate an order for many different products from asingle supplier, with each product recorded as a separaterecord in PURCHASE ORDER DETAILS. Figure 5.2reflects the ERD for business rules 2, 3 and 4.5.3 Purchase Orders DetailsPURCHASE ORDER DETAILS records every productordered on every purchase order. For example, if a singlepurchase order has 3 products, PURCHASE ORDERDETAILS will have 3 records; one for each product orderedon the single purchase order. Figure 5.3 reflects the ERD forbusiness rules 5 and 6.5.4 Inventory Transactions (for purchase orders)INVENTORY TRANSACTIONS records all productspurchased and sold, as well as products on-hold to fillcustomer backorders, and products that are not available forsale due to being waste. When the products from a singlepurchase order are received into inventory, each individualproduct is recorded in INVENTORY TRANSACTIONS,and is assigned a Transaction ID, the Transaction Type(‘Purchased’) from INVENTORY TRANSACTIONTYPE, and a Product ID from PRODUCTS. Note that inthe illustrative table the Purchase Order ID attribute is blankfor all records, but new purchase orders entered using theappropriate form will record the Purchase Order ID. This isnot necessarily an error, but likely due to Microsoft simplynot including the data when populating the table, as is thecase in several other tables. Figure 5.4.1 reflects the ERM forbusiness rules 7, 8 and 9, while Figure 5.4.2 reflects the ERDfor business rule 10.Figure 5.1. Employee PrivilegesFigure 5.2. Purchase OrdersFigure 5.3. Purchase Order Details89

Journal of Information Systems Education, Vol. 26(2) Spring 2015Figure 5.4.1. Inventory TransactionsFigure 5.4.2. Purchase Order Detail6. FULFILLMENT TABLES6.1 OrdersORDERS reflects a summary of a customer order, but notthe products ordered. Instead, the details of each product ona single customer order are reflected in one or more recordsin ORDER DETAILS. A single customer order.6.2 Order DetailsORDER DETAILS reflects every product ordered on everycustomer order. For example, if a single customer order hasthree products, ORDER DETAILS will have 3 records; onefor each product ordered on the single customer order.Figure 6.2 reflects the ERD for business rules 16, 17 and 18.6.3 InvoicesINVOICES reflects every customer order that has beeninvoiced. The table records only those orders that have beeninvoiced (using the appropriate form). INVOICES willrecord an Invoice ID and Invoice Date, and the related OrderID from ORDERS. Note that INVOICES has additionalfields for Due Date, Tax, Shipping (fee) and Amount Due,but all fields are null in the table and are never updated withany form or query. Note, one would assume that an invoiceis similar to a packing slip and that it would only begenerated prior to order shipping. Yet, there are severalorders in ORDERS that have shipped but are not recorded inINVOICES, and there are several unpaid and unshippedorders that do have an invoice. This is likely another error inpopulating the database with illustrative data. Figure 6.3reflects the ERD for business rule 19.6.4 Inventory Transactions (for orders)For customer orders, INVENTORY TRANSACTIONSrecords all products ‘sold,’ as well as products ‘on-hold’ tofill customer backorders. When a customer order is filled,each product on the order is removed from inventory, and isassigned a Transaction ID and Transaction Type (‘Sold’).The Transaction ID is also recorded in ORDER DETAILS.Figure 6.4 reflects the ERD for business rules 20, 21 and 22.Figure 6.1. Orders90

Journal of Information Systems Education, Vol. 26(2) Spring 2015Figure 6.2. Order DetailsFigure 6.3. InvoicesFigure 6.4. Inventory Transactions7. NAVIGATING NORTHWINDA Startup Screen form is loaded when the database isopened, prompting the user to click the Security WarningEnable Content button. The Login Dialog form (Figure 7.1)appears after clicking the Enable Content button. The formrequires selecting an employee name from the combo box(drop-down list) and clicking the Login button. It isimportant to note that after pressing the button an embeddedmacro runs that captures the selected employee’s ID andprivileges and sets it to a temporary variable which is usedthroughout the processes to allow privileges and to auto-fillform fields with the employee data. Note that the employeenamed ‘Andrew Cencini’ has purchase approval privilegesaccording to EMPLOYEE PRIVILEDGES, but none ofthe other eight employees have been assigned privileges,perhaps a design error. The records in EMPLOYEE reflectthat all employees are associated with sales. Following login,another embedded macro opens the Home form (Figure 7.2)providing access to objects such as hyperlinks to create aNew Customer Order or New Purchase Order, Quick Linksto tables, queries, forms and reports, and embedded queryresults within subforms, such as Active Orders andInventory to Reorder. The object navigator is also availablein the left pane, providing access to all database objects,although it is collapsed when the database opens.Figure 7. 1. Login Dialog Form91

Journal of Information Systems Education, Vol. 26(2) Spring 2015Figure 7.2. Home FormThe following sections will postulate assumed businessprocesses for the Northwind procurement and fulfillmentprocesses, based on the previously established business rules,relationships and participations. It is recommended that thereader become familiar with each object (tables, queries,forms, reports, macros, modules) involved in every processcomponent, as well as the object relationships anddependencies. All business processes are completed usingforms and subforms, which depend on underlying tables,queries, embedded macros and VBA modules. The userseldom interacts directly with objects other than forms andreports. Instead, form data is read from and written to tablesand/or queries, hence updating related and dependentobjects. Additionally, embedded macros and VBAsubroutines are directly related to many form actions, e.g.,assigning an employee ID (based on login) to forms, ortriggering a purchase requisition creation/submission when acustomer order for a product exceeds inventory quantity.Access provides a tool for examining how objectsinteract with (or depend on) other objects. The tool isavailable in the menu tab ‘Database Tools’ inside ependencies.’ Becoming familiar with database objects canmake it easier to perform a wide variety of tasks, such asentering data into a form, adding or removing tables, findingand replacing data, and running queries.To better understand object dependencies the interestedreader should see the reference, Learn the Structure of anAccess Database (2007). For example, by clicking onINVOICES and using the ‘Object Dependencies’ tool, onecan determine that INVOICES interacts with (and dependson) data from ORDERS, while the SALES ANALYSISquery depends on INVOICES. Similarly, the PurchaseOrders Detail form depends on tables PURCHASEORDER STATUS and PURCHASE ORDERS, queriesEMPLOYEES EXTENDED, PURCHASE DETAILSEXTENDED, and SUPPLIERS EXTENDED, and formsEmployee Details, Purchase Subform for Purchase OrderDetails, Receiving Subform for Purchase Order Details,and Supplier Details. The objects that depend on thePurchase Orders Detail form are the forms Home andPurchase Order List. Unfortunately, the tool does not showdependencies on macros or VBA modules. Additionally, onecan use the built-in documenter tool from the menu tab‘Database Tools’ inside the ‘Relationships’ group, byclicking on ‘Database Documenter.’ This will provide thedescription and schema of each object in the database. Thereader is encouraged to examine object dependencies for allNorthwind objects.8. PROCUREMENT PROCESS NAVIGATIONAlthough the actual Northwind procurement process isundocumented, the assumed process is described in thefollowing procurement Steps, 8.1 to 8.7 (below). Note thatform and subform names, as well as form tabs, buttons andlinks, are Proper letter-case, and bold and italicized fontstyle. Attribute/field names are italicized, field data valuesare surrounded in ‘single quotes,’ table names are asaforementioned, while query names are UPPER letter-case,and bold and italicized font-style. An overview of theNorthwind procurement process steps is shown in Figure 8.1below.8.1 TriggerA purchase order requisition is triggered by a reorder levelrequirement or insufficient inventory (view the tablePRODUCTS, and the two forms Inventory List andInventory to Reorder Subform for Home). This might occurafter filling a customer order that depleted current inventorybelow the reorder level, or a new customer order for whichcurrent inventory is insufficient. Following login, the Homeform Inventory to Reorder Subform for Home subformdisplays query results showing which products have beentriggered to be reordered. Clicking on a hyperlink in thesubform opens the Product Details form to determine theappropriate supplier and reorder quantity. Note that most ofthe product’s attribute values can be updated directly in theform.Additionally, the Order/Purchase History tab reflectsthe sales and purchases transactions of the product using thesubform Product Transactions Subform for ProductDetails. One can also view other products by clicking the Goto Product drop-down, as well as save changes and create anew product using the Save and New form hyperlink. Theform does not show the actual quantity that needs to beordered. Instead, the Inventory List form shows the exactquantity to order for every product. The Inventory List formisavailableintheleftnavigationpane.Figure 8.1 Procurement Process Steps92

Journal of Information Systems Education, Vol. 26(2) Spring 20158.2 Purchase Order RequisitionUsing the Purchase Order Details form, an employeecreates a new purchase order requisition. This form isavailable from the Home form hyperlink named NewPurchase Order. For existing orders that have been created,submitted or approved, the Form Header section Status fieldis populated from an embedded ‘Select’ query set as theRecord Source (see the form Property Sheet). For neworders, the Created By employee ID field is populated usingthe default value of the employee ID (set on login), while theCreation Date field is a default value using a built-in Now()function. The form requires selecting a Supplier from thedrop-down list, populated from the SUPPLIERSEXTENDED query. After a supplier is selected and productsare added, a new Purchase Order ID (AutoNumber) iscreated in PURCHASE ORDERS. The form data areinserted into PURCHASE ORDERS, including SupplierID, Created By employee ID, Creation Date, and a Status IDset to ‘New’ (which is the default value).After selecting the subform tab Purchase Details(Purchase Subform for Purchase Order Details subform),the subform requires selecting a Product from a drop-downlist, populated from PRODUCTS, and entering an orderquantity (Qty). The subform creates a new record inPURCHASE ORDER DETAILS with in a new ID(AutoNumber) and inserts the corresponding Purchase OrderID and the Product ID into PURCHASE ORDERDETAILS. Note that PURCHASE DETAILS EXTENDEDis based off of PURCHASE ORDER DETAILS andPURCHASE ORDERS, and the subform fields Unit Costand Total Price are populated from calculated fields inPURCHASE DETAILS EXTENDED. Additionally, theUnit Cost value can be changed to reflect a Standard Costthat is different from the value stored in PRODUCTS. Itshould be noted that once a product is selected, the Productcan be changed, as well as the Qty and Unit Cost, but theproduct cannot be deleted. Instead, the purchase order can becanceled after submitting for approval (Step 8.3).For each product selected in the subform, theINVENTORY ON ORDER (query fields (Product and/orQuantity on Order) are updated, although the purchase orderhas not yet been submitted or approved. The INVENTORYfield Qty on Order is also updated, hence affecting the QtyBelow Target, Current Level, and Qty To Reorder fields. Theform can be closed at this point, or immediately submittedfor approval. Before submitting for approval, either or boththe Product and Qty can be changed, and the fields arecorrectly updated in the related tables and queries.Once the requisition is created, closed and reopened, theexisting form fields are populated from PURCHASEORDERS, while the subform is populated fromPURCHASE DETAILS E

2010). Additionally, the database can be downloaded for SQL Server 2000 from Microsoft (Northwind and Pubs Sample Databases for SQL Server 2000, 2010) and for SQL Server 2005 and 2008 from Codeplex (Northwind atabase, D 2011). For those unfamiliar with Access, a great tutorial resource has been provided by GCF Global (Free