Create the FARMER table and enter data
This step is demonstrated through Video1. Create Farmer Table (wmv) (67.8MB; 4.29min)
- Go to Tables>New>Design view>OK.
This will open a new table in Design view. Note that Design view has three columns- one for the field names, the other for the data types, and the third for the description of your variable. Note that description of a variable or field name is optional and is just to give the user some information about the field.
- To create a field, type the field name in the first column and select the data type from the second column. For example, type FARMERID in the first row of the first column (Field Name) and specify Autonumber as the data type.
- Similarly, create the following fields for the FARMER table (Note: To obtain further information about data types, place your cursor in any row of the Data Type column and press F1).
|Field Name||Data Type||Description|
|FirstName||Text||First name of the farmer|
|LastName||Text||Last name of the farmer|
|StreetNum||Text||Farm Address: Street number|
|StreetName||Text||Farm Address: Street name|
|Suburb||Text||Farm Address: Suburb|
|State||Text||Farm Address: State|
|Postcode||Text||Farm Address: Postcode|
|Member?||Yes/No||Is the farmer a member of the xyz association?|
|Picture||OLE object||A photo of the farmer|
|Income||Currency||Annual income in AU $|
|Notes||Memo||Other information about the farmer|
The text fields are limited to only 255 characters. Therefore, if you need to store more characters in a field, use the Memo data type instead. However, note that it is a bit tedious to analyse qualitative data, therefore, restrict the memo data type to fields that you are not likely to analyse, such as comments and notes.
- Keep your cursor in the FARMERID field and click on a small key on the toolbar to specify the FARMERID as the primary key. Primary key is used to link this table with other tables.
- Save your table by clicking the save button on the toolbar or from File>Save.
- To facilitate data entry and to avoid errors, it is useful to create look-up lists. For example, you could create a look-up list of the state names so that a user can simply select a state name from the dropdown menu. One way to create such lists is by creating a combo box:
o Click in the data type field for the State field, and select lookup wizard to start a new wizard.
o Select I will type in the values I want.
o Keep the numbers of columns as 1 and type in state names in the column Col1. Type a name (say NSW) and then press Tab to go to the next row and type another name (say QLD) and so on.
o Click Next and then Finish.
- Another good facility in Access is to define input masks. Input mask is a sort of template that facilitates data entry. You can create input masks for any field but they are particularly useful for dates and phone numbers. Let’s create an input mask for the Phone field in the above table.
o Click anywhere in the Phone field
o Notice two tabs at the bottom of the window – General and Lookup. Place your cursor in the row for the input mask property and notice a build button (three dots) at the end of the row. Click this button to start the input mask wizard.
o Select the input mask that you prefer- you could actually enter data in the Try it box to select the one that you want. As in Australia we have only two digit area codes, select the first suggestion and delete one ‘9’ from the parentheses of the input mask if the database is to be used in Australia. Click Next a couple of times and Finish the wizard.
Note that like any other field, you could place your cursor in the input mask row and press F1 to obtain further information about input masks.
There are many other properties in the General tab. We will not discuss these in detail in this tutorial but you can obtain information about these from Access Help. The first property, Field size, defines the size of a field and is set to 50 by default (you can check it by placing your cursor in any text field), but you can increase or decrease this depending on the data to be entered. For example, you can reduce the field size to 3 for the state field as you are not going to enter more than three alphabets in this field. Note that it is a good practice to keep the field size as small as possible, but only if it doesn’t impact the functionality of a database.
After creating the table, go to datasheet view by clicking datasheet view button on the table design toolbar at the top-left corner, or by right clicking on the table and selecting datasheet view (or by closing and opening the table). Enter a few imaginary records that we will use later on for data manipulations.
You have created your first table. Congratulations!
Go to >Create the ANIMAL table