Programme overview
Introduction:
Departments often track purchase requests, supplier invoices, stock movements and asset loans in sprawling spreadsheets where rows are duplicated, lookups break and nobody can tell which copy is current. Microsoft Access gives these teams a desktop relational database with linked tables, validated entry forms, saved queries and printable reports, without waiting for a corporate system project. This Core Concept course takes administrative, finance, procurement and inventory staff from table planning to a split, multi-user Access application. Participants build a Purchasing and Inventory Tracking Database for a case department.
Course Objectives:
- Decide when a departmental record-keeping task belongs in an Access database rather than a spreadsheet, and plan its tables and fields
- Build Access tables with suitable data types, primary keys, relationships and enforced referential integrity, including imported and linked Excel data
- Design data entry forms and subforms with validation rules, input masks and combo boxes that keep records clean
- Write select, parameter, totals and action queries with calculated fields to answer routine tracking questions
- Produce grouped reports with subtotals and a navigation form driven by simple macros
- Split, share, back up and protect an Access database used by several colleagues at once
Target Audience:
- Administrative officers who keep request, asset or correspondence registers for their departments
- Finance staff who track invoices, payments and budget holds outside the main ERP
- Procurement staff who log requisitions, purchase orders and supplier performance
- Inventory and stores staff who record goods receipts, issues and stock levels
- Departmental coordinators who have outgrown spreadsheet trackers and look after small in-house databases
Course Outline:
Day 1: Access Fundamentals and Planning a Tracking Database
- Spreadsheet Versus Desktop Database Decision Criteria for Departmental Records
- Access Object Types: Tables, Queries, Forms, Reports and Macros in the Navigation Pane
- ACCDB File Structure, Database Templates and the 2 GB Size Ceiling
- Tracking Requirement Statement and Review of Existing Spreadsheet Trackers
- Subject Table List for Suppliers, Items, Orders and Stock Movements
Day 2: Tables, Keys, Imports and Relationships
- Table Design View: Field Data Types, Field Size and Format Properties
- AutoNumber Primary Keys and Indexed Fields Set to No Duplicates
- Import Spreadsheet Wizard and Linked Tables for Excel, Text Files and SharePoint Lists
- Relationships Window: One-to-Many Links and Junction Tables for Order Lines
- Referential Integrity with Cascade Update and Cascade Delete Options
Day 3: Entry Forms and Queries for Daily Tracking
- Form Wizard, Layout View and Split Forms for Record Entry
- Validation Rules, Input Masks, Default Values and Combo Box Row Sources
- Main Form and Subform Design for Purchase Order Headers and Lines
- Select and Parameter Queries with Criteria, Wildcards and Date Ranges
- Totals Queries, Calculated Fields and Expression Builder Functions
Day 4: Reports, Macros and Multi-User Operation
- Report Wizard, Group and Sort Levels, Subtotals and Grand Totals
- Action Queries: Append, Update, Delete and Make-Table with Backup Safeguards
- Embedded Macros, AutoExec Startup and Navigation Form Menus
- Database Splitter, Shared Back-End Folder and Record-Level Locking
- Compact and Repair, Backup Schedule and Database Password Encryption
Day 5: Lab: Purchasing and Inventory Tracking Database
- Case Department Brief: Supplier, Item, Purchase Order and Goods Receipt Tables
- Order Entry and Goods Receipt Forms with Validation and Subforms
- Stock-on-Hand Totals Query and Reorder Level Parameter Query
- Supplier Spend Report and Reorder Report Grouped by Item Category
- Navigation Form, Split Deployment and Peer Test of the Tracking Database
Skills You Will Gain:
- Tracking Database Planning
- Access Table Design
- Relationship and Integrity Setup
- Validated Form Building
- Access Query Writing
- Grouped Report Design
- Macro-Based Navigation
- Split Database Administration
Why Attend This Course:
- Leave with a working Purchasing and Inventory Tracking Database built in the lab on a case department
- Replace duplicated spreadsheet rows and broken lookups with linked tables that reject orphaned records
- Answer questions such as items below reorder level or spend by supplier with saved queries instead of manual filtering
- Compare tracking practice with administrative, finance and stores staff from other sectors and organisations
Conclusion:
Departmental tracking works when each fact is stored once, entered through a checked form and reported from a saved query. The course moves from the spreadsheet-or-database decision and table planning, through keys, imports, relationships and referential integrity, to entry forms, parameter and totals queries, then grouped reports, action queries, macros, splitting, record locking and backup. The final day is spent in Access, and each participant completes a Purchasing and Inventory Tracking Database ready to adapt for their own department.