filtering, sorting, and creating relationships · 5. click the show table button in the...

Filtering, Sorting, and Creating Relationships

INFORMATION TECHNOLOGY TRAINING 79

3.14 FREEZE COLUMNS

If the table has a large number of columns, we may want to freeze columns so the frozencolumns stay in view as we scroll across the page. For example, if we have a Students table andwe want the Student Number, First Name, and Last Name to remain onscreen as we scrollacross the table, we can freeze the Student Number, First Name, and Last Name fields. Whenwe freeze a column, Access moves it to the far left side of our table. If we want it to remainthere, we must save the table.To freeze columns:

Fig. 3.14.1: Freeze Columns

1. Select the columns we want to freeze.

2. Activate the Home tab.

3. Click the More button in the Records group. A menu appears.

4. Click Freeze. Access freezes the columns. As we scroll, the frozen columns remain stationary.

To unfreeze columns:1. Activate the Home tab.

2. Click the More button in the Records group. A menu appears.

3. Click Unfreeze. Access unfreezes the columns.

© The Institute of Chartered Accountants of India

Data Bases

INFORMATION TECHNOLOGY TRAINING80

3.15 FORMAT A TABLE

We can use the features in the Font group on the Home tab to apply a variety of formats to ourTable 3.15.1.

Format a Table

Button Function

Apply a font to all of the data in a table.

Apply a font size to all of the data in a table.

Bold all of the data in a table.

Italicize all of the data in a table.

Underline all of the data in a table.

Left-align a column.

Right-align a column.

Center a column.

Change the font color.

Change the background color. By default, the backgroundcolor is white.

Change the gridlines. Gridlines separate columns androws. This option allows we to display gridlines forcolumns only (vertical), gridlines for rows only(horizontal), gridlines for both columns and rows, or nogridlines at all.

Change the alternating color. For example, on a datasheetwe can have every other row appear in an alternatingcolor.

Table 3.15.1: Format a Table

To bold, italicize, or underline:1. Place the cursor anywhere within the table.2. Activate the Home tab.3. Click the button for the format we want to apply. Access applies the format.To left-align, right-align, or center:1. Place the cursor anywhere within the column we want to left-align, right-align, or center.2. Activate the Home tab.3. Click the button for the format we want to apply. Access applies the format.




To change the font, font size, or gridlines:1. Place the cursor anywhere within the table.


3. Click the down-arrow to the right of the option we want to apply. A menu appears.

4. Select the option we want. Access changes the font, font size, or gridlines.

To change the font color, background color, or alternating color:1. Place the cursor anywhere within the table.


3. Click the down-arrow to the right of the option we want to apply. A menu of colors appears.

4. Select the color we want. Access changes the font color or the alternating color.

3.16 COMPUTE TOTALS

On the Home tab, we can use the Total button in the Records group to compute the sum,average, count, minimum, maximum, standard deviation, or variance of a number field; thecount, average, maximum, or minimum of a date field; or the count of a text field.To compute totals:

Fig. 3.16.1: Compute Totals

1. Open the table or query for which we want to compute totals.


3. Click the Totals button in the Records group. A Total line appears at the bottom of thetable or query.

4. Click on the Total line under the column we want to total. A down-arrow appears on theleft side of the field.

5. Click the down-arrow and then choose the function we want to perform. Access performsthe calculation and displays the results in the proper column on the Totals row.


Data Bases


Fig. 3.16.2: Result

3.17 FIND AND REPLACE

If we need to find a sequence of characters, a word, or a phrase in a table or field, we can use theFind command. In Access, the Find command has three options: We can find all instances in atable or field that match a sequence of characters, all instances that begin with a sequence ofcharacters, or all instances that contain a sequence of characters. For example, we can find allstudents with the last name Smith, all students whose last name begins with S, or all instancesof 08 anywhere in the field.After we find the word, phrase, or sequence of characters we are searching for, we can replaceit with a new sequence of characters by executing the Replace command.To Find:

Fig. 3.17.1: 'Find' Option

1. Place our cursor in the column we want to search.


3. Click the Find button in the Find group. The Find and Replace dialog box appears.

Fig. 3.17.2: Result




4. Activate the Find tab.

5. Type what we want to find in the Find What field.

6. Choose the name of the table we want to search in the Look In field, if we want to searchthe entire table or select the field, we selected in step 1 if we want to search that field. If wewant to search another field, click in that field and then select it in the Look In field.

7. Choose Any Part Of Field if we want to search for our entry anywhere within a field,choose Whole Field if we want the field to match the sequence of characters we entered, orchoose Start Of Field if we want the field to begin with a sequence of characters we entered.

8. Choose All in the Search field if we want to search the entire table, Up to search upwardfrom our current location, or Down to search downward from our current location.

9. Click Find Next to begin our search. Access finds the first entry that matches our findcriteria. Continue clicking Find Next to find additional matches.

If we want to find and replace, open the Find and Replace dialog box (follow steps 1 through 3)and then activate the Replace tab. In the Replace With field, enter the sequence of characters wewant to use to replace what we find. Complete the other fields on the tab the same as we wouldif we were doing a Find. Click Find Next to find the first instance for which we are searching.Click Replace to replace that instance. Click Replace All to replace every instance.

