Download Creating Table Part 1-MS Acess-Tutorial and more Study Guides, Projects, Research Microsoft Access Skills in PDF only on Docsity!
Lesson 12. Creating a Table
There are two parts to this, firstly the creating the actual table, and secondly defining the fields within it.
- From the database window double click on
(You can also click
, select Design View and click OK .)
- Click in the first box under Field name and type the name of the first field. (maximum 64 characters, best to avoid spaces โ use _ instead)
- Press the tab key to move to the Data Type field the word Text appears.
Click on the down arrow to see the options as shown below: Text: for any alphanumeric characters. Telephone numbers will be text since you will not want to calculate with them. (holds up to 255 characters) Memo: also for any alphanumeric characters, but usually several sentences or paragraphs. (holds up to 65,535 characters) Number: for any completely numeric values - integers or fractions. Date/Time: for date and time values Currency: for monetary values (accurate to 15 digits to the left of the decimal point and 4 to the right.) Autonumber : This creates a unique sequential number each time a new record is added to a table. Yes/No : for fields which will only one of two values (Yes/No, True / False, or On/Off). OLE Object: An object (such as a Microsoft Excel spreadsheet, a Microsoft Word document, graphics, sounds) Hyperlink: can contain an internet address (which will work as a link) Lookup Wizard: for a field that allows you to choose a value from another table or from a list of values.
- Select whichever data type is suitable for the field. (Note: You do not have to click on the down arrow. Type the first letter of a data type and that type will automatically be selected from the list)
- Tab into the Description field. This does not have to be completed, however, it may be useful to you, or someone else, in the future if you put a brief description of any field which may not be immediately obvious. For example, for the field RRP you might put the description Recommended Retail Price set by the supplier.
- Press Tab again to enter another field name and continue steps 1 to 5 as above until all field names are entered.
Lesson 13. Changing the Field Size in a table
It is important to minimise the overall size of a database for three reasons. The bigger a database is the more space it will take up on a disk, the more memory it will require and the slower it will work.
You should therefore aim to minimise space required by each field. At the design stage you should decide the maximum size for data within each text or number field name. The size of an Autonumber is fixed.
Beware! There are dangers in changing the field size after entering data. If you make the field size smaller than existing data you will lose any data which is longer than the new field size. You will receive an error message warning of this when you save changes to the table design. It is safer to make a field too small since you can make it larger with no trouble!
From the Table Design view, click on the field you wish to change
(Note the black right pointing triangle indicating with which field you are dealing).
At the bottom of the screen are the Field Properties , the first of which is the Field Size. For a text field this is defined by the number of characters in the field (the maximum is 255 characters)
Type the maximum number of characters you require, move to the next field name and repeat the process. In the case of a number field you will see the following:
Note that the options here are slightly different as they pertain to numbers. Click in the Field Size area and you will see a down pointing arrow appear.
Click on this down arrow to see options for the size of the number
Byte integer numbers from 0 to 255 (no fractions), uses 1 byte Integer : integer numbers from -32,768 to +32,767 (no fractions), uses 2 bytes Long Integer : integers from -2,147,483,648 to +2,147,483,647 (no fractions), uses 4 bytes Single : decimals from -3.4x10^38 to +3.4x10 38 up to 7 decimal places, uses 4 bytes Double decimals from -1.797x10^308 to +1.797x10^308 , up to 15 decimal places, uses 8 bytes Replication ID : used when a database is to be Replicated โ ignore at this stage Decimal : decimals from -10 28 -1 to + 10 28 -1, up to 28 decimal places, uses 12 bytes (Note that an Autonumber automatically has size Long Integer )
Lesson 14. Setting the Primary Key in a table
First click on the grey button at the left of the field(s) which you wish to make the primary key
Lesson 15. Saving the Table Design
There are two actions which will cause this to be done automatically: changing views (see next task) and when you closing the table. You can also choose to save at any time.
On closing the table
Click the symbol at the top right of the table window. You will see the dialogue box shown right.
Click No lose what you have done.
Click Cancel to return to editing the table.
Click the Yes button to Save.
If you have not previously saved the table you will be asked for a name for the table. Type the table name and click OK
Table names can be up to 64 characters. It is standard practice to use _ instead of a space. (Also useful to add Tbl to then end of the name so you can tell it is a table!)
If the table has already been saved it already has a name and Access saves without further question.
Once you have successfully saved the table you will go back to the original database window, but now your table will be listed. (see right)
You have now set up the structure of the table ready to add data later.
While editing
Select File, Save. As before, if you have not saved previously you will be asked for a table name, otherwise the table will simply be saved.
Note: If you close and save a table for which you have not set a Primary Key you will see the warning message shown right. If you answer Yes to Do you want to create a primary key now Access will search the table for a field with the type Autonumber. If such a field exists it will automatically be made the primary key.
If no such field exists Access will create a new field called ID with the field type autonumber and make this the Primary Key.
If there is no field with type Autonumber you should either: i. Click No : Access will save the table without a Primary Key. You can then re-open the table and set a Primary Key ii. Click Cancel , set a Primary key and save the table.
Lesson 16. Table Views
You have now seen one view of the table, the design of the table. Once you have designed the table you will need to add data and so switch to Datasheet view.
Opening a table
When you are considering opening a table you should decide what it is you want to edit. Click on
the table you wish to edit to highlight it. If it is the design of the table then click on the
button, if it is the data then click on the button, or double click on the name of the table. When the table is open you can also change directly between views using the following methods
Changing views while a table is open
Using the toolbar
Click on to change to the design view (to view the structure)
Click on to change to the Datasheet view (to view data)
Using the menus
Select View
Click either Design View or Datasheet View.
Lesson 17. Changing the Table Design
Inserting fields
Make sure you are in Table Design view:
- To add a field at the end of the design move to the next blank line and enter the details
- To insert a field select the row the new field is to be inserted above and press the Insert key. A new line will appear in which you can type the field details
Moving fields
Make sure you are in Table Design view Select the field row or rows to be moved by clicking on the record selection button at the left of the
field name , move the mouse pointer back over the button , click and drag the field(s) to the new position and release the mouse button
Deleting fields
Make sure you are in Table Design view Select the field row(s) and press the Delete key
