This article was previously published under Q114810
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.
This article contains questions and answers about Microsoft Access version
2.0 tables and queries.
- 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
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
In addition, improvements in updating in multiple-table queries
and the addition of TopValues queries and subqueries give you
greater query functionality.
- 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
- One of the tables has a primary key defined on the matching
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.
- 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
- 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
- To delete a relationship, select the join line and press the
- To make sure that you are seeing all the defined
relationships in your database, choose Show All from the
- 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:
- 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.
- 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.
- 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.
- 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
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.
- 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.
- 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
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
There are several possible reasons why you can't enforce
referential integrity when you are defining a relationship. For
additional information, see 112111
- 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.
- Q. Why do I see a number instead of "(counter)" for my
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
- 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
- Create the Select query.
- From the View menu, choose SQL.
- Select the entire SQL statement, except for the ending
- Copy the selected text to the Clipboard by pressing CTRL+C,
or by choosing Copy from the Edit menu.
- From the Query menu, choose SQL Specific, then choose a
- Paste the query text into the Query window by pressing
CTRL+V, or by choosing Paste from the Edit menu.
- Q. How do I remove the underline characters in my input
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:
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:
For more information about input masks, search for "input mask"
then "Adding an Input Mask" using the Microsoft Access Help
Article ID: 114810 - Last Review: January 7, 2015 - Revision: 1.0
- Microsoft Access 2.0 Standard Edition
|kbnosurvey kbarchive kbinfo KB114810||