chapter ii 1. introduce to table 2. creating a table 3. maintaining the data in table 4....

Chapter II

1. Introduce to Table

2. Creating a Table

3. Maintaining the Data in Table

4. Relationship

TABLETABLE

Introduce to table Concept

Tables are the basic and important of any database. Tables are where a database stores all of its information.

Each table in a database stores related information. Most databases have more than one table.

Components of a table consist of fields, records, keys. A field is a specific type of information. Structure of a table

contains Field Name, Data Type, Description, Field Properties.

A record represents detail information of the specific object. Keys have three key: Primary Key, Secondary Key, Foreign

Key.

Page 10

Structure of Table:

Mã SV Họ và tên Ngày sinh Nơi sinh

Lớp

CDTH001 Trần An 03/10/82 HCM CDTH4A

CDTH002 Lê Hoàng 23/11/84 Hà Nội CDTHUC7

Record

Introduce to table

Primary key Foreign key Field

Field Name:

Maximum Length: 64 characters, include characters

A…Z, a…z, 0…9, space. If there is space then you had

to type form [FieldName].

If both two table have the same field name and users

access two table concurrency (tồn tại) then you had to

type as following: [TableName].[FieldName]

Or [TableName]![FieldName]

Introduce to table

1. Field

Page 22

Introduce to tableTable1: Data Types

Data Type Example Description

Text Legal Name:John Doe

Stores text, numbers, or a combination of both, up to 255 characters long. Text fields are the most common of all data types.

Memo Notes:Sally displays a high amount of...

Stores long text entriesup to 64,000 characters long (the equivalent of 18 pages of text!). Use memo fields to store notes or anything else that requires a lot of space.

Number Age: 31 Stores numbers that can be used in calculations.

Introduce to tableData Types


Date/Time Birthday:April 7, 1969.

Stores dates, times, or both.

Currency Price: $84.95 Stores numbers and symbols that represent money.

AutoNumber

Invoice Number:

187001

Automatically fills in a unique number for each record. Many tables often contain an AutoNumber field that is also used as their primary key.

Yes/No Male: Yes Stores only one of two values, such as Yes or No, True or False, etc.

Introduce to table

Data Types


OLE Object Photo: Stores objects created in other programs such as a graphic, Excel spreadsheet, or Word document.

Hyperlink Web Site:

www.amazon.com

Stores clickable links to files on your computer, on the network, or to Web pages on the Internet.

Lookup Wizard

Purpose of Trip: A wizard that helps you create a field whose values are selected from a table, query, or a preset list of values.

Introduce to tableField Properties

Page 48

2. Record

Record represent detail information of a specific object. Each record has order number. Order of the first record is 1, the second record is 2,…, the last record is total records in a table.

Introduce to table

Description of each Field

The field description is displayed in the status bar when the field is selected in a Table Datasheet View or Form Datasheet View. It can contain a maximum of 255 characters. It add a meaningful description into each field to make database maintenance easier.

Introduce to table

3. Key A key is any field used as the basis for ordering the data on a table in

a meaningful way. There are three types of keys:• Primary key: can be one or more field that it is assigned for each table, it

provides the basis for bringing together data stored in separate tables. Primary keys keep the data in the specificed key or fields, allow you to search for data and use one table as lookup table for another and pervent duplicate or null values from entered into the table.

• Secondary key: can be created on other fields with the table to be used as basis for finding and sorting records, selecting records in queries, grouping records in report.

• Foreign key: exist in most databases and are associated with managing the relationship between data within individual tables.

Creating A TableSelect Objects TableClick New buttonSelect one of ways as following:

1

23

45

Creating a table by enter direct data.

Creating a Table Using the Table Design

Creating a Table Using the Table Wizard

Import Table

Link Table

1

2

5

3

4

(Page 21)

Creating A Table

Creating a Table Using the Table Design

Insert a New Field Click the row selector for the field that will be below the new field you want to insert. Click the Insert Rows button on the Table Design toolbar Or Right clickInsert

rows Or Click Insert menu Rows Delete a Field

Click the row selector for the row you want to delete. Click the Delete Rows button on the Table Design toolbar Or Right clickDelete

rows. Change the order of Fields in a Table

Click to select the row selector for the field you want to move. Click the row selector again, and then press and hold the mouse button. Drag the row to the new position where you want the field to appear, and then

