Design and Create Tables to Store Data 9
The second option is to create a field in the table with the Calculated data type. This is particularly
useful when the calculated data is used in multiple forms and reports since you only have to create
it one time in the table. Also, while this second approach appears to directly contradict the
principle of not storing calculated data, it is perfectly valid to use this special data type in Microsoft
Access.
3. Ensure that data are stored in their smallest parts in the table.
Why? I’ll explain by example: storing employee name in one column of a table makes sorting or
searching on that column very difficult. Instead, split the employee name into first name and last
name to make the table data easier to use and more flexible.
4. Reporting needs should determine the data stored in the tables.
Why? Although this one is self-explanatory, it is a good reminder that the outputs of the database
(reports) should determine what data need to be stored in the database tables. Failure to include
necessary data in your tables will result in incomplete outputs.
How Do You Properly Build the Foundation?
Now that you have some context to understand how tables are designed and what they are used for, let’s
dive in and explore some technical details and basic terminology you will need to understand.
Tables are composed of fields (vertical columns) and records (horizontal rows), and they are used to store
data in a highly structured and organized format. Each field is assigned a name that explains the type of
data stored in that column. For example, in a table storing retail store locations for a company, you may
find fields for LocationName, Address, City, State, Zip, and Phone. If there are 65 retail store locations
across the United States, then there would be 65 records in the table: one record for each location.
You can examine a table in Microsoft Access either in the Datasheet View or the Design View. As you saw
in the Chapter 1 Guided Exercise, the Datasheet View is used to work with, enter, and delete data. In
addition, you can sort, format, filter, find, and summarize the data in the Datasheet View.
The Design View was not discussed in the Chapter 1 Guided Exercise because most end users of the
database system will not work in the Design View of the table. This view is the “behind-the-scenes” view
where the underlying structure of the table is created. While working in the Design View, you can modify
the fields, their data types, and their properties. It is important to properly set the data type and
properties of each field to help prevent bad data from being entered into your database tables.
Additionally, you can create very powerful Data Macros to further validate record updates or new data
entry. Data Macros can also be used to trigger other events such as creating a separate audit log, sending
email notifications, or updating related data records.
In general, a data type defines the type of the data that are going to be stored in that particular field. The
valid data types in Microsoft Access databases are Short Text, Long Text, Number, Date/Time, Currency,
AutoNumber, Yes/No, OLE Object, Hyperlink, and Attachment. Calculated and Lookup Wizard are two
special data types that are also valid.
• Short Text stores up to 255 characters of text, numbers, and symbols.