Home and Learn: Microsoft Access Course


Access Database Fields and Database Records

A database field is a single column in your table. A field has a name, which appears in the column header. By default, Access sets one field up for you and gives it the name ID. (You'll change that soon.) You can add your own fields by clicking where it says Click to Add. You can have as many fields as you want in your tables.

A record in a table is one complete row. For example, if you had a table of contacts, one record could consist of a person's first name, their last name, their address, etc. A table is a collection of records. And each record is a collection of fields.

 

Table Field Names

A field name can be almost anything you want. (Access reserves a few words for itself. If you pick a name that Access has already bagged for itself, it will let you know.) You can have spaces in your Field names, but it's not recommended. Spaces can cause issues further down the line, when you're constructing your own queries.

By convention, words in Field names are capitalised. For example, FirstName, FamilyName, MyFieldName.

 

Table Field Data Types

Each column in your table, each field, has to have a data type. This, not surprisingly, is the type of data that can be entered in each field. If you had a field called FirstName, the type of data would be text. If you had a field for prices, the data type would be numbers or, more specifically, currency. Let's add a field to our table so that you can see the various data types.

Click where it says Click to Add. You should see a dropdown list of data types:

A dropdown list of field data types in Access

You must select a Data Type for each field in your table. (See below for an explanation of the different field types.) Select the Short Text item. Access then gives you a default name for the field. It will say, Field, then a number on the end:

Arrow pointing to an Access field name

Once the field name is highlighted, you can change it. (If it's not highlighted, double click where it says Field1.) Type FirstName as the field name and press Enter on your keyboard. Annoyingly, Access then jumps to a new field and display the dropdown list of data types. You can click away to get rid of it. The Click to Add will be highlighted in yellow.

But add two more fields and set their data types to Short Text as well. Click to add a field called LastName and a field called MiddleName. Your table will then look like this:

An Access database table with four fields set up

Now click on FirstName to select the Field. Have a look at the Fields Ribbon above it. You should see panels for Views, Add & Delete, Properties, Formatting, and Field Validation:

The Fields ribbon in Microsoft Access

On the Properties panel, you can see that the Field Size is set to 255 for the FirstName field. You can change that, if you want, to anything above 0 and below 255. Unless, you live in a country where names are really, really long, a value of about 30 should be OK. But if an awkward person comes along whose has changed his name to ImCalledSuperCaliFragilListicExpialidocious then it will cause problems in your database - you'll get errors because the field size is not long enough for the name.

Click inside the Field Size box and change it from 255 to 30. Now do the same for the other two Fields. Click the LastName field to highlight it. On the Properties panel, change the value to 30. Then click on the MiddleName field and do the same - change it from 255 to 30.

While you have the Fields ribbon displayed, have a look at the Formatting panel. If you wanted to change the data type for the field, you can do it here. Click the dropdown box to see the different data types. Select the one you want.

 

Data Types

Most of the items on the list of Data Types are self-explanatory. But some need further explanation.

Short Text
This field can hold a maximum of 255 characters. These characters can be anything: letters, numbers, symbols, etc. If you choose this field, anything you enter will be treated as text. Even numbers. You can specify an upper limit, though. For example, if you’re sure first names in your table won’t go above, say, 100 characters, you can set this as the limit.

Long Text
In previous versions of Access, this was known as the Memo field. If you need to store more than 255 characters, this is the field you need. It can store a maximum of 1 gigabytes of text.

Numbers
Although it’s fairly obvious what this field stores, you can drill down further for the number type. You can specify these types of number:

Byte
Integer
Long Integer
Single
Double
Decimal

The values you can have for these numbers are as follows:

Byte: 0 to 255
Integer: -32,768 to 32, 767
Long Integer: -2,147,483,648 to -2,147,483,647
Single: -3.4 x 1038 to -3.4 x 1038
Double: -1.797 x 10308 to -1.797 x 10308
Decimal: -9.999 x 1027 to -9.999 x 1027

Only the Single and Decimal data types can have decimal places. The Single type can have up to 7 decimal place while the Decimal data type can have up to 15 decimal places. There’s also a Long Number data type. This can hold ridiculously large numbers. Unless you’re a scientist or mathematician, you probably won’t need these one.

Rich Text
This is an option available when you choose the Long Text data type for a field. Allows for some limited formatting, like font, font face, text colors, bold, underline.

Attachment
A text field where you can add attachments, like Excel files, PDFs, Word documents, etc.

Lookup
We’ll do a lookup field when we add our second table for books. This will allow us to add a Book Genre field which you can select from a dropdown list.

 

Default Field Name and AutoNumber Data Type

Let's change a Field name. The name ID is a default one. You can change this. Double-click the word ID and it will be highlighted:

Arrow pointing to an Id field in an Access table

Type the field name AuthorID instead:

An Access Id table field changed from the default

One thing to note about this ID field. With AuthorID selected, have a look at the Formatting panel again. Notice that the data type is AutoNumber:

The formatting panel in Access specifying a data type for a field

The formatting panel in Access specifying a data type for a field

1 Kenny Carney

If you were to add a new record, the second would be 2:

2 Jim Jam

The third 3:

3 Penny Dime

And all the records look like this:

1 Kenny Carney
2 Jim Jam
3 Penny Dime

You don't have to enter the numbers yourself, when your field is set to AutoNumber as Access will take care of it for you.

Let's create a second table now for the books. We'll do this one differently. The link to the next lesson is below.

<--Back to the Microsoft ACCESS Contents Page


Email us: enquiry at homeandlearn.co.uk