ACC2: Table and Query Questions and Answers (114810)

MORE INFORMATION

1. Q. How have queries changed in Microsoft Access version 2.0?
   
   A. Queries are now more flexible and are easier to create. The new QueryWizards--Crosstab, Find Duplicates, Find Unmatched, and Archive--help you quickly create complex queries for managing your data.
   
   There are also three new SQL-specific queries. These are union queries, data-definition queries (queries that create, change, or delete tables and indexes in Microsoft Access databases), and SQL pass-through queries (queries that pass SQL statements directly to a SQL database, providing improved connectivity in client-server relationships).
   
   In addition, improvements in updating in multiple-table queries and the addition of TopValues queries and subqueries give you greater query functionality.
2. Q. When I add two tables to my query that do not have a defined relationship, Microsoft Access automatically joins them. Can I prevent this from happening?
   
   A. Microsoft Access 2.0 automatically joins two tables in a query if the tables meet the following criteria:
   
   
   • There is no relationship defined between the tables.
   • Each table contains at least one field whose name and data type matches the name and data type of a field in the other table.
   • One of the tables has a primary key defined on the matching field.
   
   Only one AutoJoin is automatically created between two tables. Even if there is more than one join possible between the two tables, a join is created only between the first fields that meet the above criteria. If you add three tables that meet the above criteria to a query, three joins are created--one for each table pair.
   
   You cannot turn this functionality off. You must either delete the join line after it is created or manually define a relationship between the two tables.
3. Q. How does the new Relationships window work? What does it mean when I am prompted to save the layout?
   
   A. The new Relationships window gives you a graphical display of the tables in your database and their relationships to each other. You can customize the Relationships window and view as many or as few tables as you want. You can also create or change table relationships in this view. For more details about the Relationships window, order the fax or mail copy of this script.
   • To create a relationship, drag the related field from the primary table to the related table. Microsoft Access will then display the Relationships dialog box. When you choose OK, the relationship is created and saved.
   • Removing (deleting) a table from the Relationships window does not delete the table or any of its relationships from the database; it simply hides that particular table from view.
   • To delete a relationship, select the join line and press the DEL key.
   • To make sure that you are seeing all the defined relationships in your database, choose Show All from the Relationships menu.
   • When you close the Relationships window, you are prompted to save the window's layout. Choosing Yes or No does not affect whether your relationships are saved, but only the positions of the visible tables in the Relationships window.
   • To show only the relationships for a specific table, use the following three steps:
     1. From the Edit menu, choose Clear Layout, and then choose OK when you are prompted. Note that this clears the layout only and does not delete existing relationships.
     2. From the Relationships menu, choose Add Table. In the Table/Query box, select the table whose relationships you want to see, and then choose Add. Choose Close.
     3. From the Relationships menu, choose Show Direct.
        
        NOTE: If you do not clear the layout before choosing Show Direct, the relationships for the table you selected will be displayed in addition to any other relationships that are already being displayed.
4. Q. Why can I update more fields in my query than I could in Microsoft Access version 1.x?
   
   A. In Microsoft Access 2.0, when a query includes fields from more than one related table, you can update data on both sides of the join. This means that in a query that combines data from two tables, you can update data from both of the tables in the query.
   
   If you want to prevent users from updating fields in a multiple- table query, create a form based on the query and then set the Locked property for the fields you do not want users to update.
   
   For more information about updating queries, search for "queries: updating underlying tables" and then view both of the related topics using the Microsoft Access Help menu.
5. Q. How have validation rules and default values changed?
   
   A. In Microsoft Access 2.0, validation rules are always enforced, no matter how you add or edit data. Because default values and validation rules are always enforced, the following items are not valid in default values or field-level validation rules:
   • References to fields or controls. Instead, use record validation rules when you are comparing two or more fields. For more information about record validation rules, search for "validation: rules and text" then "Validating Data in a Table" using the Microsoft Access Help menu.
   • User-defined functions.
   • Microsoft Access domain functions.
   • Aggregate functions.
   • CurrentUser() or Eval() functions.
