# 6.4: Creating a Table

$$\newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} }$$

$$\newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}}$$

$$\newcommand{\id}{\mathrm{id}}$$ $$\newcommand{\Span}{\mathrm{span}}$$

( \newcommand{\kernel}{\mathrm{null}\,}\) $$\newcommand{\range}{\mathrm{range}\,}$$

$$\newcommand{\RealPart}{\mathrm{Re}}$$ $$\newcommand{\ImaginaryPart}{\mathrm{Im}}$$

$$\newcommand{\Argument}{\mathrm{Arg}}$$ $$\newcommand{\norm}[1]{\| #1 \|}$$

$$\newcommand{\inner}[2]{\langle #1, #2 \rangle}$$

$$\newcommand{\Span}{\mathrm{span}}$$

$$\newcommand{\id}{\mathrm{id}}$$

$$\newcommand{\Span}{\mathrm{span}}$$

$$\newcommand{\kernel}{\mathrm{null}\,}$$

$$\newcommand{\range}{\mathrm{range}\,}$$

$$\newcommand{\RealPart}{\mathrm{Re}}$$

$$\newcommand{\ImaginaryPart}{\mathrm{Im}}$$

$$\newcommand{\Argument}{\mathrm{Arg}}$$

$$\newcommand{\norm}[1]{\| #1 \|}$$

$$\newcommand{\inner}[2]{\langle #1, #2 \rangle}$$

$$\newcommand{\Span}{\mathrm{span}}$$ $$\newcommand{\AA}{\unicode[.8,0]{x212B}}$$

$$\newcommand{\vectorA}[1]{\vec{#1}} % arrow$$

$$\newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow$$

$$\newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} }$$

$$\newcommand{\vectorC}[1]{\textbf{#1}}$$

$$\newcommand{\vectorD}[1]{\overrightarrow{#1}}$$

$$\newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}}$$

$$\newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}}$$

$$\newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} }$$

$$\newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}}$$

