Your First Access Project: Order Tracker

The Scenario

Build a small order tracker: Customers, Orders, Products and OrderLines. This ties together every concept from the course.

1. Tables

Customers:  CustomerID (AI PK), FirstName, LastName, City, Email
Products:   ProductID (AI PK), ProductName, UnitPrice
Orders:     OrderID (AI PK), CustomerID (FK), OrderDate
OrderLines: LineID (AI PK), OrderID (FK), ProductID (FK), Qty, Price

2. Relationships

• Customers 1-< Orders (CustomerID)
• Orders 1-< OrderLines (OrderID)
• Products 1-< OrderLines (ProductID)

Enforce referential integrity on all three.

3. Queries

qrySalesByCity:  City | Sum(price*qty)  -- totals, grouped
qryOpenOrders:   OrderID, Customer, Total, Status  -- with criteria Is Null(ShippedOn)

4. Forms

• frmCustomers - bound form with a subform of that customer’s orders.
• frmOrders - with frmOrderLines subform; the line combo lists products; Price defaults via a lookup.
• All add/edit happens on friendly forms - datasheet stays locked for admins only.

5. Report

rptOrders - grouped by OrderID listing lines with a grand total; footer expression:

=Sum([Qty]*[Price])

6. Automate

• Startup: File → Options → Current Database → Display Form: frmCustomers.
• Button on reports to open in print preview.
• Validation: OrderDate default Date(), Qty > 0.

Key Points

• Plan tables and relationships before building forms.
• Parent/child forms via subforms mirror the real world.
• Small end-to-end projects teach Access faster than any single chapter.