Microsoft Access Database Design and Implementation: A Complete Project Tutorial
Document Type: Project-Based Tutorial / Case Study
Estimated Length: 3,000-3,500 words
Table of Contents
1. Project Overview: Carol's Travel Club Database
2. Phase 1: Requirements Analysis
3. Phase 2: Database Design and Normalization
4. Phase 3: Creating Tables and Relationships
5. Phase 4: Data Population Strategies
6. Phase 5: Query Design for Business Intelligence
7. Phase 6: User Interface Development with Forms
8. Phase 7: Professional Report Creation
9. Phase 8: Automation with Macros
10. Phase 9: Security and User Management
11. Phase 10: Deployment and Maintenance
12. Project Deliverables Checklist
13. Sample Data Sets
14. Troubleshooting Guide
1. Project Overview: Carol's Travel Club Database
1.1 The Business Scenario
Carol is a young entrepreneur who recently started Carol's Travel Club, a business that
organizes group travel experiences for members. Starting this business has required Carol to
be efficient with her limited resources. She currently tracks customers, trips, and bookings
using a combination of Excel spreadsheets and paper records. As her business grows, this
manual system is becoming unmanageable .
Carol needs a computerized database system to organize her data, track customer
preferences, manage trip bookings, and analyze business performance. This project will
design and implement a complete Microsoft Access solution for Carol's Travel Club.
1.2 Business Requirements
After interviewing Carol, the following requirements have been identified:
Customer Management:
Store customer contact information (name, address, phone, email)
Track customer preferences (travel interests, dietary restrictions, special needs)
Record customer communication history
Identify VIP customers based on booking frequency and value
Trip Management:
Maintain catalog of offered trips with descriptions, dates, and prices
Track destination details (country, region, attractions)
Manage trip capacity and availability
Record trip leaders and support staff
Booking Management:
Process customer bookings for trips
Track payment status and amounts
Manage cancellations and waitlists
Generate booking confirmations and invoices
Reporting Requirements:
Trip popularity and profitability analysis
Customer booking history
Revenue by month, quarter, year
Upcoming trip rosters
Mailing labels for marketing campaigns
1.3 Project Scope
This database will be a single-user system initially, but designed with future multi-user
expansion in mind. It will include:
8-10 related tables with proper relationships
15-20 queries for data analysis
5-7 data entry and navigation forms
8-10 professional reports
Menu system (switchboard) for easy navigation
Data validation and business rules
User-friendly error handling
2. Phase 1: Requirements Analysis
2.1 Identifying Entities
Through analysis, we identify the main entities (objects) about which Carol needs to store
information :
Core Entities:
Customers: People who book trips
Trips: Travel experiences offered
Destinations: Locations where trips occur
Bookings: Customer trip reservations
Payments: Money received for bookings
Supporting Entities:
Trip Leaders: Staff who lead trips
Interests: Travel preferences (adventure, culture, relaxation)
Suppliers: Hotels, airlines, tour operators
Itinerary Items: Daily activities within trips
2.2 Defining Attributes
For each entity, we identify the specific data elements needed:
Customers:
CustomerID (unique identifier)
FirstName, LastName
Address, City, State, ZipCode, Country
PhoneHome, PhoneMobile, Email
DateOfBirth
EmergencyContact, EmergencyPhone
DietaryRestrictions
JoinDate
PreferredContactMethod
Notes
Trips:
TripID (unique identifier)
TripName
DestinationID (where it goes)
Description
StartDate, EndDate
Price (per person)
MaxCapacity
CurrentBookings
Status (Planning, Confirmed, Full, Completed, Cancelled)
TripLeaderID
ItinerarySummary
Bookings:
BookingID (unique identifier)
CustomerID (who booked)
TripID (what they booked)
BookingDate
NumberOfPeople
TotalPrice
DepositPaid
BalancePaid
PaymentStatus (Deposit, Partial, Paid In Full)
Cancelled (Yes/No)
SpecialRequests
Source (how they heard about us)
2.3 Business Rules
Documenting rules that govern data:
1. A customer can make multiple bookings, but each booking belongs to one customer
2. A trip can have multiple bookings, but each booking is for one trip
3. A destination can have multiple trips, but each trip goes to one destination
4. A trip leader can lead multiple trips, but each trip has one primary leader
5. Bookings cannot exceed trip capacity
6. Deposit must be at least 20% of total price
7. Full payment is due 45 days before trip start
8. Cancellations made less than 30 days before trip forfeit deposit
9. Customer email addresses must be unique
10. Trip dates cannot overlap for the same leader
3. Phase 2: Database Design and Normalization
3.1 Conceptual Design
We create an Entity Relationship Diagram (ERD) showing entities and their relationships :
text
┌──────────┐ ┌──────────┐ ┌──────────┐
│ Customers│◄─────────┤ Bookings ├─────────►│ Trips │
└──────────┘ 1 * └──────────┘ * 1 └──────────┘
│ │
│ │
▼ ▼
┌──────────┐ ┌──────────┐
│ Payments │ │Destinatio│
└──────────┘ │ ns │
└──────────┘
3.2 Normalization Process
First Normal Form (1NF) : Eliminate repeating groups
Ensure each field contains atomic values
Create separate tables for multi-valued attributes
Example: Instead of storing multiple interests in one Customer field, create a
CustomerInterests junction table.
Second Normal Form (2NF) : Remove partial dependencies
All non-key fields depend on entire primary key
Already satisfied with single-field primary keys
Third Normal Form (3NF) : Remove transitive dependencies
Non-key fields don't depend on other non-key fields
Example: In Trips table, DestinationName depends on DestinationID, so we create a separate
Destinations table.
3.3 Final Table Structure
tblCustomers
CustomerID (AutoNumber, Primary Key)
FirstName (Short Text)
LastName (Short Text)
Address (Short Text)
City (Short Text)
State (Short Text)
ZipCode (Short Text)
Country (Short Text)
PhoneHome (Short Text)
PhoneMobile (Short Text)
Email (Short Text, Indexed, No Duplicates)
DateOfBirth (Date/Time)
EmergencyContact (Short Text)
EmergencyPhone (Short Text)
DietaryRestrictions (Long Text)
JoinDate (Date/Time, Default = Date())
PreferredContactMethod (Short Text)
Notes (Long Text)
Active (Yes/No, Default = Yes)
tblDestinations
DestinationID (AutoNumber, Primary Key)
DestinationName (Short Text)
Country (Short Text)
Region (Short Text)
Description (Long Text)
BestTimeToVisit (Short Text)
Image (Attachment - optional)
tblTripLeaders
LeaderID (AutoNumber, Primary Key)
FirstName (Short Text)
LastName (Short Text)
Phone (Short Text)
Email (Short Text)
Bio (Long Text)
Specialties (Short Text)
HireDate (Date/Time)
Active (Yes/No)
tblTrips
TripID (AutoNumber, Primary Key)
TripName (Short Text)
DestinationID (Number, Foreign Key to tblDestinations)
Description (Long Text)
StartDate (Date/Time)
EndDate (Date/Time)
Price (Currency)
MaxCapacity (Number, Integer)
CurrentBookings (Number, Integer)
Status (Short Text)
LeaderID (Number, Foreign Key to tblTripLeaders)
ItinerarySummary (Long Text)
IncludedItems (Long Text)
NotIncludedItems (Long Text)
CancellationPolicy (Long Text)
tblBookings
BookingID (AutoNumber, Primary Key)
CustomerID (Number, Foreign Key to tblCustomers)
TripID (Number, Foreign Key to tblTrips)
BookingDate (Date/Time, Default = Date())
NumberOfPeople (Number, Integer)
TotalPrice (Currency)
DepositPaid (Currency)
BalancePaid (Currency)
PaymentStatus (Short Text)
Cancelled (Yes/No, Default = No)
CancellationDate (Date/Time)
SpecialRequests (Long Text)
Source (Short Text)
Notes (Long Text)
tblPayments
PaymentID (AutoNumber, Primary Key)
BookingID (Number, Foreign Key to tblBookings)
PaymentDate (Date/Time, Default = Date())
Amount (Currency)
PaymentMethod (Short Text)
Reference (Short Text - check #, transaction ID)
Notes (Long Text)
tblInterests
InterestID (AutoNumber, Primary Key)
InterestName (Short Text)
Description (Short Text)
tblCustomerInterests (Junction table for many-to-many)
CustomerID (Number, Foreign Key)
InterestID (Number, Foreign Key)
Composite Primary Key: (CustomerID, InterestID)
tblSuppliers
SupplierID (AutoNumber, Primary Key)
SupplierName (Short Text)
ContactPerson (Short Text)
Phone (Short Text)
Email (Short Text)
Address (Short Text)
City (Short Text)
Country (Short Text)
SupplierType (Short Text)
Notes (Long Text)
tblTripSuppliers (Junction table)
TripID (Number, Foreign Key)
SupplierID (Number, Foreign Key)
ServiceProvided (Short Text)
Composite Primary Key: (TripID, SupplierID)
4. Phase 3: Creating Tables and Relationships
4.1 Creating Tables in Access
Follow these steps to create each table :
1. Open Access and create a new blank database named "[Link]"
2. Click Create > Table Design
3. Enter field names and select appropriate data types
4. Set field properties as specified
5. Set primary key by selecting the field and clicking Primary Key
6. Save table with specified name (e.g., "tblCustomers")
7. Repeat for all tables
4.2 Setting Field Properties
Critical properties to configure:
[Link]:
Required: Yes
Indexed: Yes (No Duplicates)
Validation Rule: Like "@.*"
Validation Text: "Please enter a valid email address"
[Link]:
Validation Rule: <=Date()
Validation Text: "Date of birth cannot be in the future"
[Link]:
Format: Currency
Validation Rule: >0
Validation Text: "Price must be greater than zero"
[Link], EndDate:
Validation Rule: [EndDate] > [StartDate]
Validation Text: "End date must be after start date"
[Link]:
Validation Rule: Between 1 And 10
Validation Text: "Number of people must be between 1 and 10"
[Link]:
Lookup: Value List with "Deposit", "Partial", "Paid In Full"
4.3 Establishing Relationships
Create relationships between tables :
1. Click Database Tools > Relationships
2. Add all tables to the Relationships window
3. Create relationships by dragging primary key to foreign key:
Relationshi
Parent Table Parent Field Child Table Child Field
p Type
tblCustomers CustomerID tblBookings CustomerID One-to-Many
tblTrips TripID tblBookings TripID One-to-Many
tblDestination DestinationI DestinationI
tblTrips One-to-Many
s D D
tblTripLeaders LeaderID tblTrips LeaderID One-to-Many
tblBookings BookingID tblPayments BookingID One-to-Many
tblCustomerInteres
tblCustomers CustomerID CustomerID One-to-Many
ts
tblCustomerInteres
tblInterests InterestID InterestID One-to-Many
ts
tblTrips TripID tblTripSuppliers TripID One-to-Many
tblSuppliers SupplierID tblTripSuppliers SupplierID One-to-Many
4. For each relationship, check Enforce Referential Integrity
5. Check Cascade Update Related Fields for primary key relationships
6. Consider Cascade Delete Related Records carefully (may not want to automatically
delete bookings if a customer is deleted)
5. Phase 4: Data Population Strategies
5.1 Initial Data Entry
Before using forms, populate lookup tables with reference data :
tblInterests:
Adventure Travel
Cultural Tours
Culinary Experiences
Eco-Tourism
Family Vacations
Honeymoons
Luxury Travel
Religious Pilgrimages
Safari
Senior Travel
Solo Travel
Volunteer Tourism
Wellness Retreats
Wine Tours
tblSuppliers (sample):
Global Airlines (Air travel)
Marriott International (Hotels)
Intrepid Travel (Tour operator)
Avis Budget Group (Car rental)
Allianz Travel (Insurance)
5.2 Importing Existing Data
Carol has customer data in Excel. Import it :
1. Click External Data > Excel
2. Browse to Excel file
3. Select "Import the source data into a new table in the current database"
4. Follow wizard to map Excel columns to table fields
5. Choose appropriate data types
6. Set CustomerID as primary key or let Access add AutoNumber
5.3 Sample Data for Testing
Create sample records for testing :
Sample Customers:
text
CustomerID: 1
FirstName: John
LastName: Smith
Email: [Link]@[Link]
PhoneMobile: 555-123-4567
JoinDate: 1/15/2024
Active: Yes
CustomerID: 2
FirstName: Maria
LastName: Garcia
Email: maria.g@[Link]
PhoneMobile: 555-987-6543
JoinDate: 2/3/2024
Active: Yes
Sample Destinations:
text
DestinationID: 1
DestinationName: Paris
Country: France
Region: Western Europe
BestTimeToVisit: April-June, September-October
DestinationID: 2
DestinationName: Kyoto
Country: Japan
Region: East Asia
BestTimeToVisit: March-May, October-November
Sample Trips:
text
TripID: 1
TripName: Parisian Delights
DestinationID: 1
StartDate: 6/15/2024
EndDate: 6/22/2024
Price: $2,499
MaxCapacity: 16
Status: Confirmed
TripID: 2
TripName: Cherry Blossoms of Kyoto
DestinationID: 2
StartDate: 4/5/2024
EndDate: 4/12/2024
Price: $3,299
MaxCapacity: 12
Status: Full
5.4 Data Validation Testing
Test validation rules:
Try to enter future date of birth (should be rejected)
Try to enter negative price (should be rejected)
Try to book more people than trip capacity (should trigger error)
Try to create booking for non-existent customer (should be prevented by referential
integrity)
6. Phase 5: Query Design for Business Intelligence
6.1 Customer Analysis Queries
qryCustomersByLocation :
sql
SELECT City, State, Country, Count(*) AS CustomerCount
FROM tblCustomers
WHERE Active = True
GROUP BY City, State, Country
ORDER BY Count(*) DESC;
qryVIPCustomers (customers with multiple bookings):
sql
SELECT [Link], [Link],
[Link], Count([Link]) AS BookingCount,
Sum([Link]) AS TotalSpent
FROM tblCustomers INNER JOIN tblBookings
ON [Link] = [Link]
WHERE [Link] = False
GROUP BY [Link], [Link], [Link]
HAVING Count([Link]) >= 2
ORDER BY Sum([Link]) DESC;
qryCustomerInterests:
sql
SELECT [Link] & ", " & [Link] AS CustomerName,
[Link]
FROM (tblCustomers INNER JOIN tblCustomerInterests
ON [Link] = [Link])
INNER JOIN tblInterests
ON [Link] = [Link]
ORDER BY [Link], [Link];
6.2 Trip Performance Queries
qryTripOccupancy:
sql
SELECT [Link], [Link],
[Link], [Link],
IIf([CurrentBookings]>= [MaxCapacity],"FULL",
IIf([CurrentBookings]/[MaxCapacity] >= 0.8,"NEARLY FULL",
"AVAILABLE")) AS Status,
[MaxCapacity] - [CurrentBookings] AS AvailableSpaces
FROM tblTrips
WHERE [Link] >= Date()
ORDER BY [Link];
qryTripRevenue :
sql
SELECT [Link], [Link],
Year([Link]) AS Year,
Count([Link]) AS BookingCount,
Sum([Link]) AS TotalTravelers,
Sum([Link]) AS GrossRevenue,
Avg([Link]) AS AvgBookingValue
FROM tblTrips LEFT JOIN tblBookings
ON [Link] = [Link]
WHERE [Link] = False OR [Link] Is Null
GROUP BY [Link], [Link], Year([Link]);
qryDestinationPopularity:
sql
SELECT TOP 10 [Link],
Count([Link]) AS BookingCount,
Sum([Link]) AS Travelers
FROM (tblDestinations INNER JOIN tblTrips
ON [Link] = [Link])
INNER JOIN tblBookings ON [Link] = [Link]
WHERE [Link] = False
GROUP BY [Link]
ORDER BY Count([Link]) DESC;
6.3 Financial Queries
qryPaymentStatus:
sql
SELECT [Link],
[Link] & ", " & [Link] AS Customer,
[Link], [Link],
[Link], [Link],
[TotalPrice] - [DepositPaid] - [BalancePaid] AS OutstandingBalance,
IIf([OutstandingBalance]<=0,"PAID",
IIf([OutstandingBalance]=[TotalPrice],"NO PAYMENT",
IIf([OutstandingBalance]>0 AND [DepositPaid]=0,"DEPOSIT DUE","BALANCE DUE")))
AS Status
FROM (tblCustomers INNER JOIN tblBookings
ON [Link] = [Link])
INNER JOIN tblTrips ON [Link] = [Link]
WHERE [Link] = False
ORDER BY OutstandingBalance DESC;
qryMonthlyRevenue:
sql
SELECT Format([Link],"yyyy-mm") AS Month,
Count([Link]) AS PaymentCount,
Sum([Link]) AS TotalRevenue,
Avg([Link]) AS AveragePayment
FROM tblPayments
GROUP BY Format([Link],"yyyy-mm")
ORDER BY Format([Link],"yyyy-mm") DESC;
6.4 Upcoming Trip Reports
qryUpcomingTrips (parameter query for date range) :
sql
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Link], [Link],
[Link], [Link],
[Link], [Link],
[Link] & ", " & [Link] AS Leader
FROM (tblDestinations INNER JOIN tblTrips
ON [Link] = [Link])
LEFT JOIN tblTripLeaders ON [Link] = [Link]
WHERE [Link] Between [Start Date] And [End Date]
AND [Link] <> "Cancelled"
ORDER BY [Link];
qryTripRoster (for a specific trip) :
sql
PARAMETERS [Enter TripID] Long;
SELECT [Link], [Link],
[Link], [Link],
[Link], [Link],
[Link]
FROM tblCustomers INNER JOIN tblBookings
ON [Link] = [Link]
WHERE [Link] = [Enter TripID]
AND [Link] = False
ORDER BY [Link], [Link];
7. Phase 6: User Interface Development with Forms
7.1 Main Menu Form (Switchboard)
Create a navigation hub for the database :
1. Click Create > Form Design
2. Add title label: "Carol's Travel Club Database"
3. Add command buttons for main functions:
Customer Management Section:
Button: "Customers" - opens frmCustomers
Button: "Find Customer" - opens frmFindCustomer
Button: "Customer Interests" - opens frmCustomerInterests
Trip Management Section:
Button: "Trips" - opens frmTrips
Button: "Destinations" - opens frmDestinations
Button: "Trip Leaders" - opens frmTripLeaders
Booking Management Section:
Button: "New Booking" - opens frmNewBooking
Button: "View Bookings" - opens frmBookings
Button: "Process Payment" - opens frmPayments
Reports Section:
Button: "Trip Reports" - opens switchboard submenu or runs reports directly
Utility Section:
Button: "Backup Database" - runs backup macro
Button: "Exit Application" - closes database
4. Set form properties:
o Caption: "Main Menu"
o Navigation Buttons: No
o Record Selectors: No
o Scroll Bars: Neither
o Border Style: Dialog
o Pop Up: Yes
7.2 Customer Data Entry Form
Create a comprehensive form for managing customers :
1. Click Create > Form Wizard
2. Select tblCustomers and include all fields
3. Choose Columnar layout
4. Style: Office or as preferred
5. Title: "Customer Information"
6. Enhance in Design View:
o Arrange fields in logical groups
o Add tab control for organizing:
Tab 1: Contact Information
Tab 2: Personal Details
Tab 3: Preferences & Interests
Tab 4: Booking History (subform)
7. Add subform for CustomerInterests:
o Drag tblCustomerInterests from Navigation Pane onto form
o Ensure Link Master Fields and Link Child Fields properties are set to CustomerID
o Convert to Datasheet view for easy entry
8. Add command buttons:
o Add New Record
o Save Record
o Delete Record
o Find Customer
o View Bookings
o Print Customer Summary
9. Add combo boxes for:
o State: Value list of states
o PreferredContactMethod: Value list (Email, Phone, Mail)
o Interests: Multi-select possible via subform
10. Add validation:
o Email format validation in Before Update event
o Phone number formatting with input masks
7.3 Trip and Booking Forms
frmTrips:
Based on tblTrips with combo boxes for Destination and Leader
Subform showing bookings for selected trip
Calculated fields: AvailableSpaces = [MaxCapacity] - [CurrentBookings]
Conditional formatting: Turn red when AvailableSpaces = 0
Button: "View Roster" opens rptTripRoster filtered for current trip
frmNewBooking:
1. Create form with:
o Combo box for Customer (searchable)
o Combo box for Trip (showing only trips with available spaces)
o NumberOfPeople field
o Automatic calculation of TotalPrice based on trip price and number of people
o Deposit field with minimum validation (20% of total)
2. Add code to ensure booking doesn't exceed capacity:
o In Before Update event, check CurrentBookings + NumberOfPeople <=
MaxCapacity
3. After successful booking:
o Automatically update [Link]
o Create payment record if deposit paid
o Print confirmation (optional)
7.4 Payment Processing Form
Create a form for recording payments :
1. Based on tblPayments with lookup to tblBookings
2. Show booking details (customer, trip, total price, outstanding balance)
3. Prevent overpayment with validation
4. Update [Link] or BalancePaid when payment recorded
5. Auto-update PaymentStatus based on paid amounts
7.5 Form Design Best Practices
Tab order: Ensure logical flow through fields (Tab key moves in expected order)
Default focus: Set to first data entry field
Control tips: Add helpful hints for each field
Consistent sizing: Make all text boxes same width
Group boxes: Visually organize related fields
Conditional formatting: Highlight important information
Save before close: Prompt if unsaved changes exist
8. Phase 7: Professional Report Creation
8.1 Customer Reports
rptCustomerDirectory:
1. Click Create > Report Wizard
2. Select: CustomerID, LastName, FirstName, PhoneHome, PhoneMobile, Email
3. Group by: None
4. Sort by: LastName, FirstName
5. Layout: Tabular, Portrait
6. Title: "Customer Directory"
7. Enhance in Design View:
o Add report header with title and date
o Add page numbers
o Alternating row colors for readability
o Company logo
rptCustomerMailingLabels:
1. Click Create > Labels
2. Select customer fields: FirstName, LastName, Address, City, State, ZipCode
3. Choose label type (Avery 5160 or compatible)
4. Format name as: [FirstName] & " " & [LastName]
5. Format city/state/zip as: [City] & ", " & [State] & " " & [ZipCode]
8.2 Trip Reports
rptTripItinerary:
1. Base on qryTripDetails query
2. Group by TripName
3. Include: StartDate, EndDate, Destination, Leader, Price
4. Add itinerary items from related table
5. Include map image if available
rptTripRoster (parameterized) :
1. Create query qryTripRosterParameter that prompts for TripID
2. Base report on that query
3. Include customer names, contact info, special requests
4. Group by nothing, sort by LastName
5. Add count of travelers at report footer
8.3 Booking and Financial Reports
rptBookingConfirmation:
1. Design as a formal confirmation document
2. Include company logo and contact information
3. Show customer name, trip details, payment schedule
4. Include cancellation policy and important notes
5. Set as a single record report (based on selected booking)
rptRevenueSummary:
1. Based on qryMonthlyRevenue
2. Chart wizard to add revenue trend line
3. Year-to-date totals
4. Compare to previous year
rptUpcomingTrips:
1. Based on qryUpcomingTrips with parameters
2. Group by month
3. Show trip details with available spaces
4. Conditional formatting: highlight nearly full trips
8.4 Report Design Tips
Grouping: Use group headers/footers for summaries
Page breaks: Insert after groups for separate pages per group
Can Shrink/Can Grow: Allow controls to expand with content
Hide duplicates: For repeated values in grouped reports
Running sums: Accumulate totals across groups
Charts: Add visual representation of data
Export options: Allow PDF, Excel, Word export
9. Phase 8: Automation with Macros
9.1 Data Validation Macros
Create a data macro to enforce business rules at table level :
Before Change data macro on tblBookings:
1. Open tblBookings in Design View
2. Click Create Data Macros > Before Change
3. Add actions:
text
If [NumberOfPeople] > LookupRecord (SELECT MaxCapacity FROM tblTrips WHERE TripID =
[TripID]) Then
RaiseError
Error Number: 1
Error Description: "Number of people exceeds trip capacity"
End If
If [TotalPrice] <> LookupRecord (SELECT Price FROM tblTrips WHERE TripID = [TripID]) *
[NumberOfPeople] Then
SetField
Name: TotalPrice
Value: LookupRecord (SELECT Price FROM tblTrips WHERE TripID = [TripID]) *
[NumberOfPeople]
End If
9.2 User Interface Macros
mcrOpenCustomerForm:
1. Click Create > Macro
2. Add actions:
o OpenForm (Form Name: frmCustomers, View: Form, Window Mode: Normal)
o GoToControl (Control Name: LastName)
o MaximizeWindow
mcrFindCustomer:
1. Add actions:
o OpenForm (Form Name: frmCustomers, View: Form, Window Mode: Normal)
o SearchForRecord (Object Type: Form, Object Name: frmCustomers, Record:
First, Where Condition: "[LastName] Like '" & [Enter partial last name:] & "'")
9.3 Report Automation Macros
mcrPreviewTripRoster:
1. Create macro with parameter prompt:
o SetTempVar (Name: SelectedTrip, Expression: [Enter TripID:])
o OpenReport (Report Name: rptTripRoster, View: Print Preview, Where
Condition: "TripID = " & [TempVars]![SelectedTrip])
o RemoveTempVar (Name: SelectedTrip)
mcrPrintMailingLabels:
OpenReport (Report Name: rptCustomerMailingLabels, View: Print)
9.4 Utility Macros
mcrBackupDatabase:
1. Add actions:
o RunMenuCommand (Command: CopyDatabaseFile)
o MessageBox (Message: "Backup completed successfully", Type: Information)
mcrCompactOnClose:
Set this macro as AutoExec to run when database opens, or attach to form close events
9.5 AutoExec Macro
Create a macro named AutoExec that runs when database opens :
1. Create new macro named "AutoExec"
2. Add actions:
o MinimizeWindow (hides main Access window)
o OpenForm (Form Name: frmSwitchboard, View: Form, Window Mode: Dialog)
o RunMenuCommand (Command: WindowHide)
This provides a professional application feel, hiding the Access interface.
10. Phase 9: Security and User Management
10.1 Database Password Protection
For a single-user or small team database :
1. Open database exclusively
2. Click File > Info > Encrypt with Password
3. Enter strong password
4. Confirm password
Important: Store password securely. If lost, data cannot be recovered.
10.2 User-Level Security (Advanced)
For multiple users with different permissions:
1. Create user tables: tblUsers, tblUserRoles, tblRolePermissions
2. Create login form (frmLogin) that validates credentials
3. Store encrypted passwords (use VBA for hashing)
4. After login, enable/disable forms and buttons based on permissions
10.3 Split Database Design
For multi-user environments, split the database :
1. Back-end database: Contains only tables
2. Front-end database: Contains queries, forms, reports, macros
3. Link front-end to back-end tables
4. Distribute front-end to each user
Benefits:
Better performance
Easier updates (replace front-end without touching data)
Reduced corruption risk
To split:
1. Click Database Tools > Access Database (under Move Data)
2. Follow Database Splitter Wizard
10.4 Audit Trail Implementation
Track who changed what and when:
1. Create audit table: tblAuditLog
o LogID (AutoNumber)
o TableName (Short Text)
o RecordID (Number)
o Action (Short Text - Insert, Update, Delete)
o FieldName (Short Text)
o OldValue (Long Text)
o NewValue (Long Text)
o UserName (Short Text)
o ChangeDate (Date/Time)
2. Create data macros on each table's After Insert, After Update, After Delete events
3. Log changes to audit table
11. Phase 10: Deployment and Maintenance
11.1 Database Packaging
Prepare database for distribution:
1. Compact and Repair database
2. Remove test data
3. Set startup options: File > Options > Current Database
o Application Title: "Carol's Travel Club"
o Display Form: frmSwitchboard
o Navigation Pane: Hide
o Allow Full Menus: No
o Allow Default Shortcut Menus: No
o Use Access Special Keys: No (or Yes with caution)
11.2 Runtime Distribution
For users without Access installed:
1. Use Access Runtime (free distribution)
2. Package database with Runtime installer
3. Consider Access Developer Extensions for professional installation packages
11.3 Backup Strategy
Implement regular backups:
1. Daily automated backups using Windows Task Scheduler
2. Keep at least 7 daily backups, 4 weekly backups
3. Store backups on different drive or cloud storage
4. Test restoration quarterly
11.4 Maintenance Schedule
Weekly: Compact and Repair
Monthly: Review performance, check for corruption
Quarterly: Review and optimize queries
Annually: Archive old data, review design for improvements
12. Project Deliverables Checklist
Required Database Objects
Object Type Count Description
Tables 10 As designed in Section 3
Relationships 9 Enforcing referential integrity
Queries 15-20 Including select, parameter, action queries
Forms 5-7 Main menu, data entry, search forms
Reports 8-10 Customer, trip, booking, financial reports
Macros 5-10 Navigation, automation, utilities
Documentation Deliverables
User Manual (Word document)
Technical Documentation (database design, relationships)
Training Materials (quick reference cards)
Backup and Recovery Procedures
Testing Checklist
All tables accept valid data
Validation rules prevent invalid data
Relationships prevent orphan records
All queries return expected results
All forms open and function correctly
All reports print or preview properly
Navigation menu accesses all objects
Data macros enforce business rules
Security measures work as intended
Database performs acceptably with sample data
13. Sample Data Sets
13.1 Customers (10 sample records)
FirstNam
LastName City State Email
e
John Smith New York NY [Link]@[Link]
Maria Garcia Miami FL maria.g@[Link]
Robert Johnson Chicago IL rjohnson@[Link]
Patricia Williams Los Angeles CA pwilliams@[Link]
Michael Brown Houston TX mbrown@[Link]
Linda Jones Phoenix AZ ljones@[Link]
William Miller Philadelphia PA wmiller@[Link]
FirstNam
LastName City State Email
e
Elizabeth Davis San Antonio TX edavis@[Link]
James Rodriguez San Diego CA jrodriguez@[Link]
Jennifer Martinez Dallas TX jmartinez@[Link]
13.2 Trips (5 sample records)
Destinatio
TripName StartDate EndDate Price Capacity
n
Parisian Delights Paris 6/15/2024 6/22/2024 $2,499 16
Cherry Blossoms of
Kyoto 4/5/2024 4/12/2024 $3,299 12
Kyoto
Italian Adventure Rome 7/10/2024 7/20/2024 $2,899 14
Safari in Kenya Nairobi 8/5/2024 8/15/2024 $4,499 10
Greek Island Cruise Athens 9/3/2024 9/12/2024 $3,199 20
13.3 Bookings (15 sample records with various statuses)
BookingDat
Customer Trip People Status
e
John Smith Parisian Delights 2 1/15/2024 Confirmed
Maria Garcia Cherry Blossoms 1 1/20/2024 Confirmed
BookingDat
Customer Trip People Status
e
Robert Johnson Italian Adventure 4 1/22/2024 Deposit Paid
Patricia Williams Safari in Kenya 2 1/25/2024 Paid in Full
Michael Brown Greek Island Cruise 2 2/1/2024 Deposit Paid
Linda Jones Parisian Delights 1 2/3/2024 Paid in Full
William Miller Italian Adventure 2 2/5/2024 Cancelled
Elizabeth Davis Cherry Blossoms 2 2/7/2024 Waitlist
James Rodriguez Safari in Kenya 1 2/10/2024 Deposit Paid
Jennifer Martinez Greek Island Cruise 2 2/12/2024 Confirmed
John Smith Italian Adventure 2 2/15/2024 Deposit Paid
Maria Garcia Safari in Kenya 2 2/18/2024 Paid in Full
Robert Johnson Greek Island Cruise 2 2/20/2024 Deposit Paid
Patricia Williams Parisian Delights 1 2/22/2024 Confirmed
Michael Brown Italian Adventure 2 2/25/2024 Waitlist
14. Troubleshooting Guide
Common Issues and Solutions
Issue: Form opens but no data appears
Solution: Check Record Source property; ensure table/query exists and contains data
Issue: Combo box shows ID numbers instead of names
Solution: Adjust column widths property (set first column width to 0 to hide ID)
Issue: Cannot edit data in form
Solution: Check Allow Edits property; check if form is based on non-updateable query
Issue: Report prints blank pages
Solution: Check report width exceeds paper width; adjust margins
Issue: Query runs slowly
Solution: Add indexes to join fields; limit returned fields; optimize criteria
Issue: Database file grows very large
Solution: Compact and Repair regularly; consider archiving old data
Issue: "Locked by user" error
Solution: Close all objects; have other users close database; use Compact and Repair