release the mouse button.

Creating A Table

Page 51

Viewing detail field properties

1) Setting Field Size

Creating A Table

Table 2: Available Number Field Sizes

Heading Number RangeDecimal

PlacesStorage

Size

Byte 0 to 255 None 1 byte

Integer -32,768 to 32,767 None 2 bytes

Long Integer -2.1x1038 to 2.1x1038 None 4 bytes

Single -3.4x1038 to 3.4x1038 7 4 bytes

Double -1.8x10308 to 1.8x10308 15 8 bytes

Replication ID N/A N/A 16 bytes

Page 55&56

2) Formatting Number, Currency, and Date/Time Fields

Creating A Table

Table 3: Number, Currency, and Date/Time Formats

Number Format ExampleDate/Time Format

Example

General Number

1234.567 General Date 6/10/2000 6:35:21 PM

Currency $1,234.57 Long Date Saturday, June 10, 2000

Euro 1,234.57 Medium Date 10-Jun-00

Fixed 1234.57 Short Date 6/10/2000

Standard 1,234.57 Long Time 6:35:21 PM

Percent 123456.70% Medium Time 6:35 PM

Scientific 1.23E+03 Short Time 18:35

Page 57

3) Formatting Number and Currency Values by Hand

Creating A Table

Page 58

4) Formatting Text Values

Creating A Table

Table 4: General and Text Formatting Symbols

Character Description Text Format Display

! Aligns text from the left Hello ! Hello

“”Aligns text from the

rightHello ! Hello

< Lowercase Hello < hello

> Uppercase Hello > HELLO

&"ABC" Always displays ABC 4 &" oz." 4 oz.

@ 5558000 @@@-@@@@ 555-8000

* Alert &*! Alert!!!!!!!!!!

[color] Displays value in color Hello [red] Hello

Page 60

5) Creating Input Masks

Creating A Table

Table 5: Input Mask Characters

Character Description Character Description

0Numbers 0 to 9 required; plus and minus signs not allowed.

&Character or space required.

9 Number or space optional; plus and minus signs not allowed.

C Character or space optional.

# Number or space optional; plus and minus signs not allowed.

< Converts the following characters to lowercase.

Page 64

Creating A TableTable 5: Input Mask Characters

Character Description Character Description

. , : ; - / Decimal point, thousands, date, and time separators.

> Converts the following characters to uppercase.

ALetter or number required.

! Displays characters from right to left, rather than left to right.

a Letter or number optional. \ Displays the following input mask character. For example, \* would display *.

L Letters A to Z required. Password Displays an asterisk( * ) for each character you type.

? Letter or number optional.

6) Caption (page 54)

Caption appears as the heading for the field instead of the field name. Captions are useful when you want to provide more detailed headings for your field names. Captions can contain up to 2,048 characters, including spaces. If you don't specify a caption, Access uses the field name.

7) Default Values (page 60)

Specify a value that is automatically entered in a field when a new record is created.

8) Required Fields (page 60)

Access refuses to accept a record until you enter an acceptable value for that field.

Creating A Table

9) Creating Indexed Fields(page 49): is an invisible data structure that stores the sort order of a table based on the indexed field or fields.

- Yes (No Duplicated): ensure that no two records have the same data in this field.

- Yes (Duplicated OK): allow multiple records to have the same data in this field.

- No : the field is not indexed. This is the default setting.

10) Validation Rule(page 60) (Quy tắc hợp lệ) : Used to specify the requirements for data entered into the field.

11) Validation Text(page 62) (Thông báo lỗi): : Used to specify the message that will be displayed to the user when data that violates the validation rule is entered. (Example: Page 63)

Creating A Table

12) Creating a Lookup Field

Create a Field Based on a Lookup List

1. Display the table in Design viewenter a new fieldclick the Data Type Click Lookup Wizard.

2. Click the I Want The Lookup Column To Look Up The Values In A Table Or Query optionClick Next.

3. Select the table or query you want to use for the Lookup listClick Next.

4. Select the fields that you want to appear in the Lookup listClick Next.

5. Choose a sort order for the Lookup listClick Next.

6. Resize the column widths in the Lookup list, indicate whether or not to include the primary key columnClick Next.

7. Enter a label for the Lookup columnClick Finish.

