What you should know about tables

What is a table? A table consists of columns (fields) and rows (records). It is used to store data for easy retrieval. Here is a table for students in a school:

StudentID	FirstName	LastName	DateOfBirth	TutorGroup
001	Sheila	Bodden	12-Jan-83	10DG
002	Carla	Jones	05-May-82	11WS
003	Peter	Knowles		12WH

The table above has five fields and three records.

To make a table you have to tell Access which field names you want to use, which data type is appropriate for each field, and you have to set some field properties. To make a table use Tables – New – Design View – OK.

Field names:

· Use descriptive names with no spaces. (DateOfBirth is more informative than Field8.)

· Use a mixture of upper- and lowercase letters for easier reading (DateOfBirth is easier to read then both DATEOFBIRTH and dateofbirth.)

· If you have the same field in two tables, use the same field name in both tables. (Don’t use StudentID in one table and ID in another table.)

Data types:

· Use Date/Time for dates and times. (E.g.: Date of birth, Date sold, Graduation date.)

· Use Currency for currency. (E.g.: Pay per hour, Price.)

· Use Number for data that will be calculated on.
use field size long integer for numbers without decimal points (e.g. population), otherwise use double (e.g. mileage)

· Use Text for text and also for numbers that will not be calculated on. (Name, House number, Phone number.)

· Use Yes/No for data that can be true or false (boolean data). (E.g.: Member of the choir, Have a driver’s license.)

If you choose wrong data type:

Example: If you enter 956-7869 in a number field, you will get this error message, since 956-7869 is not a number because of the hyphen:

Solution:

· click OK

· delete everything you typed in the field

· go back to the structure

· change the field’s datatype to (in this case) Text

· go back to DataView

· type 956-7869 (now with no problems.)

Field properties: (For each property, press F1 for more information.)

Field Size

· for text fields type the maximum number of characters needed

· for number fields choose Long Integer if the number has no decimals and Double otherwise

Format

· for a number field you can choose between these formats:

· for a Date/Time field you can choose between these formats:

· for a Yes/No field you can choose between these formats:

Decimal Places

The number of digits to the right of the decimal point. Select “Auto” if you want format to determine the number of decimal places.

Caption

If you specify a caption it will be used instead of the field name in Datasheet View, Query View and in Reports. Example: the caption “When the goods arrived” may be more meaningful than the field name “ArrivalDate”.

Indexed

You can use the Indexed property to set a single-field index. An index speeds up queries on the indexed fields as well as sorting and grouping operations. When you set an index you have to decide if duplicates are OK or not. If you want to give an index a name use View - Indexes.

Primary key:

The power of a relational database system such as Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables using queries, forms, and reports. In order to do this, each table should include a field or set of fields that uniquely identify each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, to ensure uniqueness Microsoft Access will prevent any duplicate or null values from being entered in the primary key fields.

If your table has a field with unique values (like StudentID), set that field to be the primary key.

If your table does not have any unique fields, add a new field you can name AutoNumber and which you give data type AutoNumber. Access will enter the values 1, 2, 3, etc. automatically, thereby giving you a unique field you can set as primary key.

If you enter duplicate values in a field set as primary key or in a field with an index with no duplicates, you get this error message:

Solution:

· click OK

· delete everything you typed in the field

· if duplicate values are needed in this field, go back to the Table Design view and remove primary key or set indexed property to allow duplicates.

Print structure:

On the application menu click “Print database structures” and follow the simple instructions.

Import a table from a csv file:

Sometimes the table you need in your database already exists in a csv file. This is what you do to import it:

File - Get External Data - Import - select the csv file you want to import and click Import.
This opens the Import Text Wizard.

Select Delimited and click Next.
Select Comma and First Row Contains Field Names. Then click Advanced and set the desired data type for each field. Click OK and Next.
Select In a New Table and click Next.
In the next window just click Next as you have already set the correct data types.
Select Let Access add a primary key and click Next.
Type the name of the table or accept the default and click Finish

Import a table from a database:

Sometimes the table you need in your database already exists in another database. This is what you do to import it:

File - Get External Data - Import - find the database you want to import from - Import - select the table(s) you want to import - OK.

List table to a file (export):

To print the content of a table with your name and tutor group you may export the table to a rich-text file.

Click on the table you want to export in the Database View - File - Export - set ‘Save as type’ to ‘Rich Text Format (*.rtf)’ - find the right folder - type File name - Save.

Now you can open the rich text file in Word to add a header and then print it. When you open the file, make sure ‘Files of type’ is set to ‘Rich Text Format (*.rtf)’ so you can find it.

Note: A simpler way to copy and paste a table into word is to select the table name, click Copy, switch to Word, click where you want the table to appear and click Paste.

Note: Another way is to have the Access window and a Word document opened next to each other and drag the table name into the document.

Copy a table:

Right-click the table, choose Copy, right-click somewhere, click Paste, make sure “Structure and Data” is selected, type name for the copy.

Rename a table:

Right-click the table, choose Rename, type the new name.

Delete a table:

Right-click the table, choose Delete.

Working in Datasheet view:

· Edit records:

Edit normally with Delete and Backspace, Ctrl+C to copy , Ctrl+V to paste, Ctrl+X to cut. Use Ctrl+’ to get the content in the field above.

· Delete records:

Click the row header and then press the Delete key. A window asks you to confirm that you really want to delete.

· Add records:

A new record can not be inserted between other records, it has to be added below the last record. The record is saved as soon as the pencil icon on the left disappears.

· Sort records:

Click the AZ and ZA buttons when the cursor is sitting in the field you want to sort on.

· Replace values:

Use Edit - Replace to find and replace values.

Moving between the Database window and other windows:

· Ctrl+F6 switches between open windows in Access (like Alt+Tab switches between open applications in Windows 95).

· You can also switch between windows using the Window menu. In the example on the right you can choose between three open windows labeled 1, 2 and 3.

· If you want to see more than one window at a time use the options Tile Horizontally, Tile Vertically and Cascade.

· Click this button to go to the Database Window.

· Click this button to go to the Datasheet View.

· Click this button to go to the Design View.