Lesson · 24 min

Creating Tables and Choosing Data Types

Build tables in Design view and choose data types that make invalid entries impossible rather than merely discouraged.

By the end of this lesson you can

Create a table in Design view and set a primary key
Choose the appropriate data type for each field
Set field properties including Required, Default Value, and Field Size
Explain why identifiers belong in text fields

📘 Reading Lesson

Lesson Notes

Read through the key concepts before you try the challenge.

Data types are your first line of defense

On the job

You build a supply inventory database at Lakeside Medical Associates.

You set every field to Short Text because it accepts anything. Six months later the quantity field contains '12', 'twelve', '12 boxes', and 'approx 12'. Nothing can be summed, sorted, or reported on, and the database cannot answer the one question it was built for.

Your task: Choose types that reject wrong data at the moment of entry.

A data type constrains what a field will accept. A Number field will not take 'twelve'. A Date/Time field will not take 'next Tuesday'. Choosing correctly at design time prevents a category of problem that is extremely tedious to repair afterwards.

Type	Use for	Note
Short Text	Names, addresses, codes, identifiers	Up to 255 characters
Long Text	Notes and comments	Sortable but not efficiently indexed
Number	Quantities you will calculate with	Set an appropriate Field Size
Currency	Money	Avoids the rounding errors of floating-point numbers
Date/Time	Dates and times	Enables date arithmetic and correct sorting
Yes/No	Two-state values	Stored as true/false
AutoNumber	Primary keys	Access assigns a unique value automatically
Lookup	A value chosen from a list or another table	Enforces consistent entry
Attachment	Files linked to a record	Grows the file quickly; use sparingly

Access data types

Store an identifier you would never calculate with — a phone number, a ZIP code, a patient ID — as Short Text, not Number. A Number field strips leading zeros, so ZIP code 07030 becomes 7030. The test is simple: if you would never add two of these values together, it is text.

Property	Does	Use it to
Required	Refuses to save a record with this field empty	Stop half-entered records
Default Value	Pre-fills a value	Reduce typing on fields that are usually the same
Field Size	Caps the length or numeric range	Prevent a 200-character state abbreviation
Validation Rule	Rejects entries failing a condition	Refuse a negative quantity
Validation Text	The message shown when the rule fails	Explain what the user should do instead
Input Mask	Guides entry into a fixed format	Phone numbers and dates
Indexed	Speeds searching and can enforce uniqueness	Fields you search or sort on constantly

Field properties worth setting

Worked example

Building the supply inventory table

Create a table recording items, quantities, costs, and reorder levels, designed so bad data cannot be entered.

1
Create the table in Design view, not by typing into a datasheet.
Design view makes you name each field and choose its type deliberately. Building by typing lets Access guess, and it guesses Short Text far too often — which is exactly how the scenario above happens.
2
Add ItemID as AutoNumber and set it as the primary key.
AutoNumber guarantees uniqueness with no effort and carries no meaning, which is what a key should be. Access will not enforce record uniqueness without a primary key.
3
Set types by purpose: Quantity as Number, UnitCost as Currency, LastOrdered as Date/Time.
Each type rejects data that does not belong. Currency specifically avoids the floating-point rounding that makes money columns fail to reconcile by a cent — a real problem with ordinary decimal fields.
4
Add a Validation Rule of >=0 on Quantity, with Validation Text explaining it.
A negative quantity on hand is meaningless. Catching it at entry is far cheaper than discovering it in a report, and the Validation Text is what makes the rejection useful rather than confusing.
5
Set Required on ItemName and Quantity.
Half-entered records are worse than no record, because they look complete in a list. Required makes the incomplete record impossible rather than merely discouraged.

Result: A table that refuses invalid entries at the point of entry rather than accumulating them.

Design the table deliberately and constrain it. Forms and reports are generated in seconds; a badly typed table takes months to repair.

Check your understanding

Which data type should be used for a five-digit ZIP code?

Challenge

Apply what you've learned in this lesson.

Build the table from your Module 1 design.

Create a database named 'Lakeside Supplies'. Build a table in Design view with fields for item name, category, quantity on hand, unit cost, reorder level, and last ordered date. Choose a deliberate type for each and set an AutoNumber primary key.
Set Required on item name and quantity, a Default Value on category, and a Validation Rule preventing negative quantities. Write Validation Text for it.
Try to save a record with a blank item name and one with a quantity of -5. Record exactly what Access does in each case.
Enter six valid records, then explain in two sentences which property you think prevents the most errors in practice.

Finished this lesson?

Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.

Relationships and Referential Integrity →