Lesson 1

This module introduces the core skills you use to manipulate tables in Microsoft Access: creating tables from scratch, refining field definitions, and enforcing data quality at the point of entry. In Access, tables are your “system of record.” If the table design is weak (wrong data types, missing keys, inconsistent formats), every query, form, and report built on top of it becomes harder to maintain and less reliable.

In practical terms, table manipulation in Access means:

• Defining structure (fields, data types, and properties) in Design View.
• Editing structure safely (adding/removing fields, changing data types) without corrupting existing data.
• Controlling data quality using field properties such as Required, Field Size, Default Value, Validation Rule, and Input Mask.
• Choosing a primary key so each row is uniquely identifiable and relationships to other tables work correctly.

Even in Microsoft 365 / modern Access, the fundamentals are the same: define the right data types, enforce constraints early, and model the table for how the data will be queried and maintained. The difference today is that many Access databases are used in hybrid workflows:

• Access as a front end (forms/reports/VBA) with data stored in SQL Server, Dataverse, or a shared file-based backend.
• Integration with Excel/Power BI, where clean keys and consistent data types are essential for stable refreshes and joins.
• Multi-user environments, where table design affects locking, performance, and the risk of data-entry collisions.

Your goal in this module is to build tables that remain stable as your database grows—meaning fewer “fix it later” redesigns, fewer broken queries, and fewer reports that fail because a field contains inconsistent values.

Access provides two primary ways to work with tables:

• Datasheet View: You see rows and columns like a spreadsheet. Use this view for entering data and quick inspection.
• Design View: You define the table schema. Use this view to create fields, assign data types, set field properties, and define the primary key.

In most real projects, you switch between the two: you design the structure in Design View, then validate and test your changes in Datasheet View.

The following concepts show up in nearly every Access application:

1. Data types (Text, Number, Date/Time, Currency, Yes/No, Attachment, Hyperlink, etc.)
   Picking the correct type prevents data-entry errors and makes sorting/filtering predictable. A classic example is a ZIP code: it often needs Short Text (not Number) so you don’t lose leading zeros.
2. Field properties
   Properties like Field Size, Required, and Validation Rule are how you enforce “business rules” at the table level so every form or query benefits automatically.
3. Input masks
   Input masks are primarily a formatting aid (phone numbers, postal codes, IDs). They do not replace validation rules, but they can reduce mistakes and speed up data entry.
4. Primary keys
   A primary key is how Access uniquely identifies rows. It is also what enables reliable relationships, fast lookups, and correct update/delete behavior.

After completing this module, you will be able to:

1. Display the table Design view and use it to edit a table definition
2. Select correct data types for each field you create
3. Edit a field’s data type safely, including understanding what happens to existing data
4. Use an input mask to guide and limit data entry formatting
5. Add, move, and remove fields in both Datasheet view and Design view
6. Choose and define a primary key field that supports relationships and reporting

When building tables from scratch, the most reliable workflow is:

1. Draft the fields: list the information you need to store and how it will be used (searching, sorting, reporting, joining).
2. Choose the key first: decide whether an AutoNumber surrogate key is appropriate, or whether a natural key (or composite key) is required.
3. Assign data types intentionally: choose types for correctness first, then storage/performance.
4. Set constraints early: Required, validation rules, and sensible defaults prevent future cleanup projects.
5. Test with sample data: enter realistic records and verify sorting, filtering, and joins behave correctly.

This process reduces rework later, especially after forms and reports depend on the table structure.