Access - Tables

Introduction

You will find in the tables three important elements of a database; the list of fields, the properties of each one and the data that's in them.Tables are structured. The structure contains the list of fields as well as the properties or the characteristics of these fields. This page gives you not only the information to create of a table, but also how add and change fields in it.

The first part of this page consists of creating a table to keep some information about the employees of a company. You will see in this way all the stages for the creation and the management of a table.
At the time of the creation of your tables for your needs, don't especially forget the phase of analysis. A good analysis in depth of your needs and the data that you need to reach your expectations can at the end save you time and escalations a lot. This phase is explained in more details on Access's introduction page.
Before we begin, here are some explanations on the fields of the table Employees. Every recording of the table should have the name and the First name of the person. The post is the occupation that the person occupies in the company. For this example, there are only two types of occupation: the managers and the salesmen. The field office(desk) indicates the office location. For this company, there are employees in Montreal and in Quebec. The managers work according to a salary whereas the salesmen work only on commission. To end, it is necessary to have the date of hiring of the employee.
Here is the list of the fields of the table Employees so that the compulsory characteristics. Besides giving a name to the field, it is necessary to determine it a type and, in most of the boxes, the size. It is necessary to indicate the size of fields according to the number of characters that you want to keep in these fields.

Name of the field	Type of field	Size	Format
First name	Text	15
Name	Text	15
Occupation	Text	10
Office	Text	15
Salary	Monetary
Commission	Monetary
Hiring	Date		Abbreviated date

Create a table

This exercise consists in creating a table that contains of the information about the employees of a company. Before even entering of the information, it is necessary to give a structure to this table. It is necessary to determine a list of fields as well as the characteristics of these fields. It is only after this stage that it will be possible to enter of the information.
Click on the Tables tab .
Press on the New button.

Access offers you several ways to create a table of data. The mode worksheet shows you a grid in that you write simply the information that you want to keep. You can then choose the mode creation to change the options that Access had determined for you. The mode creation contains all the options to create and personalize the structure of a table of data. The assistant Banks gives you of the assistant to the creation of a table by asking you to choose among a list of fields that you will find mostly in a table. You can also import a table from another base of datum of Access or even a working sheet of Excel. The last option allows you to make(do) a link for another table of another data base and to reach its information.
Of the list of choices, select the Creation mode option.

1. Give a name to the field

During the addition of a field in the structure of a table, it is necessary to give it a name and a type of field. According to the type of field, it is also necessary to indicate to Access the number of characters that you want to protect in the field. You can also, in your choice, add a comment to give more information about the contents of the field.

The types of fields

Access the choice between several types of field gives you