6. Q. Why can't I set referential integrity?
   
   A. Referential integrity is a set of rules that preserves the defined relationships between tables when you enter or delete records. If you enforce referential integrity, you cannot add a record to a related table when there is no associated record in the primary table, change a value in a primary table that would result in an orphan record in a related table, or delete a record from a primary table when there is a matching related record.
   
   If you select the Cascade Update Related Fields or Cascade Delete Related Records option for a relationship, Microsoft Access will change or delete related records to ensure that the referential integrity rules are enforced when you change or delete records.
   
   There are several possible reasons why you can't enforce referential integrity when you are defining a relationship. For additional information, see 112111.
7. Q. How can I optimize my queries?
   
   A. Microsoft Access 2.0 uses Rushmore, a data-access technology that permits sets of records to be queried very efficiently. Here is a summary of query performance tips:
   
   
   • Index the fields used in sorts and criteria.
   • Index the fields used in joins in both tables.
   • Use multiple-field indexes on fields where there are multiple-column joins between the tables.
   • If a table has a single-field primary key, do not add a separate index to the primary key field. If a table has a multiple-field primary key, it may help to have a separate index on each field.
   • Use outer joins only when necessary--outer joins limit the options for the query optimizer.
   
   For more information about query performance, search for "Rushmore technology" then "Optimizing Queries with Rushmore Technology" using the Microsoft Access Help menu.
8. Q. Why do I see a number instead of "(counter)" for my counter field?
   
   A. Microsoft Access 2.0 enters a counter value when you start to edit a new record. In Microsoft Access 1.x, this value was entered after you saved the record.
   
   Since this value is now provided earlier, if you start editing a new record and then cancel the record, the counter value is still used, even though no record is stored with the value. For example, when you add a new record to a table containing two records, the counter value is 3. If you cancel this new record and then later add another new record, the counter value is 4 for the new record.
   
   Counter values are not reused when you delete records. For example, if in a table of 15 records you delete the last three records and then add a new record, the counter value for the new record is 16. To reset the next available counter value, compact the database. After the database is compacted, the next available counter is set to one higher than the last counter value in the table.
   
   For additional information about counters, see 112160.
9. Q. While I was creating a query, when I switched from a Select query to a SQL Specific query, the SQL statement disappeared. When I switched back to a Select query, my query disappeared. Where did my query go?
   
   A. When you switch to a SQL Specific query, the SQL statement from the existing query is deleted. If you want to start with a Select query to help create the SQL statement for a SQL Specific query (to save some typing) use the following six steps:
   1. Create the Select query.
   2. From the View menu, choose SQL.
   3. Select the entire SQL statement, except for the ending semicolon.
   4. Copy the selected text to the Clipboard by pressing CTRL+C, or by choosing Copy from the Edit menu.
   5. From the Query menu, choose SQL Specific, then choose a query type.
   6. Paste the query text into the Query window by pressing CTRL+V, or by choosing Paste from the Edit menu.
10. Q. How do I remove the underline characters in my input masks?
    
    A. An input mask consists of up to three parts, separated by semicolons. The first part specifies the input mask itself, the second part specifies whether Microsoft Access stores the literal display characters in the table when you enter data, and the third part specifies the character that Microsoft Access displays in the input mask for spaces.
    
    If you omit the third part, Microsoft Access uses an underline character for the input mask. For example, in the Customers table in the sample database NWIND.MDB, the input mask on the Customer ID field is:
    

              >LLLLL
    						

    
    When you enter data in a new record, five underlines are displayed in the field. You can replace these underlines with spaces by using the following for the input mask:
    

              >LLLLL;;" "
    						

    
    For more information about input masks, search for "input mask" then "Adding an Input Mask" using the Microsoft Access Help menu.