Note: This site will be taken down July 1, 2024.

---

Design View

The Table Design View lists all of the fields in the table along with their data types and descriptions. You do not see any records in this view. Each row represents a field. A Field Name and a Data type are required for every field. A Description is optional. When you select a field or a cell in one of the columns, there are further properties in the bottom of the window. You will see different choices for different data types.

Selecting: When a field (the whole row) or a property is selected in the table, a rectangle surrounds what is selected.

View: Table Design

---

Field Name

A field name, like other things in Access, can have up to 64 characters. It can contain letters, numbers, and symbols except for a period (.), an exclamation point (!), an accent grave (`), brackets ([ ]), and double quote mark ("). You cannot start a field name with a space. It will be safer to not use spaces or special characters at all.

Avoid using property names as field names: Access can get confused if your field has the same name as a property, such as Name, Caption, Format, or Height.

---

Data Types

You must choose a data type for each field. The data type determines what kind of data you can store in the field and how much disk space is reserved for that data. Most data types also have other properties for which you can choose values, like the number of decimal places or a default value.

Types:

• Text types - Can hold plain text or text combined with numbers as in an address. Good to use for numbers that are not used in calculations like phone numbers, part numbers, and postal codes. Will sort as characters, where the order of numbers is like 1, 10, 100, 2, 20, 200...
  

  • Text or Short Text (Access 2013, 2016) - less than 255 characters. Default field size is 50 characters.
    You should choose a size that will hold the longest entry that you expect.
  • Access 2007, 2010: Memo -Can hold 65,535 plain text characters. Cannot contain formatting.
    Access 2013, 2016: Long Text - Holds up to about 1 GB. Can be configured to accept Rich Text Formatting in a desktop database but not a web database.
    

• Number types - For calculations and sorting as numbers instead of text.

  • AutoNumber - Automatically assigns a unique number to each record. Great to use for an primary key.
  • Number - Several number field sizes are available, like Byte, Integer, and Long Integer.
    
  • Date/Time - Use this type in order to sort and calculate with dates and times correctly.  Dates are numbers representing the number of days since December 30, 1899. Earlier dates are negative numbers. See also Microsoft on storing date/time .
    
  • Currency - Use for high accuracy and for money. Avoids rounding. Can hold 15 digits to the left and 4 digits to the right of the decimal point.
• Yes/No - Use when there are only two choices - Yes/No, On/Off, True/False
• OLE Object - OLE = Object Linking and Embedding. Field can hold only one object. Objects include formatted text, complete documents, spreadsheets, images, sounds. Can be up to 1 GB in size if there is enough disk space. Access databases are limited to a total of 2 GB, including OLE objects and attachments.  The original program handles the display in Access, so it must be installed on the computer where Access is installed. Most file types are turned into bitmaps, which take a lot more space than with the new Attachment data type.
• Hyperlink - Up to 2048 characters for a URL (Uniform Resource Locator) or UNC (Universal Naming Convention) path
• Attachment - Adds one or more files like a document, image, or presentation, to a record. This results in a much smaller file size than using an OLE Object, which could only include one file per field.
• Access 2010, 2013, 2016: Calculated - Uses fields from the same table to perform a calculation and store the result in the table. This is not the same as a calculated field in a query, form, or report.
  Use with caution! There are some issues:
  • If you change the expression for the calculation after entering records, new records will use the new expression but old records may still use the old expression. That is not usually acceptable!
  • If an older version of Access is used to open the database, the table with a Calculated data type field will not open! You get an error message. No queries, forms, or reports that use that table will work.
  • Expressions allowed are limited.
• Lookup Wizard - Starts a wizard that will create a list of values to choose from for the field.

See also Microsoft's online list of Access data types

Things to consider when choosing a data type:

You want to choose the data type that will use the least amount of space on your hard disk without being too small for any future data. You must think carefully!

1. How many text characters are needed?
   Example - Phone Numbers: If you know that all phone numbers are from the USA, you could set the size to 10 and have the user type in the numbers without any punctuation, like 5555551234. But, if a phone number might be from another country, you cannot use size 10! How long is the longest phone number in the world??? I don't know. But it is longer than 10.

   Example - Names: It is harder to know how many characters you will need for names of people, cities, products, or companies. The default size of 50 may not be enough! Do not choose a size less than 50 unless you are VERY sure that you won't need more later. Be sure to count the spaces, too.

   Company names, in particular, can get long sometimes. My local phone book has some long corporate entries:
    Cumberland County Medical Center Regional Cancer Center = 55 characters
    Cumberland County Playhouse Production Shops & Rehearsal Hall = 61 characters
    Highland Federal Savings & Loan Assoc Fairfield Glade Branch =  60 characters!
   Even with some abbreviations, some names can be long!

   Having separate fields for parts of a name helps, like FirstName, MiddleName, and LastName for people. For companies you might use fields like CompanyName, Department, Branch, and Region.

2. What kind of numbers: Some number data types are for whole numbers only, like -123 or 1067. Others are for decimal numbers like 123456.987 or 0.00375. All data types for numbers have a maximum and minimum number that can be held in the field. You will need to know what size numbers you can expect in order to choose wisely.

   Example - Number of People: These will always be a whole number so you have 3 choices.
     Data type Byte requires that values be between 0 and 255 and uses 1 byte of storage.
     Data type Integer can hold whole numbers between –32,768 and 32,767 in 2 bytes of storage.
     Data type Long Integer can hold whole numbers between -2,147,483,648 and 2,147,483,647  (over 2 billion!) in 4 bytes of storage.

   Example - Gross National Product: This will be a very large number. Possibly larger than the Long Integer type can hold.
   Example - Scientific measurements: These might be very small as well as very large decimal numbers.

     Data type Single can hold a 7 digit decimal number in 4 bytes.
     Data type Double can hold a 15 digit decimal number in 8 bytes.
     Data type Decimal can hold a 28 digit decimal number in 12 bytes.

Text won't calculate: If a number has data type Text, you won't be able to use it in calculations.

Display Format vs. Data Type: The formatting of the data is not the same as the data type.

For example, you can type numbers in a field that has Text data type and then align them to the right. Those numbers will look just like they were Number type, but you won't be able to add with them. Another example is Date/Time data type. You can format a date as 19-Jun-06 or 6/19/06 or Monday, June 19, 2006. These are different ways to display the same date value, which is a number. If you want another format, like 06-19-2006, you must change the data type to Text. Then you will not be able to calculate using the date.

Changing data type later: You may have difficulty changing a data type later. If there is a relationship set up between your table and another one, you may have to delete the relationship first, change the data type, and then recreate the relationship. If you already have data in the field, any data that is too large for the new field type will be cut short. Any data that is not appropriate for the new data type will be tossed out. Plan carefully before changing a data type when there is already data in place!

---

Description

The Description appears in the Status Bar when the cursor is in the field in Datasheet View or in Form View.  Its purpose is to help the user enter the right data and enter it in the correct format.

Examples, when you might want a Description:

• Format for entering: Should a phone number be entered as 123-555-1234 or as 1235551234 or as (123) 555-1234?
• Kind of entry: Should money be rounded to whole amounts ($525) or must you include the decimal part ($525.33)?
• Choices or explanation of what to enter: What codes can be entered in the field and what do they stand for? A drop list or scrolling list box is often used to restrict the choices to the ones that work! The Description message can add warnings or explanations about the choices.

---

Primary Key

Each table needs to have a field called a primary key that will be unique for each record. Usually this is an AutoNumber field, which automatically assigns a number when a record is created. An AutoNumber has type Number: Long Integer.

Sometimes you can use existing data for your primary key, like a serial number or identification number, or even a combination of fields, like first, middle, and last names. Of course you must be VERY sure that what you have selected will ALWAYS be different for EVERY record!

---

Design Issues

How will you use your table?
The order of the fields will be important if you will be working directly in the table. If you use a form to enter data (recommended!), the order of fields in the original table will not matter.

What type of data?
Think about the size of the field and the data type that you need. For fields that will be shared with other tables, the data types must almost always be the same.

---

Undo Changes: Design View

In Design View, MS Access keeps a list of the last 20 actions. You can use the Undo and Redo buttons on the Quick Access Toolbar to back up through the list of actions.

Key Combo method:
   Undo = CTRL + Z;
   Redo = CTRL + Y

When you change views or objects, the Undo list is wiped out. There is no Undo list in Datasheet view! This is a reason not to enter data directly in Datasheet view.

---

Common Mistakes in Table Design

• Putting into one field data that should be in separate fields
  Example: Using one field for the City, State, and Zip Code is convenient to type. BUT, you cannot sort on State or filter to see records for a certain zip code.
  
• Using the wrong data type.
  Example: Zip codes - Zip codes start with 0 in some areas. That leading 0 will be dropped by Access if a number data type is used. 02234 is not the same as 2234 to the Postal Service! You don't do arithmetic with those numbers anyway. A text type works better in this situation.
  
• Leaving field sizes at the default size
  Example: The default size for a text field is 255. That is far too large for the State field when you are using the 2 character abbreviations for states. A Memo/Long Text field is needed when the entry may be longer than 255 characters. Such fields cannot be as easily sorted or filtered as Text/Short Text fields can, however. Choose carefully!
  
• Trying to get all the data into a single table
  Example: An invoice has information about the customer, the ordered items, the information about each item ordered (how many, color, size...), and shipping information. The customer information has to be repeated for each order. Shipping info might be the same for some orders and different for others. Wasted space!  All this information is too much and too complex for a single table! It takes 3 tables - customers, orders, orderdetails.

---

---