Text	Can contain figures or letters. By default, Access indicates that the size of the field is 50 characters. You can always modify the size of the field according to your needs. The maximum is 255 characters.
Memo	Useful to register comments. The size of the field is not definite. The maximum is 65 535 characters.
Numeric	Can contain figures only. You can also determine the name of figures after the decimal according to your needs.
Date/Time	Can contain or show dates or even of the hour. Although a date or one hour is shown, Access keeps these data in the form of figure. Every figure represents a day. Figure 1 is by January 1, 1900. The figure 2 by January 2... Access keeps the hour, the minutes and the seconds in fractions of day. So 0,5 is the equivalent of noon, 0,75 of the 18 hours(o'clock) etc. It is also easier to make(do) the difference between two dates.
Monetary	It is about a type of numeric field. The size(format) of the field is already ready to show the sign $.
NuméroAuto	Whole numeric field that increases automatically by one (1) in every new recording that you add to the table. Formerly called meter. It is ideal for addition of new invoices, commands(orders) or customers.
Yes/No	Logical field. Determine if a box applies or not. Ex: smoker, no-smoking. Access keeps the information under numeric format. 0 = false, 1 = really.
Object OLE	A link for an object resulting from applications Windows allows to make(do). It is capable of managing by binding(connecting) or by implanting in the data base (Object Linking and Embedding).
Hypertext link	A hypertext link for another object allows to make(do) or for one of the services of the internet that allows the URL (Uniform Ressource Locator) such as FTP, to gopher, and newsgroups.
Assisting list of choice	This assistant allows you to reach a list of predetermined choice that is contained in another table or query. This list can be the contents of one tables, of a query or what you prepared even you.

In the choice of the user, it is also possible to add a description. This is practical when you return some months later and want to know why you chose to have these fields in the table. This description is also going to appear in the left lower corner of the window at the time of the entry or the modification of the data.

The properties of the field

At the bottom of the screen, there are the characteristics or the properties of the field. Each is practical under the circumstances. The next part explains each of these properties and when these are practical. You can also fetch more information about one of the properties below by placing the pointer on this one and by pressing on the key F1.

Size:	Determine the number of character that can be contained in a field. This property is only valid for the fields of type Text. The other types of fields already have a predetermined size.
Format:	Determine the way that the information will be shown in the field.
Mask of seizure:	Determine the way that the information will have entered the field.
Legend:	Text that will be shown in the worksheet, the forms and the reports in the place of the name of the field.
Value by default:	Determine an initial value during the addition of new records.
Valid yes:	Place of the borders or limits on the kind of information that can have entered a field. For example, few persons would be ready of working for a negative salary.
Message if error:	View this message if the contents of the field don't respect the limitations of the valid property yes.
Null Interdit:	If active, does not allow to have an empty contents in the field.
Authorized empty chain(channel):	Allows to insert spaces " " into a field.
Being indexed:	This property is to show the information in increasing order during the posting. It is also necessary during the creation of relations with the other tables.

These last properties are those that are available for the fields of type Text. Here are some available supplementary properties for the other types of fields.

Decimals (Numeric, monetary):	Determine the number of decimals that will be kept with the figure.
New Values ( NuméroAuto):	Determine if the next figure will be the answer of the series ( Incrément) or a (at random unpredictable) figure.

To have more information about the properties of fields:
Place the pointer in the property for that you want more information. Press the F1 key.
Access's assistant will show you the information that it possesses on this property.

Primary key

A primary key is a field, or a series of fields, that helps to differentiate a recording of all the others of the table. For example, one can use the number of social assurance to differentiate each from the employees of the company. There is no two that are identical. One can also use a number of inventory to differentiate each of the products of the others etc.
A primary key can also consist of several fields. As long as the combination of field differentiates a recording of the others. For example, one can add a booking knowing that there will be two persons who will appear at the same time with the same name. If it would be the box, it would be necessary to add another field to the primary key to differentiate them. Or, one could simply create a field No. of booking to solve the problem. The use of several fields to compose a primary key is very rare but possible.
The method to insert the primary key on one or several fields of the table will be explained a little farther on this page; at the time of the modification of the table.

Secondary key

A secondary key is a field that is connected with the primary key of another table. For example, the field of the table Charges can be connected with the key No. of customer of the table Customers.

Save the structure of the table

Having created a table, it is necessary to keep it.
Press the button.
OR
From the File menu, select the Save option.
Access will ask you that is the name of the new table.
Enter the name of your choice. For this exercise, enter the name Employees and press the OK button.
Access goes possiblement to ask you if you want to add a primary key to your table. For the moment, this table does not need primary key. It will be added a little farther on this page.
Press the No button.

Data entry

Now the structure of the table is defined, it is possible to enter of the useful information for the user; data. To begin, there are data on the four employees of the office(desk) of Montreal. The next stage consists in entering these data the table.
Press the button.
OR
From the View menu, select the Data mode option.
Access will show you a grid to enter the data just like this one but without the data.

Enter the following information about the employees of the company.

First name	Name	Occupation	Office	Salary	Commission	Hiring
Roger	Lepage	Manager	Montreal	50000		92-01-01
Denis	Lambert	salesperson	Montreal		43000	92-01-01
Suzanne	Being Rémi	salesperson	Montreal		65000	93-01-01
Éric	Gendron	salesperson	Montreal		23000	93-06-06

Note: The last row of a table or a query always shows one "*" in the grey box to the left of the row. This is to indicate the end of this one. enter figures without the signs $. In the laboratory, enter dates size(format) year ( 2 figures), month, day with a hyphen among each.

Insert a mask of seizure

A mask of seizure is to indicate to Access the way that the information entered a table. You can force the user to enter the data in a certain way. For example, it is unthinkable to have letters in a phone number or a social number of assurance. Also, a postal code is a series of letters and figures. How to make sure that the data will have entered in a right way? It is for that reason that there are the masks of seizure.
Access already has some sizes (models) for the data that you will find mostly in a data base. The next part is to demonstrate how to reach you these models. You will apply it according to your needs in your own tables.
Place the cursor on the field that you want to add a mask of seizure.
From the list of properties, click that of the mask of seizure.
At the end of the box, a button with three little dots inside has just appeared.

Press this button.
Access is maybe going to ask you to protect your table before being able to continue. protect it. If Access asks you also for a primary key, for the needs of the exercise, don't add it for the moment.
The window with the list of the models has just appeared.

There are several models. Each is for a different occasion. You can use them to enter a phone number up to a password.
When you will have chosen mask of seizure, press the Next button.
The following window will appear.

This window shows you the mask of seizure. You can experiment by writing of the text in the box To try. It is possible to you to change the size(format) of the mask of seizure better to answer your needs. By placing the cursor in the box Masks of seizure and by pressing on the key F1, you will have a list of the possibilities for the size(format) of the mask of seizure. This list is very detailed and should be read attentively.
When you will have determined the mask of seizure, press the Next button.

Access asks you then how that the information will be stored in the field: with or without the symbols of the mask of seizure (the brackets, the spaces as well as the hyphens).
Select and press the Next button.
A last window should appear to indicate you that the creation of the mask of seizure is ended.
Unless you wanted to change an option by pressing on the Previous button, press the End button.

Add a mask of seizure

The previous option how showed to use the masks of existing seizures. But, it is also possible to you to add your own masks of seizures better to answer your needs. The text that follows shows the stages to be followed to create a new mask of seizure. The next exercise will create a new mask of seizure for a code of inventory. This code consists of three letters, a hyphen and four figures.
Enter the mode of mask of seizure by pressing on the button (....)
Press the button located in the left lower corner of the window.
Press the button to return you to the end of the list of the masks of seizures and be able to add a new of it.
A window will appear of whom(that,what) all the boxes will be empty. It is to you to enter the name of the mask of seizure, its presentation, if there are reserved spaces. You can even add an example of the mask of seizure.
For the purpose of the example, enter the text following in the appropriate boxes.

After you entered the wanted options, press the Close button.
You can add as many masks of seizure as you want.

Insert a Valid if

The property Confirms If allows to put limits or borders in the entered of data. For example, there are very few persons who like working for a salary or a negative commission. The exercise consists in demonstrating ValideSi's functioning by using it not to allow negative values fields Salary and Commission.
Open the Employees table in creation mode.
Place the pointer on the Salary field.
The properties of the field will appear in the section of bottom.
Place the pointer in the Valid box Yes.
It is in this case that you put the criteria of validation. You can use various operators (= >, <, < =, > =, < >, Among and, or, Not...) to create the criterion of validation. For the exercise, it is necessary to make sure that the value that entered the field is not negative.
In the Valid If box , enter the following criterion: > = 0.
In the exercise, it is possible that a person earns no salary or no commission according to the occupation of the employee in the company. The managers earn only a salary whereas the salesmen earn only a commission on sales. So, it is possible to have a salary or a commission equal to zero.
The box Message if error is the text that will appear to the screen when the contents of the field respect the criteria of validation. In that box, if the user enters a negative figure.
In the box Message if error, enter the following text: An employee of the company can not have a negative salary. enter a positive figure or a zero.
repeat the operation for the field Commission.
The next time that you will enter or will modify the contents of the records, try to enter a negative value to see the result.

Modify the table

After a while, you will notice that changes are necessary for a table. It needs new fields. Or, certain fields need to be modified or simply eliminated.
From the mode worksheet, press the button
OR
From the View menu, select the creation mode option.
OR
From the main menu, select the table and press the Modify button.
For the example, it is necessary to add four new fields: Numéro of poste (Employee's ID number), status, permanent status and comments. The field Numéro of poste (Employee's ID number) will be used to differentiate each of the records. It will be the primary key of the table. Furthermore, the field Numéro of poste (Employee's ID number) will be inserted at the beginning of the structure of the table. The field Status will serve for saving the marital status of the employee. This field will be the Numeric type. It's going to keep the marial status according to a number: 1 = bachelor(single woman), 2 = married without dependent, 3 = married with dependent, 4 = divorced, 5 = divorced with family allowance. So, the only valid rags are 1 and 5. It will be also used during the creation of a form. The field Permanent will be the type Yes/No. It's going to determine if the person with receipt its permanent status in the company. The field Comment will be of type memo. It will be possible to write comments, about the performance of each from the employees of the company.

Name of the field	Type of field	Size	Format	Other
Numéro of poste (Employee's ID number)	Meter
First name	Text	15
Name	Text	15
Occupation	Text	10
Office	Text	15
Salary	Monetary
Commission	Monetary
Hiring	Date		Abbreviated date
Status	Numeric	Whole length		Valid yes: Between 1 and 5
Permanent	Yes/No
Comments	Memo

Insert a field

Place the pointer on the row that you want to insert a new field at the table. For the exercise, place the pointer on the first row of the table, that is the row of the First name field.
From the Edit menu, select the option Insert row.
OR
Press the button.
A new free row will appear. The other rows will be moved downward.
write the name of the field. For the exercise, it is about Numéro of poste (Employee's ID number).
Select the type of field Text with a lenght of 15 characters.
insert the new fields Status, Hiring and Comments at the end of the table with their properties that are described in the table above.

Move a field

To change the order of presentation of fields
Click the grey box to the left of the name of the field.
While pressing the left mouse button , move the field upward or to the bottom according to your needs.
The field will re-fit into the structure of the table between both fields among that the row that separates it is more thick as soon as you will release the mouse button.

Delete a field

Click the row of the field that you want to delete. Press the Delete button .
OR
From the menu Edit, select the Delete rows option.

Place the primary key

The primary key is a field, or a series of fields, that allows to distinguish each of the records. Every person in its social number of assurance, a number of customer or employee. It can consist of letters or figures. One of the things that Access verifies later is to make sure that there are two records that will have the same information in the field. For example, two persons can have the same number of social assurance or two products the same number of products.
Access will not allow the creation of the primary key if two records have the same information in the chosen field. If you need absolutely that both records have the same information, it would be necessary to consider another field, or combination of fields, for the primary key of the table.
A table is not obliged to have a primary key. It needs however a primary key if the other tables want to reach the information of this table. For example, the table Charges its address and the other information could, thanks to the relations between tables, reach the information of the table Customers to know the name of the customer. So, to find more quickly the information and to make sure that there are two customers with the customers' same number, it is necessary that the field Customer's number of the table Customers is the primary key of this table.
For this exercise, the new field Numéro of poste (Employee's ID number) will be the primary key of the table.
Place the pointer on the row of the field that will be the primary key of the table. For the exercise, it is about the field Numéro of poste (Employee's ID number).
Press the button.

The key placed in the box intoxicates to the left of the field Numéro of poste (Employee's ID number) confirms that the primary key is on this field.
If by mistake, you chose the bad field as the primary key, press the button for déselectionner the field as the primary key.

Place a primary key on several fields

A primary key can also consist of several fields. There are even occasions where it is necessary to have a primary key in this way. For this exercise, the primary key will consist of fields First name, Name and Date of hiring.
Click the small grey box to the left of the First name field.
By keeping one owes on the CTRL key, then click the grey boxes to the left of fields Name and Hiring.
Press the button.
Here is the final result.

The key placed in the grey box confirms that the primary key consists of fields First name, Name and Hiring. It means that it is possible to register several times the same value in one of the fields. But it is impossible to register several times the same values in all the fields that compose the primary key at the same time. For example, it is impossible to have two persons with the same First name and name that are hired at the same time.
For the purpose of the next exercises, replace the primary key on the field Numéro of poste (Employee's ID number).

Adding records

bring the additions to the already created records and add the other records to your table.

Numéro of poste (Employee's ID number)	First name	Name	Occupation	Office	Salary	Commission	Hiring	Status	Permanent	Comments
1	Roger	Lepage	Manager	Montreal	50000		92-01-01	2	Yes
2	Denis	Lambert	salesperson	Montreal		43000	92-01-01	1	Yes
3	Suzanne	Being Rémi	salesperson	Montreal		65000	93-01-01	1	Yes
4	Éric	Gendron	salesperson	Montreal		23000	93-06-06	5	No
5	Roger	Dubuc	Manager	Quebec	43000		93-01-01	1	Yes
6	Elects	Lavigueur	salesperson	Quebec		47000	93-06-01	2	Yes
7	Paul	Gendron	salesperson	Quebec		22000	93-01-06	1	No

Sorting the records

At the time of showing the contents of the table in mode sheet of data, the option of the sorting shows the records in increasing or lessening order according to a field.
In the sheet of data mode, you will find in the toolbar the following buttons: . These
Place the cursor in the column that you want to show in increasing or lessening order.
Press one of the buttons to show the table according to this field in the order that you want.
All the records will be shown according to the order for that you asked on the field where is the pointer at this moment.

The filter

The option of the filter allows only to show a part of the records: the one that answers the criteria that you determined before. This is very advantageous when the user wants to find quickly some records among a lot of data.
From the sheet of data mode, you will find buttons to create, activate and deactivate the filter on a table or a query: .

Creating a filter

Determine the criteria of selection. You can choose only to show a part of the records; those that answer certain criteria.
From the mode worksheet, press the button.
The list of the fields of the table will appear the one next to another.
To remove the previous filters, press the button.
It remains only to put the criteria only to show the records that answer these conditions. The exercise consists of showing only the salesmen of the region of Quebec. So, will be needed two criteria: the one for the Occupation field and the other one for the Office field.
Place the pointer in the box Occupation.
Press the triangle pointing down at the end of the box.
From the list, select the salesperson occupation.

Place the pointer in the Office box.
Select Quebec from the list.
The list of choices makes easy to find the information. It only shows what is contained in the field. But, it is also possible to create its own criteria. For example, it is possible to know that earns a salary superior to 45 000 $ by putting the operation > 45000 below the Salary field.
It is also possible to combine criteria. In this exercise, two criteria are combined. Occupation equals to salesperson AND Office equals in Quebec.

Activate a filter

To activate the filter, press the button.
OR
From the Filter menu, select the Apply the filter option.
Access will show you the records that answer the criteria that you chose.

Deactivate a filter

Press the button.
OR
From the Record menu, select the option View all the records.

Print the structure of a table

From the Tools menu, select the analyzes option.
Select the documentation option.
The following window will appear.

Select one or several tables the structure of that you want to print.
Press the Options... button.
The following window will appear.

Select the options that you need.
Press the OK button.
Once having returned to the window of documentation, press the OK button.
Access will generate the state for that you asked. You can see it in the screen or print it:
By pressing on the button.
OR
From the File menu, select the option Print.
OR
Press the CTRL and P keys.

Export a table

Access gives you the possibility of exporting the data of tables. These can be exported towards other data bases or in the size(format) of the other applications such as Excel or Word. You can in this way take advantage of the possibilities of these for calculations, analyses or mailing for example. It's to note however that it's better to export from the queries if you need only a part of the records and not the whole.
From the main menu, select the Tables tab .
Click on the table that you want to export.
From the File menu, select the Export Record option.

Select the To an external file or a data base option.
Press the OK button.

Select the name of your choice.
Select the type of file format that answers your needs.
Press the Export button.
Access will create a new document with the data of tables in the size(format) that you chose.

Import a table

Import's option allows you to go look for data resulting from the other data bases or from the other applications and to add them to your data base. The next exercise consists in importing data from a file Excel. If you have not already made it, protect the file demodb.xls from the page of the demonstration files on a floppy disk.
From the main menu, select the Tables tab .
Click on the table that you want to export.
From the File menu, select the External Data and Import .

The next part presumes that you have to protect the file demodb.xls on a floppy disk.
Of the available list of the drives box, select Floppy disk 3 ½ ( A ).
Of the list of the Type menu of file, select the Microsoft Excel option.
From the list of files on the floppy disk, select the demodb file.
Press the button Import.
OR
Double-click on the demodb.xls file.

Access needs to know from the worksheet of Excel about that you will find the data to be imported. For the exercise, these data on the sheet of work named Sheet1. There is no other information about this sheet other than the data to be imported. Otherwise, it would have been necessary to give a name to the block of cells containing the data to be imported. The assistant of import calls it the option " View the named(appointed) beaches ".
Select the View the worksheets option.
Select the worksheet named Sheet1.
Press the Next button.

Access asks you if the data of the first row of the working sheet are the names of the fields of the table to matter. In that box, it is true that the data of the first row are the names of fields.
Click on the option First row contains headings columns.
Press the Next button.

Access asks you if you want to create a new table to store these data and add them to a table that already exists. For the exercise, the data will be kept in a new table.
Select the option In a new table.
Press the Next button.

Access asks you now for more information about each of the fields, or columns, the data to matter. To change the properties of each of the fields, it is enough to click the grey box containing the name of the field. You can then change some properties such as the name, the indexation and if you want to import the field or not.
If the first row of the data to be imported was not that with the name of fields, you can change it now by entering the name of your choice the box Name of the field. Even if you answered yes the question two windows before, you can again change the name better to answer your needs.
Access asks you also if you want to index the field. Choices are not, yes with doubloon and yes without doubloon. This helps to sort out recording and so necessary during the relations between tables. It accelerates the access to the data in the connected tables. place a sorting only on the necessary fields for your needs. You slow down the presentation of the data when you place the sorting on several fields of the table.
A last option gives you importer's choice only the fields that you need. It is still possible to you to exclude a field of the import. It is enough to choose the name of the field and to tick the box not to import the field.

Access asks you then for that name you go to look to the table that you import. This implies that you don't import the data in a table that already exists.
You have also the possibility of activating the assistant of analysis of structure. It analyzes the data and looks for halvings information. He asks you then if he can cut the data in several tables to have the most optimal possible structure of tables. For the purpose of the exercise, don't activate this option. You can also have of the supplementary assistant when the assistant in ended his work.

Access will confirm you the import of the data according to the choices for that you asked.
Press the OK button.

Here is the result of the import if you open the table Employee 2.

Linking a table

To link a table to the data base the advantage gives you to reach data that are outside of this one without enlarging it for all that. You can also add or remove data of the outside table. The disadvantage is that you can not change the structure of this table. The next exercise consists in binding(connecting) the same table as you mattered in the previous exercise. Some of the stages to be followed to link a data base external look like those of the import.
From the File menu, select the options external data and Link.

The next part presumes that you have to protect the file demodb.xls on a floppy disk.
Of the available list of the readers of the Drive, select Floppy disk 3½ ( A ).
Of the list of the Type menu of file, select the option Microsoft Excel.
From the list of files on the floppy disk, select the demodb file.
Press the Paste button.
OR
Double-click on the demodb.xls file.

Select the option View the worksheet.
Select the worksheet named Sheet1.
Press the Next button.

Access asks you if the data of the first row of the working sheet are the names of the fields of the table to matter. In that box, it is true that the data of the first row are the names of fields.
Click on the option First row contains headings columns.
Press the Next button.

To end, Access asks you that is the name that you want to give to the attached table.
For the purpose of the exercise, write in the Name of the attached table box: Employees 3.
Press the End button.

Access confirms you that the data of the working sheet Sheet1 of the file demodb.xls are now accessible from your data base.

To differentiate the tables of the data base of those that are attached, Access places an arrow in front of the attached table. Because the data result from the Excel spreadsheet, the icon of the application appears also.