Creating A Table

Page 65

Create a Field Based on a Value List

1. Display the table in Design view, enter a new field, click the Data Type list arrowClick Lookup Wizard.

2. Click the I Will Type In The Values That I Want optionClick Next.

3. Specify the number of columns you want in the Value list.

4. Enter the values in the list. If necessary, resize the column widths. Click Next to continue.

5. Choose which column will act as the bound columnClick Next.

6. Enter a label for the Lookup column.

7. Click Finish.

Creating A Table

Page 67

Modifying a Lookup List: Select TableSelect Design ViewSelect Lookup Tab

Creating A Table

Table 6: Lookup Field Properties

Property Description

Display Control

Determines whether the lookup field is a text box, combo box, or list box.

Row Source Type

Determines how Access provides data to the lookup field: from a table or query, from a list of values specified in the Row Source box, or from a list of field names in a table or query.

Row SourceDetermines what is displayed in the lookup field. The Row Source property setting depends on the Row Source Type property setting.

Bound Column

The column in the lookup list that contains the value that is actually stored in the field. The bound column is the first column (1) by default.

Page 69

Modifying a Lookup List: Select TableSelect Design ViewSelect Lookup Tab

Creating A Table

Table 6: Lookup Field Properties

Property Description

Column Count

The number of columns that are displayed in the lookup field list.

Column Widths

The width of each column that is displayed in the lookup field list. Setting a column width to 0 hides the column.

Limit to List

Determines whether a field can accept a value that is not in the lookup list.

13) To add a primary key to a table

1. Display the table in design view, and click the field that you want to set as the primary key. If many fields are primary, you press CTRL+Click fields. Autonumber fields make great primary keys.

2. Click the primary key button on the toolbar. Or Right-click the field you want to use as the primary key and select primary key from the shortcut menu

Creating A Table

14) To remove a primary key to a table

1. Display the table in design view, and click the fields are the primary key. Autonumber fields make great primary keys.

2. Click the primary key button on the toolbar.or Right-click select primary key from the shortcut menu. Page 50

1) Enter a New Record and Move Around in a Table

Maintaining the Data in Table

1.Click the New Record button Or Click InsertNew Record Or Right-clickNew Record Or Click the new record navigation button Or Ctl + + .

2. Press Tab to accept the AutoNumber entry.

3. Enter the data. If you make a typing mistake, press Backspace.

4. Press Tab to move to the next field or Shift+Tab to move to the previous field.

In the Database window Click Tables on Object bar double-click the table.

Page 11


To insert a hyperlink:1. Select the hyperlink field and click the insert hyperlink

button on the toolbar. Or...2. Select the hyperlink field and select inserthyperlink

from the menu.3. Either select a file you want (use the browse button to

help you locate the file) or type a web address for the hyperlink's destination and click ok.

To edit a hyperlink:1. Right-click the hyperlink and select hyperlink edit

hyperlink from the shortcut menu. Page 194


2) Delete a Record from a Table

1. Right-click the row selectClick Delete Record Or Click the Delete Record button Or Click Edit menuDelete record.2. Click Yes.

To insert an ole object:1. Select the ole object field and select insert object from the menu.2. Select either:3. Create new:to create a new ole object file. (you must select the type of file you wish to create).4. Create from file: to insert an existing file.5. Click ok. If you selected the create from file option, browse to and double-

click the file you want to insert and click ok.

Page 195

3) Find information

1. Click the find button on the toolbar. Or Press ctrl + f. Or Select Edit Find from the menu

2. Enter the text you want to search for in the find what text box.

3. Click the find next button.

4. Repeat step 3 until you find the text you're looking for.

4) Find and Replace information

1. Select edit replace from the menu. Or Select edit find from the menu.

2. Enter the text you want to search for in the find what text boxenter the text you want to replace the word with in the replace with text


Page 36


Table 7: Using the Match List Options

Match Description

Whole FieldFinds only data that is exactly the same.Example: John finds John, but not Johnson, or

Sue and John.

Any Part of FieldFinds data anywhere in the field.Example: John finds John, Johnson, and Sue and

John.

Start of FieldFinds data only at the beginning of the field.Example: John finds John and Johnson, but not

Sue and John.

Page 37

5) To Sort Records by one field1. Click anywhere in the column you want to use to sort the list.2. Click either the sort ascending button or sort descending button on

