Teedee Access Teedee 2010
Create Customer Table

Fields Required

We would like to store the following information for each of our customers CompanyName Using CompanyName rather than Name gets around using a reserved word. BillingAddress This field will be a multi-line field to allow us to use the standard two-line entry or expand it to three or more. Single line entries are also OK. Contact This will be the full name, for example Joe Bloggs. In this project we will not be using name parts ContactPhone Terms The number of days in which payment is required Email This will allow us to optionally email our invoice
1

Open The Table Editor

  In the Database Window ensure that Tables is selected At the top of the Database Window click New In the New Table dialog which is then displayed select Design View and click OK We want the Field Names listed above (CompanyName, Billing Address, Contact, ContactPhone and Email)
2

Customer Table

Data Type

When the Data Type box is selected there is a drop-down list of the options. Typing the first letter will display the required option. For example, After you have entered CustomerID and pressed the TAB key the data type for CustomerID Text is automatically displayed and selected. Type A (case does not matter) and Autonumber will be displayed

Description

The third column in the design view is Description. Anything you enter in this column will be displayed in the Status Bar (at the bottom of the Access window when you are entering data and have selected that field.

Primary Key

The CustomerID row has a Key displayed at the start of the row (See Customer Table Definition diagram above). This indicates that CustomerID is the Primary Key of this table. Most tables have a Primary Key. In Access it does not have to be the first row. A Primary Key has unique values and is indexed for quick selection of the record. Primary Keys may not have a blank (NULL) value; this is why they often are Autonumber fields.

Autonumber

An Autonumber field is either incremental, starting at 1 and incrementing by 1, or random. You cannot edit the value in an Autonumber field. The value is automatically assigned when the record is created and the value is NEVER re-used. If the record is deleted you cannot assign the number of the deleted record to another record. This is useful for audit tracking.
3 4
Full Field Definitions We would like to store the following information for each of our customers CustomerID AutoNumber PrimaryKey Field Size: Long Integer New Values: Increment Indexed: Yes (No duplicates) CompanyName Text Field Size: 255 Required: Yes Allow Zero Length: No Indexed: Yes (Duplicates OK) BillingAddress Text Field Size: 255 Required: No Allow Zero Length: Yes Indexed: No Contact Text Field Size: 50 Required: No Allow Zero Length: Yes Indexed: No ContactPhone Text Field Size: 50 Required: No Allow Zero Length: Yes Indexed: No Terms Number Field Size: Integer Decimal Places: 0 Default Value: 30 Required: No Indexed: No Email Text Field Size: 255 Required: No Allow Zero Length: Yes Indexed: No

NOTES

Primary Key

In a table the Primary Key is a value unique to each row. In our Customers table, for instance, we may have multiple entries for Company XYZ Products, one in Melbourne and one in Sydney. The CustomerID which is an Autonumber field contains unique values so XYZ in Melbourne will have a different CustomerID from XYZ in Sydney. Access automatically indexes a table on the Primary Key

Index

An Index is a hidden lookup table Access maintains for its own use. Indexes (No! Not Indices) make finding data very quick. However, there is a downside. Access has to maintain its Indexes when you enter or modify data. It (Access) is very good at this and very quick. The more Indexes it has to maintain, the slower your entry will be and the bigger your database will be. There is no rule for the number of Indexes. Note that in the field definitions opposite Company-Name is indexed (Duplicates OK) to allow for multiple entries of the same company in different locations.

Field Size

Access employs Sparse Array This means that if you allocate, say, 100 characters for a the size of a text field but only use 20 characters, 80 blank characters are not stored. There is a small amount of overhead. Always try to size your fields appropriately.
Home Tutorial Code Snippets