MS-Access / Getting Started

---

Working with Query Datasheets

A query datasheet looks a great deal like a table datasheet - you can sort, filter, navigate, and in some circumstances, enter data in the query datasheet. The data displayed in the query datasheet is sometimes referred to as a dynaset - a dynamic subset of your data.

The query result reflects changes in the data in your tables. The actual records displayed in a dynaset aren't stored in the database; only the design of the query is stored. Each time you open the query in Datasheet view, the query definition determines which records appear in the datasheet.

Because working with queries in Datasheet view is similar to working with tables in Datasheet view. To toggle between Datasheet and Design view, click the View button, the first button on the Home and Design/Datasheet tabs on the Ribbon.

Using the query datasheet to edit data

In many cases, you can edit the data in the query datasheet and use the datasheet to add new records. Any changes you make are reflected in the table that holds the data you changed - edits are permanent and apply to the underlying tables and not just to the query.

When your query includes fields from multiple tables, you may see some funky things on-screen when you edit data.

• You may see other data in the datasheet change when you make an edit. If your query includes related tables, you may see repeated data. If you make edits, you see all the repetitions of the name change when you change one instance. Because you are changing a single record repeated in the datasheet, the other instances change to reflect the change in the underlying table. When this happens, you have happened on an AutoLookup query. The next section covers AutoLookup queries.
• If your query meets the qualifications of an AutoLookup query, Access may fill in fields after you enter a single value.

If you work with a query datasheet that shows data from multiple related tables, you may not be able to modify data. The rules get complicated, but generally all data on the "many" side of a one-to-many relationship can be updated. Data on the one side usually can be updated if you are not editing the primary key field.

AutoLookup queries to fill in data automagically

AutoLookup queries can be a terrific tool when you want to enter one value (such as a customer number) and see other data from the same table (such as the customer's name, address, and phone number). You may want to use this feature as you enter a new order - you can enter a customer number and see the contact information, and then enter the particulars of the order, such as the date and payment method. You can even create an AutoLookup query and use it as the basis of a form, where it may be more convenient to enter data. AutoLookup queries may sound complicated, but in fact they're pretty simple.

The AutoLookup feature also works in forms.
The key to creating an AutoLookup query is that you must include the Join field from the "many" side of the one-to-many relationship (also known as the foreign key). Then when you enter a value for that field, Access fills in other fields from the "one" side of the relationship automatically.

When new orders are entered into the query datasheet, only the customer number needs to be entered - Access automatically fills in the first name, the last name, and other contact information from the Address Book table. The rest of the Order information can then be added.