the toolbar. 6) Filtering by Selection

To filter by selection: Find the field value on which you want to base the filter.

1. Right-click the field value and select filter by selection from the shortcut menu, or...

2. Click records from the menuFilterFilter by Selection, or…3. Click the field value, then click the filter by selection button on the

toolbar. To filter excluding the selection:

Right-click the field value you want to exclude and select filter excluding selection from the shortcut menu.


Page 39

To remove a filter:1. Right-click the filtered table and select remove filter/sort from the

shortcut menu.or...2. Click the remove filter button on the toolbar.

7) Filtering by Form 1. Click the filter by form button on the toolbar.2. Click the empty text box below the field you want to filter, click

the drop-down arrow and select the value you want to use to filter the records.

3. Repeat step 2 for each additional field you want to use to specify additional filter criteria.

4. If you want to use or criteria, click the or tab at the bottom of the screen to specify the additional filter criteria.

5. Click the apply filter button on the toolbar.


Page 39

Maintaining the Data in TableTable 8: Common Criteria Operators

Operator Example Description

= ="MN" Finds records equal to MN.

<> "MN" Finds records not equal to MN.

< <10 Finds records less than 10.

< = <=10 Finds records less than or equal to 10.

> >10 Finds records greater than 10.

> = >=10 AND 5Finds records greater than or equal to 10 and not equal to 5.

BETWEENBETWEEN 1/1/99

AND 12/31/99Finds records between 1/1/99 AND 12/31/99.

LIKE LIKE "S*"Finds text beginning with the letter "S." You can use LIKE with wildcards such as *.

Page 41

8) Creating an Advanced Filter

1. Select records filter advanced filter/sort from the menu.

2. Double-click each field you want to include from the field list.or...

3. Drag the field from the field list onto the design grid.

4. In the design grid, enter any desired search criteria for the field in the criteria row.

5. Click the sort box list arrow for the field and select a sort order (optional).

6. Click the apply filter button on the toolbar.


Page 41

9) Rearranging Columns 1. Click the field name of the column you want to move.

2. Drag the selected column to its new location.


10) Changing Gridline and Cell Effects

1. Select format datasheet from the menu.

2. Select the cell effect option you want and click ok. 11) Freezing / Unfreezing a Field

To freeze a column:

1. Right-click the column field name you want to freeze and select freeze columns from the shortcut menu.

TO Unfree a column:

1. Select Format UnFreeze All Column from the menu. Page 43&44

12) Hiding/Unhiding a Column

- To hide a column:

Right-click the column field name you want to hide and select hide columns from the shortcut menu.

- To unhide a column:

Select format unhide columns from the menu.


13) Changing the Datasheet Font

- Select format font from the menu

15) Delete a Column Right-click the column selector(s) for the column(s) you want to

deleteClick Delete ColumnYes.

14) Insert a Column Right-click the column selector to the right of where you want to add

the new columnClick Insert Column.

Page 45

16) Adjusting Column With Drag the column header's right border to the left or right. Or... Right-click the column header(s), select column width from the

shortcut menu, and enter the column width. Or... Select the column header(s), select format column width from

the menu, and enter the column width.


17) Adjusting Row Height Drag the row header's bottom border up or down.or... Right-click the row header(s), select row height from the shortcut

menu, and enter the row height. Or... Select the row header(s), select format row height from the shortcut

menu and enter the row height.

To automatically adjust the width of a column or row:• Double-click the right border of the column or bottom border of a row.


18) Importing information from another file into access (chapter 10-page 186)

1. In the database window, select file get external data import from the menu.

2. Select the type of file you want to import from the files of type list.

3. Browse to the appropriate drive and/or folder and double-click the file you want to import.

4. Follow the onscreen instructions of the import text wizard to import the data.

Page 188


19) Exproting a table to an external file

1. In the database window, right-click the table and select export from the shortcut menu. Or...

2. Select the table and select file export from the menu.

3. Select a format from the save as type list and then, if necessary, type a new file name in the file name text box.

4. Click export.

5. Follow the onscreen instructions of the export text wizard to export the data.

Page 188


20) Importing an object from another access database

1. In the database window, select file get external data import from the menu.

2. Browse to the appropriate drive and/or folder and double-click the database that contains the object(s) you want to import.

