 |
|
|
|||||||||
 |
|||||||||||
|
|||||||||||
 |
|||||||||||
|
|
 |
|
Adding, changing, searching for and deleting records
1. Introduction.After creating and loading a database, you must be able to maintain it. Maintaining the database means modifying the data to keep it up-to-date, such as adding new records, changing the data for existing records, and deleting records. Updating can include bulk updates or deletions, that is, to update or to delete many records at the same time. As the need of the organisation change, you may need to restructure your database i.e. to change the database structure. For example, your organisation may decide that customers are to be categorised by customer type, so you need to add a field for customer type to the CUSTOMER table in your database. You may also need to change the characteristics of existing fields. For example, you may find the Name field is to short to contain the name of one of your new customers, so you need to change the field's width in the CUSTOMER table structure. To improve the efficiency of certain types of database processing, you can also create secondary indexes, which are similar to indexes found in the back of books. In more sophisticated databases, several secondary indexes may exist in the same table. 2. Adding, Changing and Deleting.Keeping the data in a database up-to-date requires three tasks: adding new records, changing the data in existing records, and deleting existing records. 2.1. Adding RecordsPreviously, in the session “Microsoft Access - Creating a Database”, you added records to a database using Datasheet view, i.e. as you were adding records, the records were displayed on the screen in the form of a datasheet or table in a grid. When you need to add additional records, you can use the same techniques. In session “Microsoft Access - Creating a Database”, you also used a form to view records. This is called Form view. You can also use Form view to update the data in a table. You can add new records, change existing records, or delete records. To do so, use the same techniques you used in Datasheet view. To add a record to the CUSTOMER table with a form, for example, perform the following steps. These steps use the Customer form you created in session “Microsoft Access - Creating a Database”. ¨ Open the CUST database and point to the Form button. ¨ Choose the Form button. Select Customer, and click the Open button. ¨ Maximize the form. Point to the Last Record button. The screen should contain a maximised version of the form for the CUSTOMER table. ¨ Click the Last Record button to move to the last record in the table and then point to the Next Record button. The last record displays on the screen. ¨ Click the Next Record button. Type the data for the new record, as shown in Figure 1. Press the TAB key after entering the data in each field.
Figure 1. 2.2 Searching for a Record.In the database environment, searching means looking for records that satisfy some criteria. Looking for all the customers whose sales rep number is 04 is an example of searching. The queries in the session “Microsoft Access - Querying a Database” are examples of searching. Access had to locate those records that satisfied the criteria. There is also need for searching when using Form view or Datasheet view. In order to update customer FC63, for example, you first need to find the customer. In a small table, repeatedly pressing the Next Record button until customer FC63 is on the screen may not be particularly difficult. In a large table with many records, however, this would be extremely cumbersome. You need a way to be able to go directly to a record just by giving the value in some field. This is the function of the Find button Perform the following steps first to move the highlight to the Customer Number field and then use the Find button to locate the customer whose number is FC63: ¨ With the CUSTOMER table open and the form (Customer) for the CUSTOMER table on the screen, point to the First Record button and click it to display the first record. If the Customer Number field is not currently selected (highlighted), select it by pointing to it and clicking the left mouse button. ¨ Point to the Find button ¨ Click the Find button. Type FC63 and point to the Find Next button. The Find in field: 'Customer Number' dialog box should now display. The Find What: box contains FC63. ¨ Choose the Find Next button and then choose the Close button. Access locates the record for customer FC63. After locating a record that satisfies a criterion, to find the next record that satisfies the same criterion, repeat the same process. (You will NOT need to re-type the value.) 2.3 Changing the Contents of a Record.After locating the record to be changed, move the highlight to the field to be changed by using the TAB key or by clicking the field with a mouse. Then, make the appropriate changes. (Clicking the field with a mouse automatically produces an insertion point. After pressing the TAB key, press F2 to produce an insertion point.) Normally, Access is in Insert mode, so the characters typed will be inserted at the appropriate position. To change to Overstrike mode, press the INSERT key. The letters OVR will then appear near the right-hand edge of the status bar at the bottom of the screen. To return to Insert mode, press the INSERT key. In Insert mode, if the data in the field completely fills the field, no additional characters can be inserted. In this case, increase the size of the field before inserting the characters. You will see how to do this later in these notes. Perform the following steps to use Datasheet View to change the name of customer FC63 to Forrest Eccles Co. by inserting Eccles between Forrest and Co. (There should be sufficient room in the field to do so.): ¨ Click the Datasheet View button to move to Datasheet view. Point to the position in the name field for customer FC63 for the new letters to be typed, (that is, immediately prior to the letter C of Co.). The mouse pointer appears as an l-beam. ¨ Type Eccles followed by a single space. The name should now be Forrest Eccles Co. 2.4. Deleting Records.When records are no longer needed, delete (remove) them from the table. If, for example, customer LR72 will no longer be doing business with the organisation, that customer's record should be deleted. To delete a record, first locate it and then press the DELETE key. Perform the following steps to delete customer LR72: ¨ With the datasheet for the CUSTOMER table on the screen, point to the record selector for the record on which the customer number is LR72 and click the left mouse button to select the record. The record is selected. ¨ Press the DELETE key to delete the record. The Microsoft Access dialog box displays (Figure 2). The message indicates how many records will be deleted. ¨ Click the Cancel button and do NOT complete the deletion. ¨ Close the table by clicking the Close button.
Figure 2. 3. Changing the Structure.When you initially create a database, you define its structure; that is, you indicate the names, types, and sizes of all the fields. It would be ideal if the structure you first defined would continue to be appropriate as long as you use the database. There are, however, a variety of reasons why the structure of a table might need to change. Changes in the needs of users of the database might require additional fields to be added. For example, if it is important to store the type of a customer (such as regular, discount, or special), you need to add such a field to the CUSTOMER table because it is not there already. Characteristics of a given field might need to change. It just so happens that Carson Enterprise's name is stored incorrectly in the database. It should be Carson Lanard Enterprise. Unfortunately, there is not enough room in the Name field to hold the correct name. To accommodate this change, increase the width of the Name field. It may turn out that a field that is currently in the table is no longer necessary. If no one ever uses a particular field, there is no point in having it in the table. Because it is occupying space and serving no useful purpose, it would be nice to remove it from the table. You will also need to delete the field from any forms, reports, or queries that include it. To make any of these changes, choose the Design button from the database window. 3.1 Changing the Size of a Field.Perform the following steps to change the size of the Name field from 20 to 25: ¨ With the Database window on the screen, the Table tab selected, and the CUSTOMER table highlighted, point to the Design tab. ¨ Choose the Design tab, select the Name field by clicking on its record selector (the first column) and then point to the Field Size box. The Name field is selected and the mouse pointer, which has changed shape to an l-beam, points to the Field Size box. ¨ Select the entry for the size of the Name field by clicking the left mouse button, press the BACKSPACE key to delete the number 0, and then type 5 (the new size should be 25). The new size displays in the Field Size box (see Figure 3).
Figure 3. 3.2 Adding a New Field.Perform the following steps to add a new field to a table. The field will be called Cust Type. It will be used to indicate the type of customer. The possible entries in this field are REG (regular customer), DSC (discount customer), and SPC (special customer). The new field will follow the Post Code in the list of fields, i.e. it will be the seventh field in the restructured table. The current seventh field (Balance) will become the eighth field, Credit Limit will become the ninth field, and so on. ¨ Click the left mouse button to select the Balance field and then press the INSERT key to insert a blank row. A blank row displays in the position for the new field. ¨ Point to the Field Name column for the new field and click the left mouse button. Type Cust Type (field name) and press the TAB key. Select the Text data type by pressing the TAB key. Type “Customer Type (REG, SPC, or DSC)” as the description. Point to the Field Size box and click the left mouse button. Press the BACKSPACE key twice to erase the 50 and then type 3 (the size of the Cust Type field). The entries for the new field are complete. ¨ Click the Save button and then click the Datasheet view button. The Datasheet should display and the changes to the structure have been made. 3.3. Updating the Restructured Database.Changes to the structure are immediately available. The customer name field is longer, although it doesn't appear that way on the screen, and the new customer number type field is included. To make a change to a single field, such as changing the name from Carson Enterprise to Carson Lanard Enterprise, point to the field to be changed, click the left mouse button, and then type the correct value. If the record to be changed is not on the screen, use the toolbar buttons, Next Record and Previous Record, to move to it. If the field to be corrected is not visible on the screen, use the horizontal scroll bar along the bottom of the screen to shift all the fields until the correct one displays. Then, make the change. Perform the following steps to change the name of Carson Enterprise to Carson Lanard Enterprise. These steps also increase the column width so the entire name is visible:- ¨ With the CUSTOMER table open and the window containing the table maximized, point immediately in front of the letter E in Carson Enterprise. Click the left mouse button and then type Lanard followed by a single space. The name is changed from Carson Enterprise to Carson Lanard Enterprise. The column is too narrow, however for the entire name to display. ¨ Point to the line separating the Name and Address column headings. The mouse pointer shape should change to a plus sign with two arrowheads and points to the line separating the Name and Address column headings. ¨ Double-click the left mouse button. Access re-sizes the column to best fit the entries in it. The entire name of Carson Lanard Enterprise displays. ¨ Close the table by clicking its Close button. When asked if you wish to save the layout changes, choose the Yes button. The name is changed and the table is closed. 3.4 Updating the Contents of a New Field in a Table.The Cust Type field is blank on every record. One approach to updating the field would be to step through the entire table, changing the value on each record to what it should be. However, if most of the customers have the same type, there is a simpler approach. Suppose, for example, that most customers are type REG (regular). You can initially set all the values to REG. The quickest and easiest way to do this is to use a special type of query called an update query and. later, change the type for the special and discount customers individually. The process for creating an update query begins just like the process for creating the queries in “Microsoft Access - Querying a Database”. After selecting the table for the query, select the Update option from the Query menu. An extra row, Update To: will then appear in the QBE grid. Use this additional row to indicate the way the data will be updated. Perform the following steps to change the value in the Cust Type field for all the records to REG:- ¨ Choose the Query button and then point to the New button. ¨ Choose the New button, choose the New Query button, select the CUSTOMER table, and point to the Add button. The New Query dialog box appears, select Design View. The Show Table dialog box displays and the CUSTOMER table is selected. ¨ Choose the Add button to add the CUSTOMER table to the query and then choose the Close button. Adjust the size of the two portions of the Query screen, as well as the list box containing the fields in the CUSTOMER table. Select the Query menu and point to the Update command. ¨ Choose the Update command by clicking the left mouse button, double-click the Cust Type field to select the field, and then point to the Update To: box in the first column of the QBE grid. The Cust Type field is selected. ¨ Click the left mouse button, type REG and click the Run button. The Microsoft Access dialog box displays (Figure 4). The message indicates eleven rows will be updated by the query.
Figure 4. ¨ Choose the Yes button by clicking the left mouse button and then click the Close button to close the query. In the Microsoft Access dialog box that displays, choose the No button (do not save the query). ¨ Open the CUSTOMER table. Click the right scroll arrow so the Cust Type field displays. The CUSTOMER table that displays shows that all entries in the Cust Type field are now REG. After changing all the customer types to REG, change the ones that should be DSC or SPC individually. Perform the following steps to change the customer types that should not be REG: ¨ Point immediately in front of REG in the third row and click the left mouse button. Press the DELETE key to remove REG. Type SPC and in a similar fashion, change the REG on the sixth row to DSC and the REG on the eighth row to SPC. ¨ Click the Close button to close the table. 4. Creating Validation Rules.Up to this point in the course, you have created, loaded, queried, and updated a database. Nothing done so far, however, ensures that users only enter valid data. This section explains how to create validation rules, i.e. rules that the data entered by a user must follow. As you will see, Access will prevent users from entering data that does not follow the rules. The steps also specify validation text, the message that will be displayed if a user violates the validation rule. Validation rules can indicate a required field, a field in which the user must actually enter data. For example, by making the Name field a required field, a user must actually enter a name that is, the user cannot leave it blank. Validation rules can make sure a user's entry lies within a certain range of values, for example, that the values in the Balance field are between 0 and 20,000. They can specify a default value, i.e. a value that Access will display on the screen in a particular field before the user begins adding a record. To make data entry of customer numbers more convenient, you can also have lower-case letters converted automatically to uppercase. Finally, validation rules can specify a collection of legitimate entries. For example, the only legitimate entries for Customer Type are REG, SPC, and DSC. 4.1 Specifying a Required FieldPerform the following steps to specify that Name is to be a required field: ¨ With the Database window on the screen and the CUSTOMER table highlighted, choose the Design button, select the Name field, and point to the Required box. The Design view displays and the mouse pointer (an l-beam) points to the Required box. ¨ Click the left mouse button and then click the drop-down arrow that appears in the Required box. Point to Yes. A list of available choices for the Required box displays. ¨ Select Yes by clicking the left mouse button. It is now required that the user enter data into the Name field when adding a record 4.2 Specifying a Range.Perform the following steps to specify that entries in the Balance field must be between £0 and £20,000: ¨ Select the Balance field by clicking its record selector. The Balance field should be selected. ¨ Point to the Validation Rule box and click the left mouse button to produce an Insertion point In the Validation Rule box, type “>=0 and <=20000” and then point to the Validation Text box. ¨ Click the left mouse button to produce an Insertion point In the Validation Text box. Type the comment “Balance must be between £0.00 and £20,000”. You must type all the text, including the pound signs, in the box. The validation text is entered and, depending on length of entered text, only the last portion of the wording may be visible. (See figure 5.) Users will now be prohibited from entering a balance that is either less than £0 or greater than £20,000 when they add records or change the value in the Balance field.
Figure 5. 4.3 Specifying a Default Value.Perform the following step to specify a default value of 7000 for the Credit Limit field. This simply means that if users do not enter a credit limit, the credit limit will be £7,000. ¨ Select the Credit Limit field. Point to the Default Value box, click the left mouse button, delete the 0, and type 7000. The Credit Limit field is selected. The default value is entered in the Default Value box. From this point on, if users do not make an entry in the Credit Limit field when adding records, Access will set the credit limit to £7,000. 4.4 Specifying a Collection of Legal Values.The only legal values for the Cust Type field are REG, SPC, and DSC. An appropriate validation rule for this field can make Access reject any entry other than these three possibilities. In other words, these are the only three legal values. Perform the following step to specify the legal values for the Cust Type field. Unlike criteria in queries, it is essential to enclose character values, such as REG, in quotation marks:- ¨ Select the Cust Type field. Point to the Validation Rule box, click the left mouse button, and type =" REG" OR ="SPC" OR ="DSC". Point to the Validation Text box, click the left mouse button, and type “Cust type must be REG, SPC or DSC”. The Cust Type field is selected and the validation rule and validation text have been entered. Users will now only be allowed to enter REG, SPC, or DSC in the Cust type field when they add records or make changes to this field. 4.5 Using a Format.Perform the following step to specify a format for the Customer Number field in the CUSTOMER table. The format symbol used in the example is > which causes Access to automatically convert lower case letters to uppercase. The format symbol < causes Access to automatically convert uppercase letters to lower case. ¨ Select the Customer Number field. Point to the Format box, click the left mouse button, and type > From this point on, any lower case letters users enter in the Customer Number field when they add records or change the value in this field will automatically be converted to uppercase. 4.6 Saving the Validation Rules, Default Values and Formats.Click the Save button. Point to the No button. The Microsoft Access dialog box should display. The message is asking if you wish to have the new rules applied to current records. If this were a database used to run a business or to solve some other critical need, choose the Yes button. You would not want to take the chance that some of the data already in the database violates the rules. Because none of the rules is violated by the data in the CUSTOMER table, choose the No button by clicking the left mouse button. Close the Table: CUSTOMER window by clicking its Close button. 4.7. Updating a table that contains Validation Rules.When updating a table that contains validation rules, Access provides a great deal of assistance in making sure the data entered is valid. Access helps in making sure that data is formatted correctly. Access also will not accept invalid data. Entering a number that is out of the required range, for example, or entering a value that is not one of the possible choices will produce an error message in the form of a dialog box. The database will not be updated until the error is corrected. ¨ If the customer number entered contains lower case letters, such as ss22, Access will automatically convert the data to SS22. ¨ If the Cust Type is not valid, such as XXX, Access will display the message you specified and will not allow the data to enter the database. ¨ If the Balance is not valid, such as 99999, Access also displays the appropriate message and refuses to accept the data. ¨ Instead of the Credit Limit initially appearing as £0.00, it will now appear as £7,000.00 because 7000 is the default value for the Credit Limit field. Thus, for any customer whose credit limit is £7,000.00, there is no need to enter the value. Simply pressing the T AB key accepts the £7000.00 that is currently displayed on the screen. If a required field is not entered, Access indicates this fact as soon as you attempt to leave the record. This field must be entered before Access will move to a different record. When a table has validation rules, it is possible to get stuck in a field. Perhaps you don't remember the validation rule you created or perhaps the one you created was incorrect. In any case, if you have entered data that violates the validation rule, you will not be able to leave the field nor can you simply close the table. The first thing to try is to enter an acceptable entry. If this does not work, repeatedly press the BACKSPACE key to erase the contents of the field and then try to leave the field. If, for some reason, this doesn't work either, select the Undo Current Record command from the Edit menu. The record will not be added to the database; instead, it will be removed from the screen. If you ever have to take such drastic action, you probably have a faulty validation rule. Use the techniques of the previous sections to correct the existing validation rules for the field. 4.8. Specifying Referential Integrity.A foreign key is a field in one table whose values are required to match the primary key of another table. For example, the sales rep number in the CUSTOMER table must match the primary key of the SLSREP table. In practice, this simply means that the sales rep number for any customer must be that of a real sales rep, i.e. a sales rep currently in the SLSREP table. You should not store a customer whose sales rep number is 05, for example, if there is no sales rep 05. The property that the value in a foreign key must match to that of another table's primary key is called referential integrity. In Access, to specify referential integrity, define a relationship between the tables by using the Relationships button. Access will then forbid any updates to the database that would violate the referential integrity. The type of relationship between two tables specified by using the Relationships button is referred to as a one-to-many relationship. This means that one record in the first table is related to (matches) many records in the second table, but each record in the second table is related to only one record in the first. In the sample database, for example, there is a one-to-many relationship between the SLSREP table and the CUSTOMER table. One sales representative is associated with many customers, but each customer is associated with a single sales representative. In general, the table containing the foreign key will be the "many" part of the relationship. Perform the following steps to use the Relationships button ¨ If a datasheet is currently on the screen, click its Close button to close it. Then, point to the Relationships button. ¨ Click the Relationships button, and then select the SLSREP table. The Add Table dialog box should display. ¨ Choose the Add button, select the CUSTOMER table, choose the Add button again, and then choose the Close button. Resize the Field List boxes that display so all fields are visible. Point to the Sales Rep Number field in the Field List box for the SLSREP table. Field list boxes for the SLSREP and CUSTOMER tables should display. The boxes have been re-sized so all fields are visible. ¨ Click and hold the left mouse button, drag the pointer to the Sales Rep Number field in the Field List box for the CUSTOMER table, and then release the left mouse button. Point to the Enforce Referential Integrity check box. The Relationships dialog box should display. ¨ The correct fields (the Sales Rep Number fields) have been identified as the matching fields. ¨ Click the left mouse button to place a tick in the Enforce Referential Integrity check box. Enforce Referential Integrity is selected (see Figure 6). This will cause Access to reject any update that would violate referential integrity.
Figure 6. ¨ Choose the Create button and point to the Save button. Access creates the relationship and displays it visually with a line joining the two Sales Rep Number fields (Figure 7). The number 1 by the Sales Rep Number field in the SLSREP table indicates that the SLSREP table is the "one" part of the relationship. The symbol at the Customer end of the arrow indicates that the CUSTOMER table is the "many" part of the relationship.
Figure 7. ¨ Click the Save button to save the relationship. ¨ Click the Close button to close the Microsoft Access [Relationships] window and then choose the Yes button. Access will now reject any number in the Sales Rep Number field in the CUSTOMER table that does not match a sales rep number in the SLSREP table. Trying to add a customer whose sales rep number does not match would result in the error message shown in Figure 8.
Figure 8. Deleting a sales representative for which there are related customers, would also cause a problem. These customers would now have a sales rep number that does not match any sales representative. Deleting Sales Rep 04 from the SLSREP table, for example, would cause a problem for all records in the CUSTOMER table on which the sales rep number is 04. To prevent this problem, Access will forbid such a deletion. Instead of deleting the record, Access will display the message shown in Figure 8. 5. Bulk Updates.Bulk updates are also sometimes known as mass updates. Earlier in the course, an update query was used to change all the entries in the Cust type column to REG. The updates can also involve criteria. 5.1. Deleting Groups of RecordsIn some cases, there may be several records to be deleted at a time. If, for example, territories change and customers whose postcode begins WS11 are assigned to a different organisation, all the customers who have this postcode should be deleted. Instead of deleting these customers individually, which would be very cumbersome, delete them in one operation by using an update query. Perform the following steps to use an update query to delete all customers whose postcode begins WS11. ¨ If there is a datasheet on the screen, close it by clicking its Close button. Click the Query button, choose the New button, choose the New Query button. A New Query dialog box should appear and select Design View and click OK. In the Add Table dialog box, select the CUSTOMER table, choose the Add button, and then choose the Close button. Adjust the size of the two portions of the Query screen, as well as the list box containing the fields in the CUSTOMER table. Select the Query menu and point to the Delete command. The Microsoft Access - [Query1: Delete Query] window should display. All the fields in the CUSTOMER table display. ¨ Double-click the Post Code field to select the field, and then point to the Criteria box. The Post Code field displays in the Field box indicating it is selected. ¨ Click the left mouse button and then type WS11*. The criterion is entered in the Post Code column and the * is a wild card denoting that all records containing a post code commencing WS11 will be deleted. ¨ Click the Run button and then point to the 0K button. The Microsoft Access dialog box displays and the message indicates that this particular query will delete 4 rows (records). ¨ Choose the 0K button. The records are deleted. Four customers (AN91, FY16, PA12 and RO22) have been removed from the table. 5.2. Changing Groups of Records.Just as update queries can delete several records at once, they can also change several records at a time. To change the credit limit of all customers whose credit limit is currently £4,000 to £5,000 would be very cumbersome individually. Again, an update query can simplify the process. Perform the following steps to use an update query to change the credit limit of all customers whose credit limit is £4,000 to £5,000:- ¨ Select the Edit menu and choose the Clear Grid command to clear the QBE grid. Then, select the Query menu and point to the Update command. The Microsoft Access - [Query1: Update Query] window should display. ¨ Double-click the Credit Limit field to select the field, enter 5000 as the Update To: value, and enter 4000 as the Criteria: value. Then, point to the Run button. The Credit Limit field is selected, the value 5000 is entered in the Update To: box and the value 4000 is entered in the Criteria: box. ¨ Click the Run button and then choose the OK button in the Microsoft Access dialog box. The query executes. ¨ Close the query by clicking its Close button and choose the NO button. Choose the Table button, select the CUSTOMER table, and then choose the Open button. Click the right scroll arrow three times to display the Credit Limits column. The CUSTOMER table displays in Datasheet view. The credit limits that were £4,000 have been changed to £5,000. 6. Creating and Using Indexes.6.1 You are already familiar with the concept of an index. The index in the back of a book contains important words or phrases, as well as a list of pages on which the given words or phrases can be found. An index for a database table is similar. Figure 9, for example, shows the CUSTOMER table and an additional secondary index built on customer names. In technical terms, Name is the index key. In this case, the items of interest are customer names instead of key words or phrases as in a book index. Each customer name occurs in the index in figure 9 along with the number of the record on which the customer name is located. Further, the names appear in the index in alphabetical order. To use this index to find Forrest Co., for example, rapidly scan the names in the index to find the associated customer number for Forrest Co. Next, look at the corresponding record number (5) and then go immediately to that record in the CUSTOMER table, thus finding this customer much more rapidly than by looking through the entire CUSTOMER table one record at a time. This is the same action that Access takes when it uses an index. Thus, indexes make the process of retrieving records very fast and efficient. Index on Name Column CUSTOMER Table
Figure 9. There is another benefit to indexes. Indexes provide an efficient alternative to sorting. That is, if the records should appear in a certain order, it is easier to use an index instead of physically rearranging the records in the table. Physically rearranging the records in a different order, which is called sorting, can be a very time consuming process. To see how indexes can be used for alphabetising records, look at the record numbers in the index and suppose you used these to list all customers. i.e. simply follow down the record number column, listing the corresponding customers. In this example, you would first list the customer on record 2 (Alliance West), then the customer on record 1 (Atwater-Nelson), then the customer on record 3 (Betterdial), and so on. The customers would be listed alphabetically by Name without actually sorting the table. To gain the benefits from an index, you must first create one. Access automatically creates an index on the primary key. This index is called the primary index. The other indexes are called secondary indexes. You must create the secondary indexes, indicating the field or fields on which the index is built. 6.2. Creating Single-Field Indexes.Perform the following steps to create two single-field secondary indexes. For the first one, the index key will be the Name field. For the second, the index key will be the Post Code field. Each case needs to indicate whether to allow duplicates, i.e. two records that have the same value in the index key. For example, in the secondary index for the Name field, if duplicates are not allowed, Access would not allow the addition of a customer whose name is the same as the name of a customer already in the database. In both of the indexes created in the following steps, duplicates will be allowed: ¨ With the CUSTOMER table open and its datasheet maximised, click the Design button. Select the Name field, point to the Indexed box, and click the left mouse button. Then, point to the drop-down arrow next to the Indexed box. ¨ Click the drop-down arrow and point to Yes (Duplicates OK). The Indexed list displays. The mouse pointer points to Yes (Duplicates OK). ¨ Click the left mouse button to select Yes (Duplicates OK). Select the Post Code field, point to the Indexed box, and click the left mouse button. Click the drop-down arrow, which is next to the Indexed box, and select Yes (Duplicates OK). The Post Code field is selected. The Indexed entry is Yes (Duplicates OK}. The indexes on the Name and Post Code fields have now been created and are ready for use. 6.3. Creating Multiple-Field IndexesCreating multiple-field secondary indexes, indexes created on more than one field, involves a different process from creating single-field secondary indexes. Select the Indexes command from the View menu and then enter the combination of fields that make up the index key. Perform the following steps to create a multiple-field secondary index with the name CREDBAL. The key will be the combination of the Credit Limit and the Balance fields: ¨ Select the View menu and point to the Indexes command. ¨ Choose the Indexes command. The Indexes: CUSTOMER dialog box should display. The index on Customer Number is the Primary index and was created automatically by Access. The indexes on Name and Post Code are the ones just created. Use this dialog box to create additional indexes. ¨ Select the Index Name entry on the row following Post Code by pointing to it and clicking the left mouse button. Type “CREDBAL” as the Index name, press the TAB key, and point to the drop-down arrow. The Index name has been entered as CREDBAL. An insertion point appears in the Field Name column. ¨ Click the left mouse button to produce a list of fields in the CUSTOMER table and point to the down scroll arrow. A list of fields in the CUSTOMER table should display. ¨ Click the down scroll arrow once to bring the Credit Limit field to the screen and then select the Credit Limit field by pointing to it and clicking the left mouse button. Press the TAB key three times to move to the Field Name entry on the following row. Select the Balance field in the same manner as the Credit Limit field. Point to the Close button for the Indexes: CUSTOMER dialog box. Credit Limit and Balance are selected as the two fields for the Credbal index. The absence of an Index name on the row containing the Balance field indicates that it is part of the previous index, Credbal. ¨ Close the Indexes: CUSTOMER dialog box by clicking its Close button. ¨ Save the changes by clicking the Save button. Then, click the Datasheet View button to move to Datasheet view. The indexes are created and the datasheet displays. The indexes have now been created. Access will use them automatically, whenever possible, to improve efficiency of ordering or finding records. It will also maintain them automatically. That is, whenever the data in the CUSTOMER table is changed, Access will automatically make appropriate changes in the indexes. 6.4. Ordering Records.Recall from previous discussions that Access sequences the records by customer number whenever listing them because customer number is the primary key To TEMPORARILY change the order in which records appear, click the Sort Ascending button Perform the following steps to order the records by customer name: ¨ Open the CUSTOMER table in Datasheet view, press the TAB key to move to the LastName field, and point to the Sort Ascending button. ¨ Click the Sort Ascending button. The rows are now ordered by LastName. If you require to sort on multiple fields, perform the following steps to order records on a combination of fields. For example, to sort on the LastName field first and then on the FirstName field, the LastName field must be to the left of the FirstName field in the grid. ¨ Create a new query for the relevant table(s) and display the Advanced Filter/Sort (QBE) grid window. ¨ To sort on more than one field, first arrange the fields in the design grid in the order you want the sorts performed. Microsoft Access sorts on the leftmost field first, then on the next field to the right, and so on. For example, to sort on the LastName field first and then on the FirstName field, the LastName field must be to the left of the FirstName field in the grid. ¨ In the Sort cell for each of the fields you want to sort on, click an option i.e. ascending or descending. ¨ Run the query to see the results.
|
. Before clicking this button, move the highlight to the field for the search.
to specify referential integrity by specifying a relationship between the SLSREP and CUSTOMER tables.
or Sort Descending button 
. These will reorder the records, based on the field in which the cursor is located.
|
|
|
[Home] [Database Creation] [Queries] [Maintenance] [Reports] [Normalisation] [Switchboards] [Macros] |