WX0995: Table and Query Questions and AnswersLast reviewed: October 21, 1997Article ID: Q114810 |
The information in this article applies to:
Novice: Requires knowledge of the user interface on single-user computers. The "Table and Query Questions and Answers" (WX0995) Application Note contains questions and answers about Microsoft Access version 2.0 tables and queries. You can obtain this Application Note from the following sources:
THE TEXT OF WX0995
Microsoft(R) AnswerPoint Application Note (Text File)
WX0995: TABLE AND QUERY QUESTIONS AND ANSWERS
Revision Date: 3/94
No Disk Included
The following information applies to Microsoft Access(R) version 2.0.
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
| Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER |
| EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED |
| WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR |
| PURPOSE. The user assumes the entire risk as to the accuracy and |
| the use of this Application Note. This Application Note may be |
| copied and distributed subject to the following conditions: 1) All |
| text must be copied without modification and all pages must be |
| included; 2) If software is included, all files on the disk(s) |
| must be copied without modification (the MS-DOS(R) utility |
| diskcopy is appropriate for this purpose); 3) All components of |
| this Application Note must be distributed together; and 4) This |
| Application Note may not be distributed for profit. |
| |
| Copyright (C) 1994 Microsoft Corporation. All Rights Reserved. |
| Microsoft, Microsoft Access, and MS-DOS are registered trademarks |
| and Windows is a trademark of Microsoft Corporation. |
|---------------------------------------------------------------------|
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, you can order item number Q112111 by
selecting the FastTips Technical Library option from the
FastTips Main Menu.
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. Or, you can
order item number Q112112 by selecting the FastTips Technical
Library option from the FastTips Main 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, you can order item
number Q112160 by selecting the FastTips Technical Library
option from the FastTips Main Menu.
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.
TO OBTAIN THIS APPLICATION NOTE
Keywords : Fstqa Softlib kbappnote kbfile kbfasttip Version : 2.0 Platform : WINDOWS Hardware : x86 Issue type : kbinfo |
================================================================================
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |