Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 36 ยท Projects and Practice

Project: Order Management in Access

Build an order management database in Access with customers, products, orders, order lines and stock. Calculate totals and invoices, update stock with VBA and report on sales.

The second project is the classic business application: customers place orders for products, the business ships them, bills them and watches its stock. It combines a many-to-many relationship (orders and products), calculated totals that must never be stored by hand, stock movements, an invoice report and a monthly sales summary. Where the first project (a library) was about status, this one is about money and quantities.

Design decisions

Decision Choice and reason
Many-to-many OrderLines junction table between Orders and Products
Price history OrderLines.UnitPrice copies the product price at sale time so old invoices never change
Totals Not stored; calculated in queries: Qty * UnitPrice * (1 - Discount)
Stock Products.StockOnHand is changed only by stock movements (StockMovements table), so every change has a reason and a date
Statuses A lookup table OrderStatuses (New, Packed, Shipped, Cancelled)
Money Currency data type everywhere; tax rate in a settings table

In Access: set OrderLines primary key as the composite (OrderID, ProductID), or add an AutoNumber LineID if the same product may appear twice. Relationships with integrity on; cascade delete from Orders to OrderLines is acceptable here because a line has no meaning without its order (but you will cancel orders rather than delete them).

Build the schema

SQL Orders schema with sample data

Press Run (or Ctrl + Enter) to see the result here.

Calculated totals and the invoice query

An invoice is a query joining customer, order, lines and products. In Access, add a calculated column in the query grid: LineTotal: [Quantity]*[UnitPrice]*(1-[Discount]). Group by order for the totals, and add tax from the settings table.

TEXT
SELECT o.OrderID, c.Name, Sum(l.Quantity * l.UnitPrice * (1 - l.Discount)) AS Subtotal,
       Sum(l.Quantity * l.UnitPrice * (1 - l.Discount)) * 1.2 AS TotalWithTax
FROM (Orders AS o INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID)
INNER JOIN OrderLines AS l ON o.OrderID = l.OrderID
GROUP BY o.OrderID, c.Name;

Use CCur(...) or Round(..., 2) so that totals have two decimals. Here are the invoice lines for order 2, then every order's totals (tax rate 20 percent):

SQL Invoice lines and order totals

Press Run (or Ctrl + Enter) to see the result here.

Sales analysis queries

Typical management questions, each a saved query that also feeds a chart or a report: sales by month (Format([OrderDate], "yyyy-mm") in Access), top customers, best-selling products, and products to reorder (stock at or below the reorder level):

SQL Monthly sales, top customers and reorder list

Press Run (or Ctrl + Enter) to see the result here.

Keeping stock correct

Stock is a classic place for silent errors. Never let a form edit StockOnHand directly. Instead record movements (+ receipts, - sales, adjustments) and derive the balance, or update the stock in the same transaction that saves the order line. In Access, use a data macro After Insert on OrderLines, or VBA on the form; the version with a DAO transaction is explicit and testable:

VBA
Public Function ShipOrder(lngOrderId As Long) As Boolean
    Dim ws As DAO.Workspace: Set ws = DBEngine.Workspaces(0)
    Dim db As DAO.Database: Set db = CurrentDb
    Dim rs As DAO.Recordset

    On Error GoTo Failed
    ws.BeginTrans

    ' Refuse the shipment if any line exceeds stock
    Set rs = db.OpenRecordset( _
        "SELECT p.Name FROM OrderLines AS l INNER JOIN Products AS p ON l.ProductID = p.ProductID " & _
        "WHERE l.OrderID = " & lngOrderId & " AND l.Quantity > p.StockOnHand", dbOpenSnapshot)
    If Not rs.EOF Then
        MsgBox "Not enough stock for " & rs!Name, vbExclamation
        rs.Close: ws.Rollback: Exit Function
    End If
    rs.Close

    db.Execute "UPDATE Products AS p INNER JOIN OrderLines AS l ON p.ProductID = l.ProductID " & _
               "SET p.StockOnHand = p.StockOnHand - l.Quantity " & _
               "WHERE l.OrderID = " & lngOrderId, dbFailOnError
    db.Execute "UPDATE Orders SET Status = 'Shipped' WHERE OrderID = " & lngOrderId, dbFailOnError

    ws.CommitTrans
    ShipOrder = True
    Exit Function
Failed:
    ws.Rollback
    MsgBox "Could not ship the order: " & Err.Description, vbCritical
End Function

The check-then-update logic works in plain SQL as well. This SQLite version proves a shipment cannot drive stock below zero by using the same guard:

SQL Guarded stock update in one transaction

Press Run (or Ctrl + Enter) to see the result here.

The CHECK (stock_on_hand >= 0) rule is a last line of defense: even if a form forgets to test, the database refuses a negative stock.

Forms and reports

  • frmOrder: header bound to Orders (customer combo, date defaulting to Date()) and a continuous subform sfrmOrderLines bound to OrderLines. Choosing a product in the line combo fills UnitPrice in the combo's AfterUpdate with Me.UnitPrice = Me.cboProduct.Column(2) (the price column of the combo's row source). A text box in the subform footer shows the total: =Sum([Quantity]*[UnitPrice]*(1-[Discount])), and the main form refers to it as =[sfrmOrderLines].Form![txtTotal].
  • rptInvoice: based on the invoice query, grouped by OrderID with a group header (customer, date) and group footer (subtotal, tax, total). Set Keep Together so an invoice does not break across pages and export it to PDF as in exporting data.
  • rptSalesByMonth with a column chart bound to the monthly query, and a dashboard form with reorder warnings (conditional formatting: red when StockOnHand <= ReorderLevel).

Testing checklist

Enter an order with two lines; check that totals match a calculator. Change a product price afterwards and confirm old invoices do not move. Try to ship more than the stock. Try deleting a customer with orders (blocked). Open the app as two users and edit the same order (see multi-user). Finally back it up.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why copy UnitPrice into OrderLines?

    The line stores the price actually charged, giving a correct history.
  2. 2. Where should an order's total be computed?

    Derived values should be calculated so they cannot disagree with the lines.
  3. 3. Why ship an order inside a transaction?

    A transaction prevents half-finished shipments if an error occurs.
  4. 4. What does a CHECK (stock_on_hand >= 0) rule add?

    The rule lives in the table, so every code path must obey it.

Summary

  • Orders and products are many-to-many: use OrderLines with the price at sale time.
  • Compute totals, tax and invoices in queries; never store them by hand.
  • Change stock only through guarded, transactional updates; add table rules as a backstop.
  • Build an order form with a subform, an invoice report, sales and reorder queries, and test with realistic cases.

Next: Access SQL practice set