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 record 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 sales person. 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 sales person work only on commission.
To end, you must 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, you must determine
it a type and, in most of the boxes, the size. It's 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 |
Currency |
|
|
Commission |
Currency |
|
|
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's necessary to determine
a list of fields as well as the characteristics of these fields. It's 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 Design view option.
1. Give a name to the field
During the addition of a field in the structure of a table, it's necessary
to give it a name and a type of field. According to the type of field,
it's 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 how 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. |
Number |
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's also easier
to make(do) the difference between two dates. |
Currency |
it's about a type of numeric field.
The size(format) of the field is already ready to show the sign $. |
AutoNumber |
Whole numeric field that increases
automatically by one (1) in every new record that you add to the table.
Formerly called meter. It's 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. |
OLE Object |
A link for an object resulting from applications
Windows allows to make(do). It's capable of managing by binding(connecting)
or by implanting in the data base (Object Linking and Embedding). |
Hyperlink |
A hyperlink 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. |
Lookup wizard |
This wizard allows you to crate and manage a list of
predetermined choices for the user. That list can be from another table or query or a list that you created for this field. |
While configuring each field, you can also 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. |
Input mask: |
Determine the way that the information will
have entered the field. |
Caption: |
Text that will be shown in the worksheet, the forms and the reports in the place of the name of the field. |
Default value: |
Determine an initial value during the addition
of new records. |
Validation rule: |
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. |
Validation text: |
View this message if the contents of the
field don't respect the limitations of the valid property yes. |
Required: |
If active, does not allow to have an empty contents
in the field. |
Allow zero length: |
Allows to insert spaces " " into a field. |
Indexed: |
This property is to show the information
in increasing order during the posting. It's 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 (Number, monetary): |
Determine the number of decimals that will
be kept with the figure. |
New Values ( AutoNumber): |
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 record 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 record 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, you must 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's 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 an entry mask
An entry mask 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's 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's 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 input mask.
From the list
of properties, click that of the input mask.
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 input mask, press the Next button.
The following dialog box will appear.
This window shows you the input mask. You can experiment by writing of
the text in the box To try. You can change the
length of the input mask better to answer your needs. By placing
the cursor in the box Input mask and by pressing on the
F1 key, you will have a list of the possibilities for the size(format) of
the input mask. This list is very detailed and should be read
attentively.
When you will
have determined the input mask, 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 input mask (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 an input mask
The previous option how showed to use the masks of existing seizures. But,
you can also 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 input mask. The next exercise will create a new input mask
for a code of inventory. This code consists of three letters, a hyphen and four
figures.
Enter the mode
of input mask 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 dialog box will appear of whom(that,what) all the boxes will be empty. It's
to you to enter the name of the input mask, its presentation, if there
are reserved spaces. You can even add an example of the input mask.
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 Design view.
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's 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, you must 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's 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 sales person earn only a commission
on sales. So, it's possible to have a salary or a commission equal
to zero.
The box Validation text 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 Validation text, 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 Design view option.
OR
From
the main menu, select the table and press the Modify button.
For the example, you must 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 Number 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 Likent 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) |
AutoNumber |
|
|
|
First name |
Text |
15 |
|
|
Name |
Text |
15 |
|
|
Occupation |
Text |
10 |
|
|
Office |
Text |
15 |
|
|
Salary |
Currency |
|
|
|
Commission |
Currency |
|
|
|
Hiring |
Date |
|
Abbreviated date |
|
Status |
Number |
Whole length |
|
Validation rule: 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's about Numéro of poste (Employee's ID number).
Select the type of field Text with a length 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's 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's 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éselect 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 you must 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's possible
to register several times the same value in one of the fields. But it's impossible
to register several times the same values in all the fields that compose the
primary key at the same time. For example, it's 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 sales person of the region of Quebec. So, will be needed two criteria:
the one for the Occupation field and another 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's contained in the field. But, you can also create its own criteria.
For example, it's possible to know that earns a salary superior
to 45 000 $ by putting the operation > 45000 below the Salary field.
it's 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 a table's structure
From the Tools menu, select the Analyze option.
Select the documentation option.
The following dialog box will appear.
Select one
or several tables the structure of that you want to print.
Press the Options... button.
The following dialog box 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 Recordoption.
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's 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's 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 record 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's 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's 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.
|