Table Field Data Types

When creating table fields, it’s important to select the correct data type for each field, so that you can enter data into the field (you can’t enter text into a numeric field!), and also use the data for sorting and filtering as needed. The table below lists the field data types available in Access tables, with comments. The primary data type is what you see in the drop-down Data Type list when creating or editing a field; some fields (Numeric and AutoNumber) also have subtypes, which are selected from the Field Size property in the field properties sheet.

Primary Data Type	Subtypes	Description	Comments
Text		Text data up to 255 characters in length.	Use for text data, and numbers (such as IDs) that are not used for calculations.
Memo		Blocks of text up to 65,535 characters in length.	Use for long text; only limited sorting is available on this field (just the first 255 characters are used).
Number	Byte	Whole numbers from 0 through 255.	Small numbers with no decimal points.
	Integer	Whole numbers from –32,768 through 32,767.	Medium-sized numbers with no decimal points.
	Long Integer	Whole numbers from –2,147,483,648 through 2,147,483,647.	Long numbers with no decimal points. This is the default value. Matches AutoNumber fields when linking tables.
	Single	Numbers from –3.402823E38 through –1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values.	Accurate to 7 decimal points.
	Double	Numbers from –1.79769313486231E308 through –4.94065645841247E–324 for negative values and from 4.94065645841247E–324 through 1.79769313486231E308 for positive values.	Accurate to 15 decimal points.
	Replication ID	Globally unique identifier (GUID), a 16-byte field used as a unique identifier for database replication.	Only used in replicated databases.
	Decimal	Numbers from –10^28–1 through 10^28–1.	Accurate to 28 decimal points.
Date/Time		Dates or times.	Always store dates in a Date/Time field, so you can do date and time calculations on them.
Currency		Currency values, or numbers that need great accuracy in calculations.	Use a Currency field to prevent rounding off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right.
AutoNumber	Long Integer	Incrementing sequential numbers used as unique record IDs.	Same data type as Long Integer for linking purposes. There may be gaps in the numbering sequence, if records are created and later deleted.
	Replication ID	Random numbers used as unique record IDs.	This is a very long and strange looking string.
Yes/No		Data that is either True or False.	Null values are not allowed.
OLE Object		Documents created in programs that support OLE (such as Word or Excel).	You can’t see the object in the table; use a form or report control to display it. Can’t sort or index on this field.
Hyperlink		URLs or UNC paths.	You can click on a value in this field to open a Web site (if it is a valid link).
Lookup Wizard		Not a separate field type, but a wizard that lets you select a table or value list for selecting a value for a field.	Once a field is set up as a lookup stored in the field in table Datasheet view.

Whether they are created by the Table Wizard or manually in Design view, I prefer not to use lookup fields in tables, but instead select the lookup table in a combobox or listbox’s RowSource property on a form. The reason for this is that if you set up a field (such as VendorID) as a lookup field, then you won’t see the VendorID when you look at the table in Datasheet view—just the vendor name from the lookup table. If you need to see the actual VendorID, you will have to convert the field back to a standard field. Also, a lookup field will always be placed on a form as a combobox, while you may prefer to have the field displayed in a textbox, for example on a read-only form.

The tblVendors table as created by the Table Wizard is shown in Figure 1.14.

Figure 1.14

Inportant

You can use the F6 function key as a shortcut for moving between a field in table Design view, and its properties sheet. This hot key is especially handy when setting the FieldSize property of numeric fields.

Because the client said that vendors could sell either finished toys or raw materials, the table needs two Yes/No fields to indicate whether the vendor sells finished toys, materials, or both, so I added these fields, setting the default value of the SellsToys field to True (since the client said that the majority of his vendors sell toys), and the default value of the SellsMaterials field to False.

Although Access lets you use spaces (and most punctuation marks) in field names—note the slash in the Country/Region field in tblVendors—I prefer not to use spaces or punctuation marks other than underscores in field names, to prevent problems when referencing fields in code and SQL statements, or when exporting table data to other applications that may not support spaces or punctuation marks.