3.18 CREATE RELATIONSHIPS

In Access, we store data in multiple tables and then use relationships to join the tables. After wehave created relationships, we can use data from all of the related tables in a query, form, orreport.

A primary key is a field or combination of fields that uniquely identify each record in a table. Aforeign key is a value in one table that must match the primary key in another table. We useprimary keys and foreign keys to join tables together—in other words, we use primary keys andforeign keys to create relationships.

There are two valid types of relationships: one-to-one and one-to-many. In a one-to-onerelationship, for every occurrence of a value in table A, there can only be one matching occurrenceof that value in table B, and for every occurrence of a value in table B, there can only be onematching occurrence of that value in table A. One-to-one relationships are rare because if thereis a one-to-one relationship, the data is usually stored in a single table. However, a one-to-onerelationship can occur when we want to store the information in a separate table for securityreasons, when tables have a large number of fields, or for other reasons. In a one-to-manyrelationship, for every occurrence of a value in table A, there can be zero or more matchingoccurrences in table B, and for every one occurrence in table B, there can only be one matchingoccurrence in table A.

When tables have a one-to-many relationship, the table with the one value is called the primarytable and the table with the many values is called the related table. Referential integrity ensuresthat the validity of the relationship between two tables remains intact. It prohibits changes tothe primary table that would invalidate an entry in the related table. For example, a school hasstudents. Each student can make several payments, but each payment can only be from onestudent. The Students table is the primary table and the Payments table is the relatedTable 3.18.1.


Data Bases


Students

Student ID Last Name First Name

Primary Key

1 John Smith

2 Mark Adams

3 Valerie Kilm

Payments

Payment ID Student ID Amount Due Amount Paid

Primary key Foreign key

1 1 500 500

2 2 700 300

3 3 500 250

4 2 400 300

5 3 250 250Table 3.18.1: Relationships

If we delete Student ID 1 from the Students table, Student ID 1 is no longer valid in the Paymentstable. Referential integrity prevents we from deleting Student ID 1 from the Students table.Also, if the only valid Student IDs are 1, 2, and 3, referential integrity prevents us from enteringa value of 4 in the Student ID field in the Payments table. A foreign key without a primary keyreference is called an orphan. Referential integrity prevents us from creating orphans.To create relationships:1. Close all tables and forms. (Right-click on the tab of any Object. A menu appears. Click

Close All.)

Fig. 3.18.1: Create Relationship (i)

2. Activate the Database Tools tab.

3. Click the Relationships button in the Show/Hide group. The Relationships window appears.




Fig. 3.18.2: Create Relationship (ii)

4. If anything appears in the relationships window, click the Clear Layout button in the Toolsgroup. If we are prompted, click Yes.

5. Click the Show Table button in the Relationships group. The Show Table dialog box appears.

Fig. 3.18.3: Create Relationship (iii)

6. Activate the Tables tab if our relationships will be based on tables, activate the Queries tabif our relationships will be based on queries, or activate the Both tab if our relationships willbe based on both.

7. Double-click each table or query we want to use to build a relationship. The tables appearin the Relationships window.

8. Click the Close button to close the Show Table dialog box.


Data Bases


Fig. 3.18.4: Create Relationship (iv)

9. Drag the Primary table’s primary key over the related table’s foreign key. After we drag theprimary key to the related table’s box, the cursor changes to an arrow. Make sure thearrow points to the foreign key. The Edit Relationships Dialog box appears.

Fig. 3.18.5: Create Relationship (v)

10. Click the Enforce Referential Integrity checkbox.

11. Click Create. Access creates a one-to-many relationship between the tables.




Fig. 3.18.6: Create Relationship (vi)

12. Click the Save button on the Quick Access toolbar to save the relationship.

When we create a relationship, we can view the related table as a subdatasheet of the primarytable. Open the primary table and click the plus (+) in the far left column. The plus sign turnsinto a minus (-) sign. If the Insert Subdatasheet dialog box opens, click the table we want to viewas a subdatasheet and then click OK. Access displays the subdatasheet each time we click theplus sign in the far left column. Click the minus sign to hide the subdatasheet.After a relationship has been created between two tables, we must delete the relationship beforewe can make modifications to the fields on which the relationship is based. To delete arelationship:1. Click the line that connects the tables.

2. Press the Delete key.

When we create a lookup column, Access creates a relationship between the tables.Sources[1] http://www.databasedev.co.uk/microsoft-access2007.html

[2] http://www.functionx.com/access/

[3] http://www.microsoft.com/downloads/details.aspx?familyid=d9ae78d9-9dc6-4b38-9fa6-2c745a175aed&displaylang=en

[4] http://www.yevol.com/en/access2007/index.htm

[5] http://www.ms-access2007.com/

[6] http://www.vtc.com/products/Microsoft-Access-2007-Tutorials.htm

[7] http://www.baycongroup.com/access2007/index.html

[8] http://office.microsoft.com/en-us/access/CH100645761033.aspx


filtering, sorting, and creating relationships · 5. click the show table button in the...

Documents