Creating Tables, Forms, and Reports

Build the three core objects of a working database: a table with proper data types, a form for entry, and a report for output.

By the end of this lesson you can

Create a table in Design view and choose appropriate data types
Set a primary key and basic field properties
Generate and adjust a form for data entry
Generate a report and prepare it for printing

📘 Reading Lesson

Lesson Notes

Read through the key concepts before you try the challenge.

Data types are a validation decision

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 data types that make wrong data impossible to enter rather than merely discouraged.

A data type is the first line of defense for data quality. A Number field will not accept 'twelve'. A Date/Time field will not accept 'next Tuesday'. Choosing the right type at design time prevents an entire category of problem that is extremely tedious to fix afterwards.

Type	Use for	Note
Short Text	Names, addresses, codes	Up to 255 characters
Long Text	Notes and comments	Sortable but not efficiently indexed
Number	Quantities you will calculate with	Choose an appropriate field size
Currency	Money	Avoids the rounding errors of floating-point numbers
Date/Time	Dates and times	Enables date arithmetic and proper sorting
Yes/No	Two-state values	Stored as true/false
AutoNumber	Primary keys	Access assigns a unique value automatically
Attachment	Files linked to a record	Grows the database file quickly

Access data types

Store an identifier that is never calculated 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.

Worked example

Building a supply inventory table

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

1
Create the table in Design view rather than by typing into a datasheet.
Design view makes you name each field and choose its type deliberately. Building by typing into a datasheet lets Access guess the types, and it guesses Short Text far too often — which is how the scenario above happens.
2
Add ItemID as AutoNumber and set it as the primary key.
AutoNumber guarantees uniqueness with no effort and no meaning attached, which is exactly what a key should be. Access will not enforce record uniqueness without a primary key.
3
Set types by what each field is for: 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 number fields.
4
Set field properties: Required on the fields that must be filled, and a Default Value where one is sensible.
Required stops half-entered records, which are worse than no record because they look complete in a list. Defaults reduce typing and mistakes on fields that are usually the same.
5
Generate a form with Create > Form, and reorder the fields to match how staff work.
The generated form is a starting point in table order, which is rarely entry order. Arranging fields in the sequence someone reading off a delivery note would use is what makes the form fast rather than merely functional.
6
Generate a report with Create > Report, then check it in Print Preview.
The default report is almost always too wide for the page. Print Preview shows the real pagination, and narrowing or removing columns there is what turns it into something usable on paper.

Result: A table that rejects invalid entries, a form staff can work through quickly, and a report that prints correctly.

Design the table first and deliberately. 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 field?

Challenge

Apply what you've learned in this lesson.

Build a small working database from your Module 5 design.

In Access, 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 data type for each and set an AutoNumber primary key.
Set Required on item name and quantity, and give category a sensible default. Enter six records and try to save one with a blank item name — note exactly what Access does.
Generate a form for the table. Reorder the fields to match the order someone would read them off a delivery note, and enter two more records through the form.
Generate a report, open Print Preview, and adjust it until it fits the page width. Write two sentences on what you had to change and why the default was not usable.

Finished this lesson?

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

← The Access Window and Database Objects