As is often the case, on examining the table I realized that I needed to ask the client some more questions. It is rare to find all of the information you need to create tables at one time, right at the beginning. You will need to confer with your client from time to time while creating the application, asking more specific questions in order to refine table structure as needed. The question here is whether the vendors are all in the United States, or if there are some non-U.S. ones. If all the vendors are in the United States, we can eliminate the Country/Region field, and put input masks for U.S. state abbreviations or zip codes on the PostalCode and StateOrProvince fields; otherwise, the CountryRegion field is needed (I removed the slash in the field name), and we have to either leave the PostalCode and StateOrProvince fields without input masks, or take care of formatting by swapping input masks on a form or running event procedures to check that the correct data is entered into these fields.

1.	Are the vendors are all in the United States, or are there some non-U.S. ones?
2.	Do you need just one phone number and one fax number, or could vendors have more than one phone number? Also, is one email address enough?
3.	Do you need to send Word letters to vendor contacts?

Answers

1.	There are some vendors outside of the United States. Leave the table’s fields as they are. Now some more questions arise:
2.	Some vendors have cell phone numbers too, and multiple email addresses. We need to remove the phone and email fields from tblVendors, and create separate linked tables to hold this data.
3.	No, the name is just so we know whom to ask for when we call the vendor. Then we don’t need to break up the contact name into its components, as would be required if we were going to create letters to them.

Figure 1.15 shows the final tblVendors.

Figure 1.15

The linked tables require only the VendorID field and fields for (respectively) phone descriptions and numbers, and email addresses; the VendorID field in the linked tables is a Long Integer, to match the AutoNumber VendorID in tblVendors. When saving the new tables, don’t create a key field; VendorID is a foreign key in tblVendorPhones and tblVendorEMails. (See the “Relationships” section later in this chapter for a definition of foreign key.) Since the vendors could be outside the United States, there is no need to create an input mask for the VendorPhone field. Figure 1.16 shows the two tables of vendor phone and email data; they will be linked to tblVendors in the Relationships window later in this chapter. tblVendorPhones has a phone number field and another field for the description (work, home, fax, and so forth), which allows you to enter as many different phone numbers as are needed for each vendor, each with its own description.

Figure 1.16

Continuing with table creation for the Toy Workshop application, tblCustomers can be created using the Table Wizard Customers table template, using all the fields to start with (just changing CompanyOrDepartment to Department), and setting up no relationships. The starter tblCustomers is shown in Figure 1.17.

Figure 1.17

As with tblVendors, we have a few questions for the client:

1.	Do you need just one phone number and one fax number for customers, or could customers have more phone numbers? Also, is one email address enough?
2.	Are the customers all in the United States, or do you have foreign customers too?

Answers

1.	Some customers have cell phone numbers too, and multiple email addresses. Come to think of it, some of them have Web sites, too. We need to remove the phone and email fields from tblCustomers, and create separate linked tables to hold this data. And we need to add a WebSite Hyperlink field.
2.	The customers are all in the United States. We can remove the Country/Region field, and put appropriate input masks on the StateOrProvince and PostalCode fields.

The finished tblCustomers is shown in Figure 1.18.

Figure 1.18

tblCustomerPhones and tblCustomerEmails are similar to tblVendorPhones and tblVendorEmails. We also need another linked table, to hold shipping addresses (this was determined in the initial Q&A session). The billing address can be stored directly in tblCustomers, because there is only one billing address per customer, although there could be multiple shipping addresses. tblShippingAddresses has an AutoNumber ShipAddressID field, the linking CustomerID field, an address identifier field (for selecting a shipping address from a combobox on a form), and a set of address fields. Although the address fields could have the same names as the corresponding fields in tblCustomers, I like to prefix their names with “Shipping” or “Ship” so that if the billing and shipping addresses are combined in a query, we won’t need to use the table name prefix to distinguish between them. tblShippingAddresses is shown in Figure 1.19.

Figure 1.19

The next table is tblMailingList, which is created from the Mailing List template in the Table Wizard, omitting all fields except name, address, and email fields, plus DateUpdated and Notes. The tblMailingList table is shown in Figure 1.20.

Figure 1.20

On examining the initial version of this table, it occurs to me that the mailing list could contain several persons at the same company, so the company information should be broken out into a separate table, linked by CompanyID. However, some people on the mailing list might not be affiliated with companies, so we’ll leave the address fields in the table, for entering personal address data, and add a CompanyID field to link to a separate tblMailingListCompanies table, for records that need it. The modified tblMailingList and tblMailingListCompanies tables are shown in Figure 1.21. When a mailing list record is entered on a form, the tblMailingList address fields will be enabled only if no company is selected for the CompanyID field; if a company is selected, its address will be used for mailings to that person.