3. Select the database object(s) you want to import and click ok.

21) Linking to a table in another database

1. In the database window, select file get external data link tables from the menu.

2. Browse to the appropriate drive and/or folder and double-click the

Page 189


22) Exporting records to microsoft excel

1. Open or select the table, query, or report you want to export.

2. Click the officelinks button list arrow on the toolbar and select analyze it with microsoft office excel from the list.

23) Exporting records microsoft word

1. Open or select the table, query, or report you want to export.

2. Click the officelinks button list arrow on the toolbar and select publish it with microsoft office word from the list.

Page 190


23) Mail merging records to microsoft word

1. Open or select the table, query, or report you want to use for the mail merge.

2. Click the officelinks button list arrow on the toolbar and select merge it with microsoft word from the list.

3. Specify whether you wish to merge to an existing word document or if you want to create a new word document.

4. Perform the mail merge using microsoft word.

Page 192


24) Displaying Database Object Dependencies

1. Right-click any database object and select Object Dependencies from the shortcut menu.

Database objects that are dependent on the selected database object.

Database objects that are required by the selected database object.

2. Close the task pane and the database.

Page 198

Relationships Example: This database tracks customers and their orders in two

separate but related tables.

Relationship

Malop Tenlop Siso

CDTHA Tin hoc a 60

CDTHB Tin hoc 70

CDKTA Kinh te 100

Malop Học phi BHYT

CDTHA 800000 150000

CDTHB 750000 100000

CDKTA 700000 50000

Malop1 1

LOP HOCPHI

1) Relationship Types

a) One to One: Each record in a table relates to one record in another table and vice verse.

Page 79

Relationship

Malop Tenlop Siso

CDTHA Tin hoc a 60

CDTHB Tin hoc 70

CDKTA Kinh te 100

Malop Mathay Phong

CDTHA T01 H5.1

CDTHA T02 H5.1

CDTHB T01 H5.2

CDTHB T02 H5.2

CDKTA T02 H4.3

CDKTA T03 H4.31

∞

LOP PHAN CONG

Malop

b) One to Many: Each record in a table (A) relates to one or more records in another table (B), vice verse each record in a table (B) relates to one record in another table (A).

Relationship

Malop Tenlop Siso

CDTHA Tin hoc a 60

CDTHB Tin hoc 70

CDKTA Kinh te 100

LOP THAY GIAO

Mathay Tenthay Mon

T01 Lê Hà THVP

T02 Nguyễn Nam Access

T03 Trần Minh Kế Toán

∞∞

c) Many to Many: One or more records in a table relate to one or more records in another table and vice verse

Relationship2. Creating Relationships Between Tables

1. In the database window, click the relationships button on the toolbar. Or Right-click any blank area in the database window and select relationships.

2. If necessary, click the show table button on the toolbar.

3. Click the table you want and click add.

4. Repeat step 3 for each table you want to use in the relationship.

5. Click close.

6. Click the related field in the first table and drag it to the related field in the second table.

7. Specify any referential integrity and/or join type options (optional).

8. Click create to create the relationship.

2

6

7&8

Page 73

Relationship3. Enforcing Referential Integrity 1. In the database window, click the

relationships button on the toolbar. Or Right-click any blank area in the database window and select relationships

2. Double-click the join line for the relationship you want to work with.

3. Check the enforce referential integrity box.- Cascade Update Related Fields: When you change data in the main field of one table, Access will automatically update the matching data in the related table.- Cascade Delete Related Records: When you delete a record in the main table, Access will automatically delete any matching records in the related table.

Page 75

Relationship4. Print the relationships window 1. In the database window, click the relationships button on the toolbar.

Or Right-click any blank area in the database window and select relationships

2. Select file print relationships from the menu.

3. Modify the report that appears in print preview, if necessary, and then click the print button on the toolbar.

6. Delete a table relationship

1. Open the relationshipswindow.

2. Click the join line that connects the tables and press delete.

3. Click yes to confirm the deletion.

5. Edit a table relationship

1. Right-click join lineSelect Edit Relationship…

Page 77

Thank You

chapter ii 1. introduce to table 2. creating a table 3. maintaining the data in table 4....

Documents

tablestructure of table

lookup table

new field

field description

table design toolbar

table datasheet view

record record

foreign key