🗄️ Database Basics
The Four Core Object Types
1
Tables — store the actual data, organized in rows (records) and columns (fields), like a spreadsheet with rules
2
Queries — ask questions of your data: filter, sort, calculate, or combine tables
3
Forms — user-friendly screens for entering and viewing data without touching raw tables
4
Reports — formatted, printable summaries of your data, often built from a query
Access vs Excel
1
Excel is great for calculations and one-off analysis; Access is built for structured, relational, multi-user data
2
Access enforces data integrity rules (e.g., you can't enter an order for a customer that doesn't exist) — Excel doesn't
3
Access handles much larger datasets (millions of records) far better than Excel's row limits
4
Multiple people can safely edit an Access database simultaneously without overwrite conflicts that plague shared Excel files
📋 Tables & Data Types
Primary Keys & Data Types
1
Every table needs a Primary Key — a unique ID for each row (Access auto-creates an AutoNumber "ID" field by default)
2
Common data types: Short Text, Long Text, Number, Date/Time, Currency, Yes/No, AutoNumber
3
Set field properties like "Required" or input masks (e.g., phone number format) in Design View for cleaner data entry
4
Use Lookup fields to restrict entry to a dropdown of valid values from another table
Relationships Between Tables
1
Database Tools → Relationships lets you visually connect tables via shared fields (e.g., CustomerID in both Customers and Orders)
2
A one-to-many relationship is most common: one Customer can have many Orders
3
Enable "Enforce Referential Integrity" to prevent orphaned records (e.g., an order pointing to a deleted customer)
4
Splitting data into related tables instead of one giant table avoids duplicated, inconsistent data — this is called normalization
🔍 Queries
Building Queries
Create → Query DesignAdd the table(s) you want to pull data from
Drag fields into the gridPick exactly which columns to show in the result
Add criteriaType conditions under a field, e.g.
>100 or "Closed" to filter resultsRun itClick Run (red exclamation icon) to see the live results as a dynamic, filtered table
💡 Use the Query Wizard for guided setup of common patterns like Find Duplicates or Find Unmatched records — both extremely useful for data cleanup.
Action Queries
1
Update Query — bulk-edits values across many rows matching a condition in one operation
2
Delete Query — removes all rows matching a condition at once
3
Append Query — copies matching rows from one table into another
4
Make Table Query — saves query results as a brand new physical table
⚠️ Action queries permanently change data. Always back up your database (or test on a copy) before running Update or Delete queries.
🖥️ Forms
Creating User-Friendly Forms
1
Select a table or query → Create → Form generates a usable data-entry screen instantly
2
Use Form Design View to add buttons, labels, dropdowns, and rearrange fields exactly how you want
3
A Subform embeds a related table's records inside the main form (e.g., an Order's line items shown inside the Order form)
4
Forms are the safest way for non-technical staff to enter data — they never touch the raw tables directly
📄 Reports
Building Printable Reports
1
Select a query → Create → Report auto-generates a clean, print-ready layout
2
Use the Report Wizard to control grouping (e.g., group sales by region) and add subtotals automatically
3
Switch to Report Design View to add a company logo, custom headers/footers, or page numbers
4
Export any report directly to PDF or Excel (External Data tab → Export)
💡 When to Use Access
Common Real-World Use Cases
1
Inventory management — track stock levels, suppliers, and reorder points across many related tables
2
Small business CRM — customers, contacts, orders, and interaction history linked together
3
Event/membership management — registrations, attendance, payments for clubs or organizations
4
For larger or web-based needs, consider growing into SQL Server, Dataverse, or SharePoint Lists — Access scales best for small teams and departmental tools, not enterprise-wide systems
🛠️ Troubleshooting
Common Access Problems
1
Database grows huge / runs slowly: Database Tools → Compact and Repair Database regularly removes bloat from deleted records
2
"Database is in an inconsistent state": Usually fixed by Compact and Repair; if not, restore from a recent backup
3
Multiple users locked out: Check for a lingering .laccdb lock file — close all instances and delete it if Access crashed previously
4
"Can't relate tables": The fields linking them must be the same data type (e.g., both Number, not one Number/one Text)