Figure 1.21

Customers will presumably get mailings too, but this doesn’t mean that the whole mailing list needs to be in the tblCustomers table—we can combine data from tblCustomers and tblMailingList with a union query, when sending out mailings (See Chapter 4, Sorting and Filtering Data with Queries, for information on union queries.)

For date fields, I recommend selecting a date format that will display four digits for years, to avoid twentieth century/twenty-first century confusion. For an individual field, you can either select one of the standard formats from the Format property of a date field, or enter a format directly, such as m/d/yyyy. See the Format Property—Date/Time Data Type Help topic for full information on date and time formatting (you can locate this Help topic by entering date format in the Answer Box or Answer Wizard). Additionally, you can turn on 4-digit date formatting globally, overriding the Format property of fields and controls, by opening the Options dialog box from the Tools menu to the General page, and checking one of the checkboxes in the Use four-digit year formatting section, as shown in Figure 1.22. While you are on this page, take the opportunity to turn off Name AutoCorrect, which is nothing but trouble, because it doesn’t make all the changes needed and sometimes makes changes when it shouldn’t. Chapter 9, Reworking an Existing Application, lists better ways to rename database objects, using my LNC Rename add-in.

Figure 1.22

The next table to create is tblMaterials, which lists the materials used to make toys. The Table Wizard Products template is a good starter, omitting the fields that aren’t needed and changing a few field names. The table is related to tblVendors on VendorID. tblMaterials is shown in Figure 1.23.

Figure 1.23

Since a material could be used for many toys, and a toy could use many materials, we need a many-to-many relationship between tblToys and tblMaterials; this is done by means of a linking table containing just the key fields; this linking table (tblToyMaterials) is shown in Figure 1.24.

Figure 1.24

There is no suitable table template for tblRepairs, so I created this table directly in Design view, with just a few fields, as shown in Figure 1.25.

Figure 1.25

Inportant

Following are some names you shouldn’t use for fields: Name, Date, Month, Year, Value, Number, Sub. In general, any word that is a built-in Access function, property, or key word should be avoided, because it is highly likely to cause problems in VBAcode and elsewhere. Just add another word to the field name (CustomerName, OrderDate), and you can avoid these problems.

Because repairs also use materials, we need a linked table, tblRepairMaterials, which lists the materials used to do repairs, and the quantity of each material. This table is shown in Figure 1.26.

Figure 1.26

The tblEmployees table is based on the default Employees table in the Table Wizard, with some unnecessary fields deleted. The client’s company uses a numeric Employee ID, but since employees in this company already have IDs, we can’t use an AutoNumber field; instead, EmployeeID is a text field, and it will be filled with existing employee numbers, with an incrementing number for new employees created by a procedure run from a form. tblEmployees is shown in Figure 1.27.

Figure 1.27

The last main table is tblOrders; it is also based on a template (Orders) in the Table Wizard, skipping the shipping address fields, and replacing them with a field (ShipAddressID) that links to tblShippingAddresses. We also need to add ToyID, to identify the toy that was purchased, and ToyQuantity (a curious omission from the Orders table template). tblOrders is shown in Figure 1.29.

Figure 1.28

Figure 1.29

The SupervisorID field takes an EmployeeID value that will be picked from a combobox on a form.

Some employee information should remain confidential, so the Social Security number (SSN) and salary are stored in a separate table, tblEmployeesConfidential, which is shown in Figure 1.28. (The input mask on the SSN field is one of the standard input masks, selected from the Input Mask Wizard.) Placing this information in a separate table lets you restrict its use to certain employees, using Access object-level permissions in a secured database. Even if you don’t want to secure your database, there is a certain measure of confidentiality in just placing the information in another table so that it isn’t visible when doing routine work on the main Employees form.

One default field in this table (ShippingMethodID) requires a lookup table of shipping methods; I created this table manually, and tblShippingMethods is shown in Figure 1.30.

Figure 1.30

Using an AutoNumber ShippingMethodID lets you select the shipper from an option group on a form (Access option group buttons have Integer values), and the selected value links to the shipper name in tblShippingMethods.