Course Aims

• Equip learners to design professional database applications.

• Teach complex query design, advanced form techniques, and data validation.

• Introduce macros and VBA to automate tasks and enhance functionality.

• Help students build polished, user-friendly interfaces for real-world use.

Course Outline

Session 1: Advanced Queries & Data Analysis, Advanced Forms & Conditional Logic

• Recap of select, action, and totals queries

• Crosstab queries for summary tables (e.g., monthly sales by region)

• Union queries to merge data from multiple tables/queries

• Using IIf, Switch, and other functions for custom calculated fields

• Nested queries and subqueries

• Creating forms with tab controls and multiple subforms

• Using conditional formatting based on data values

• Adding calculated controls to display real-time data (e.g., total cost)

• Using unbound forms for menus and dashboards

• Customizing control events (On Click, On Load, etc.)

Session 2: Macros for Automation, Intro to VBA (Visual Basic for Applications)

• Understanding macros: embedded vs. standalone

• Automating common tasks (e.g., open form, apply filter, print report)

• Using data macros to trigger actions when data changes

• Building navigation buttons and message boxes with macros

• Macro safety and troubleshooting

• Understanding the VBA editor and Access object model

• Writing simple VBA procedures for form events

• Using MsgBox and InputBox for user interaction

• Automating data validation with VBA

• Discussing when to use macros vs. VBA

Session 3: Database Optimization & Security, Mini Project – Build a Complete Application

• Splitting the database into front-end and back-end

• Using Compact and Repair tools for maintenance

• Securing objects with user-level permissions (overview)

• Hiding the navigation pane and ribbons for end-user interface

• Best practices: naming conventions and documentation

• Recap of all tools learned

• Mini project: Plan and build a database application that includes:

  • Multi-table structure with relationships

  • Advanced queries and crosstab

  • Forms with macros and conditional formatting

  • Reports with grouping and summary

  • Navigation form or dashboard

• Presentation and peer feedback