$$\newcommand{\avec}{\mathbf a}$$ $$\newcommand{\bvec}{\mathbf b}$$ $$\newcommand{\cvec}{\mathbf c}$$ $$\newcommand{\dvec}{\mathbf d}$$ $$\newcommand{\dtil}{\widetilde{\mathbf d}}$$ $$\newcommand{\evec}{\mathbf e}$$ $$\newcommand{\fvec}{\mathbf f}$$ $$\newcommand{\nvec}{\mathbf n}$$ $$\newcommand{\pvec}{\mathbf p}$$ $$\newcommand{\qvec}{\mathbf q}$$ $$\newcommand{\svec}{\mathbf s}$$ $$\newcommand{\tvec}{\mathbf t}$$ $$\newcommand{\uvec}{\mathbf u}$$ $$\newcommand{\vvec}{\mathbf v}$$ $$\newcommand{\wvec}{\mathbf w}$$ $$\newcommand{\xvec}{\mathbf x}$$ $$\newcommand{\yvec}{\mathbf y}$$ $$\newcommand{\zvec}{\mathbf z}$$ $$\newcommand{\rvec}{\mathbf r}$$ $$\newcommand{\mvec}{\mathbf m}$$ $$\newcommand{\zerovec}{\mathbf 0}$$ $$\newcommand{\onevec}{\mathbf 1}$$ $$\newcommand{\real}{\mathbb R}$$ $$\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}$$ $$\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}$$ $$\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}$$ $$\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}$$ $$\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}$$ $$\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}$$ $$\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}$$ $$\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}$$ $$\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}$$ $$\newcommand{\laspan}[1]{\text{Span}\{#1\}}$$ $$\newcommand{\bcal}{\cal B}$$ $$\newcommand{\ccal}{\cal C}$$ $$\newcommand{\scal}{\cal S}$$ $$\newcommand{\wcal}{\cal W}$$ $$\newcommand{\ecal}{\cal E}$$ $$\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}$$ $$\newcommand{\gray}[1]{\color{gray}{#1}}$$ $$\newcommand{\lgray}[1]{\color{lightgray}{#1}}$$ $$\newcommand{\rank}{\operatorname{rank}}$$ $$\newcommand{\row}{\text{Row}}$$ $$\newcommand{\col}{\text{Col}}$$ $$\renewcommand{\row}{\text{Row}}$$ $$\newcommand{\nul}{\text{Nul}}$$ $$\newcommand{\var}{\text{Var}}$$ $$\newcommand{\corr}{\text{corr}}$$ $$\newcommand{\len}[1]{\left|#1\right|}$$ $$\newcommand{\bbar}{\overline{\bvec}}$$ $$\newcommand{\bhat}{\widehat{\bvec}}$$ $$\newcommand{\bperp}{\bvec^\perp}$$ $$\newcommand{\xhat}{\widehat{\xvec}}$$ $$\newcommand{\vhat}{\widehat{\vvec}}$$ $$\newcommand{\uhat}{\widehat{\uvec}}$$ $$\newcommand{\what}{\widehat{\wvec}}$$ $$\newcommand{\Sighat}{\widehat{\Sigma}}$$ $$\newcommand{\lt}{<}$$ $$\newcommand{\gt}{>}$$ $$\newcommand{\amp}{&}$$ $$\definecolor{fillinmathshade}{gray}{0.9}$$

As previously stated, a database cannot exist without at least one table. Therefore, when a new database is created in Access, the first object that opens is a table in datasheet view.

Switching the view to Design View allows the user to make many design decisions in a much more intuitive view. Many users like to use Datasheet View to paste data copied from Excel. However, setting up the fields in Design View makes it easier to define each field’s properties and data type.

Design View doesn’t display any data. Therefore, there’s more space available to display other settings. In Design View, you can see the data type listed next to each field. When a user clicks on a field (in the top frame), the bottom frame displays the properties for that field. The user can then change these properties as desired.

Creating a table in Design View allows the user to define advanced settings, like data types and properties, for each field in the table. However, the first step is defining the field names. Here are a few guidelines when creating field names:

• Names can include numbers, UPPER and lowercase letters, and any combination. Avoid special characters (exclamation points, periods, brackets, quotes, etc.)
• No leading spaces. Spaces between words is acceptable, but not common practice, because:
• Make the names as short as possible without excessive abbreviation. Formatting column widths for displaying in printouts can be challenging with excessively long field names. For example, Order # or Order Number are better than Customer Order Number.
• Ensure the names are unique per table. If a table has fields for Customer ID and Product ID, neither field should be named simply ID. Likewise with the word Name. It is too generic and needs an prefix identifier.
• Break data into multiple fields. For example, instead of one field for Customer Name, create fields for First Name and Last Name. This will allow sorting and filtering on single fields. Another example would be separating an address into multiple fields for Street Address, City, State/Province and Zip/Postal Code.

Every field has a data type. A field’s data type indicates the kind of data that the field stores, such as large amounts of text or attached files. A field’s data type determines what other properties the field has including its size and formatting options. Field data types are defined in the table design grid, where as field properties are defined in the Field Properties pane in the bottom of the Table Design View window. Data types include:

Data type Description Size
Short Text Use for text or combinations of text and numbers (i.e. Phone Numbers). Up to 255 characters
Long Text Use for larger amounts of alphanumeric data, including sentences and paragraphs.  Note: You cannot sort a memo field. However, they are searchable. Up to 1 GB
Number Numeric values. 4 bytes
Large Number Numeric values. Stores  a non-monetary, numeric value and is compatible with the SQL_BIGINT data type in ODBC. Use this data type to efficiently calculate large numbers. 8 bytes
Currency Use for monetary data. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country’s currency to use. 8 bytes
AutoNumber Unique value generated by Access for each new record, usually starting at 1. 4 bytes
Date/Time Use for dates and times. 8 bytes
Yes/No A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0). Note: Null values are not allowed in Yes/No fields. 1 byte
Ole Object Can store pictures, audio, video, or other BLOBs (Binary Large OBjects). Up to 2 GB
Hyperlink Contain links to documents or other files, e-mail addresses, or web pages. Up to 8 bytes
Attachment Attach pictures, documents, etc. Can contain an unlimited number of characters per record. Access will compress the file. Up to 2 GB
Calculated Use to create an expression that uses data from one or more fields. You can designate different result data types from the expression. Dependent on the data type of the result type property
Lookup Wizard When selecting this entry, a wizard starts to help define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row. Dependent on the data type of the lookup field

After creating a field and setting its data type, users can set additional field properties. The field’s data type determines which other properties that can be set. The field size is the number of characters or the type of number it can store. For Number and Currency fields, the Field Size property is especially important, because it determines the range of field values. For example, a one-bit Number field can store only integers ranging from 0 to 255. In the Field Properties pane, on the General tab, enter the new field size in the Field Size property. Users can choose from the following values:

• Byte — For integers that range from 0 to 255. Storage requirement is a single byte.
• Integer — For integers that range from -32,768 to +32,767. Storage requirement is two bytes.
• Long Integer — For integers that range from -2,147,483,648 to +2,147,483,647. Storage requirement is four bytes.
• Single — For numeric floating point values that range from -3.4 x 1038 to +3.4 x 1038 and up to seven significant digits. Storage requirement is four bytes.
• Double — For numeric floating point values that range from -1.797 x 10308 to +1.797 x 10308 and up to 15 significant digits. Storage requirement is eight bytes.
• Replication ID — For storing a GUID that is required for replication. Storage requirement is 16 bytes.
• Decimal — For numeric values that range from -9.999… x 1027 to +9.999… x 1027. Storage requirement is 12 bytes.

Numeric and date/time fields can have a specified format to define how the data is displayed. Short text, long text, and hyperlink fields usually have no format. Predefined numeric field formats include:

• General Number – (Default) Display the number as entered.
• Currency – Use the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal and currency symbols, and decimal places.
• Euro – Use the euro symbol (  ), regardless of the currency symbol specified in the regional settings of Windows.
• Fixed – Display at least one digit; follow the settings specified in the regional settings of Windows for negative amounts, decimal and currency symbols, and decimal places.
• Standard – Use the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts, decimal symbols, and decimal places.
• Percent – Multiply the value by 100 and append a percent sign (%); follow the settings specified in the regional settings of Windows for negative amounts, decimal symbols, and decimal places.
• Scientific – Use standard scientific notation.

When it comes to date/time fields, Access automatically displays the date and time in the General Date and Long Time formats. The dates appear as, mm/dd/yyyy in the U.S. and as, dd/mm/yyyy outside the U.S. where mm is the month, dd is the day, and yyyy is the year. The time is displayed as, hh:mm:ss AM/PM, where hh is the hour, mm is minutes, and ss is seconds. Here are some other predefined formats for date/time fields:

• Long Date – Displays only date values, as specified by the Long Date format in your Windows regional settings. i.e. Monday, May 13, 2018.
• Medium Date – Displays the date as dd/mmm/yy, but uses the date separator specified in your Windows regional settings. i.e. 13-May-2018.
• Short Date – Displays date values, as specified by the Short Date format in your Windows regional settings. i.e. 5/13/2018.
• Medium Time – Displays hours and minutes followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings. i.e. 9:23 AM.
• Short Time – Displays only hours and minutes. Access uses the separator specified in the Time setting in your Windows regional settings. i.e. 9:23.

Other popular (not all inclusive) field properties that appear based on certain data types include:

• Decimal Places – Specifies the number of decimal places to use when displaying numbers.