| Lesson 3 | Data types |
| Objective | Describe the field data types available in MS Access. |
Microsoft Access Field Data Types
In Microsoft Access, every table field has a data type that determines what values can be stored, how much storage is required, how the value sorts and compares, and what kinds of validation Access can enforce. Choosing the right data type is one of the simplest and most effective ways to prevent bad data from entering your database.
Why data type selection matters
Access can automatically “guess” a data type when you enter data in Datasheet view, and often it chooses correctly. However, professional database design relies on intentional data types defined in Table Design view. When the type matches the business meaning of the field, Access can:
- Block invalid input (for example, letters in a numeric field).
- Apply appropriate formatting (currency, date/time, percentages, etc.).
- Sort and filter accurately (text sorting differs from numeric sorting).
- Reduce storage and improve performance by avoiding oversized types.
- Support indexing and referential integrity more predictably.
Modern Access data types at a glance
The table below summarizes the core field data types you will encounter in modern versions of Access (including Microsoft 365). Names such as Text and Memo are legacy terms that map to today’s Short Text and Long Text.
| Data Type | Allowed data, typical use, and practical limits |
|---|---|
| Short Text (legacy: Text) | Alphanumeric characters (letters, digits, punctuation, spaces). Common for names, codes, and short labels. Default maximum is 255 characters (Field Size can be set lower to enforce tighter limits). |
| Long Text (legacy: Memo) | Large text such as notes, descriptions, and comments. Supports far more than Short Text and can be configured for features like “Append Only” (tracking changes). Use Long Text when the content is not meaningfully constrained to 255 characters. |
| Number | Numeric values used for math and comparisons. The Field Size setting controls the subtype (Byte, Integer, Long Integer, Single, Double, Decimal). Choose the smallest subtype that fits your range to reduce storage and improve index efficiency. |
| Large Number | 64-bit integer values (BigInt-style). Useful for large identifiers and for interoperability when linking to external systems that use 64-bit keys. Not the same as Double; it is an integer type intended for exact whole numbers. |
| Currency | Fixed-precision monetary values. Use this instead of floating point types for money to avoid rounding artifacts. Ideal for prices, totals, and financial amounts. |
| Date/Time | Dates and times with built-in formatting and date/time pickers (depending on UI settings). Use for business dates (order date, due date) and timestamps (created/updated). |
| Date/Time Extended | Higher-precision date/time storage intended for scenarios that require more precision than the standard Date/Time type, especially when integrating with systems that store fractional seconds at higher precision. |
| AutoNumber | Access-generated unique values, commonly used for surrogate primary keys. Values can be sequential or (in some configurations) random. Use AutoNumber for stable identity, not for business meaning. |
| Yes/No | Boolean values (True/False). Often displayed as a checkbox. Suitable for flags such as Active, Archived, Approved, etc. Avoid using it for multi-valued categories (use a lookup table and foreign key instead). |
| Hyperlink | Web URLs, file paths, and email-style links. Useful when you want a clickable value. If the value is “just a string” and you do not need link behavior, Short Text is often simpler. |
| Attachment | Stores one or more files (documents, images) attached to a record. Convenient for small, record-centric files, but can increase database size quickly. For large file libraries, consider storing files externally and saving a path/URL. |
| OLE Object (legacy) | Older embedding mechanism for files. Generally avoided in modern designs due to storage overhead and compatibility issues. Prefer Attachment or external file storage with a path/URL. |
| Calculated | A stored expression based on other fields in the same table (for example, LineTotal = Quantity * UnitPrice). Use thoughtfully: calculated fields can duplicate data logic that may be better expressed in queries or reports. |
| Lookup Wizard (feature, not a true type) | Creates a drop-down selection experience by configuring a field to display values from a list or another table. In strong relational design, the stored value is typically a foreign key (often Number) while the UI shows a friendly label. Use lookups carefully to avoid confusion when viewing raw table data. |
Refining field definitions in Table Design view
After you select a data type, Table Design view lets you refine how the field behaves. The most commonly used properties include:
- Field Size (especially for Number and Short Text): enforces range/length and affects storage.
- Format: controls how values display (without changing the stored value).
- Input Mask: guides consistent entry (phone numbers, postal codes, IDs).
- Validation Rule and Validation Text: blocks invalid values with a clear message.
- Required and Default Value: supports mandatory fields and sensible defaults.
- Indexed: improves search/sort speed; use indexes for keys and frequently filtered columns.
Practical design guidance
- Choose types based on meaning, not convenience. Example: store a ZIP/Postal Code as Short Text (leading zeros matter), not Number.
- Use Currency for money and Number for math. Avoid floating point for financial totals.
- Prefer a surrogate key (AutoNumber) plus a UNIQUE constraint/index on the natural business identifier when appropriate.
- For “choices,” model the relationship: store a foreign key, and present the friendly name via forms/queries rather than overusing table lookups.
- Be cautious with Attachment and avoid OLE Object unless you have a specific legacy requirement.
Next, you will apply these concepts by editing field data types and properties in an existing table.