Rick Selby's Access Queries Help Page

Open a query in Design View, or open a form or datasheet and display the Advanced Filter/Sort Window.
In a query, MAKE SURE the Field List for the table or query containing the fields you want to add appears in the top portion of the window. You can add a table or query if the Field List you need isn't in the query.
Note
- In the Advanced Filter/Sort Window, Access displays the Field List for the underlying table or query. You CAN'T add more tables or queries to this window.
Select one or more fields from the Field List and drag them to the columns in the grid.

Add a Table or Query to a Query:

Open a query in Design View.
On the toolbar, click Show Table.
In the Show Table Dialog Box, click the tab that lists the objects whose data you want to work with.
If the table you need is in another database or application, first link the table to the active database.
Click the name of the object you want to add to the query.
To select additional objects one at a time, hold down CTRL while you click each object name.
To select a block of objects, click the first name in the block, hold down SHIFT, and then click the last name in the block.
Click Add, and then click Close.
Notes
- You can also add a table or query to a query by dragging the table or query name from the Database Window to the upper part of Query Design View.
- When you have multiple tables or queries in a query, they need to be joined together.

Enter criteria in a query or advanced filter to retrieve certain records:

Open a query in Design View, or display the Advanced Filter/Sort window for a table, query, or form.
Click the first criteria cell for the field you want to set criteria for.
Enter the criteria expression by typing it or by using the Expression Builder.
- To display the Expression Builder, right-click in the criteria cell and then click build.
- If your query includes linked tables, the values you specify in criteria on fields from the linked tables are case-sensitive - they MUST match the case of the values in the underlying table.
- Notes
  - When you view a subdatasheet in a query, the Advanced Filter/Sort Window contains the record source ONLY for the selected subdatasheet.
  - To filter on fields contained in the main datasheet or in a different subdatasheet, close the Advanced Filter/Sort Window, select the main datasheet or the subdatasheet on which you want to set filter criteria, and then reopen the Advanced Filter/Sort Window.
To enter another expression in the same field or in another field, move to the appropriate criteria cell and enter the expression.

Create:
Create an Auto Lookup Query that automatically fills in data:

For Auto Lookup to work on new records, certain conditions MUST be met.
- Create a Select Query, adding two tables that have a one-to-many relationship.
- Add the fields for the query to the Design Grid.
  - You MUST include the Join Field from the "many" side of the one-to-many relationship.
  - If you want, specify criteria in the criteria row to select specific records.
To view the query's results, click View on the toolbar.
When you enter new data in the Join Field on the "many" side of the relationship, Access will automatically look up and fill in the corresponding data from the "one" side.

Create a Crosstab Query using a Wizard:

In the Database Window, click Queries under Objects, and then click New on the Database Window Toolbar.
In the New Query Dialog Box, click Crosstab Query Wizard.
Click OK.
Follow the directions in the Wizard Dialog Boxes. In the last Dialog Box, you can choose to run the query or see the query's structure in Design View.
Notes
- If the resulting query isn't exactly what you want, you can rerun the wizard or change the query in Design View.
- You can display crosstab data without creating a separate query in your database either by using the Pivot Table Wizard in a form or by creating a Pivot Table List in a Data Access Page. With a Pivot Table Form or Pivot Table List, you can change Row and Column headings on demand to analyze your data in different ways.

Create a Crosstab Query Without using a Wizard:

In the Database Window, click queries under Objects, and then click New on the Database Window Toolbar.
In the New Query Dialog Box, click Design View, and then click OK.
In the Show Table Dialog Box, click the tab that lists the tables or queries whose data you want to work with.
Double-click the name of each object you want to add to the query, and then click close.
Add fields to the field row in the Design Grid and specify criteria.
On the toolbar, click Query Type, and then click Crosstab.
For the field or fields whose values you want to appear as row headings, click the crosstab row, and then click row heading.
- You MUST leave the default Group By in the total row for these fields.
- For the field whose values you want to appear as column headings, click the crosstab row, and then click column heading.
- You can choose column heading for one field ONLY, and you MUST leave Group By in the total row for this field.
  - By default, the column headings are sorted in alphabetic or numeric order.
  - If you want them to appear in a different order, or if you want to limit, which column headings display, set the query's column headings property.
- For the field whose values you want to use in the Cross-tabulation, click the crosstab row, and then click Value.
- ONLY one field can be set to value.
In the total row for this field, click the type of Aggregate Function you want for the Cross-tabulation (such as Sum, Avg, or Count).
To specify criteria that limit row headings before the calculation is performed, enter an expression in the criteria row for a field with row heading in the crosstab cell.
Example
- You might display sales totals for products in certain categories, such as meat and seafood.
Note
- To specify criteria that limit records before the row headings are grouped and before the Cross-tabulation is performed, add the field you want to set criteria for to the Design Grid, click "Where" in the total cell, leave the crosstab cell blank, and then enter an expression in the criteria row. (The query results won't display fields that have "Where" in the total row.)
To view the query's results, click view on the toolbar.
To stop the query after you start it, press CTRL+BREAK.
Notes
- If you include a field in the Query Design Grid, but click the (Not Shown) option in the crosstab cell and group by in the total cell, Access groups on it as a row heading, but doesn't display the row in the query's results.
- The values in your column heading field may include characters usually not allowed in field names, such as decimals. If they do, Access replaces the character with an underscore in the datasheet.

Create a Field that performs Custom Calculations or Manipulates Field Values in a Query:

Note
- You can create a new field that displays the results of a calculation you define with an expression.
Open the query in Design View.
Do one of the following:
- Type an expression in an empty cell in the field row.
- If the expression includes a Field Name, you MUST place brackets around the name.
- Notes
  - If you need help creating the expression, use the Expression Builder.
  - To display it, right-click in the field cell where you're adding the Calculated Field, and then click Build.
  - After you press ENTER or move to another cell, Access enters the Default Field Name ExprN, where N is an integer incremented for each new expression field in the query.
  - The name appears before the expression and is followed by a colon.
  - In a datasheet, this name is the column heading.
  - You can select ExprN and type a more descriptive name, such as New Price.
If the expression includes one or more Aggregate Functions (Sum, Avg, Count, Min, Max, StDev, or Var), and if the Design Grid includes another field or fields that you want to use for grouping, click Totals on the toolbar (unless the Total Row is already displayed).
Leave Group By in the Total Cell for the Grouping Field, and in the Calculated Field, change Group By to Expression.
If you want, do any of the following to complete the query:
- Enter criteria to affect the calculation.
- Sort the results. Set field properties such as format (since the field doesn't inherit properties from the underlying table).
Note
- To enter or view an entire expression without scrolling, press SHIFT+F2 to display the Zoom Box.

Creating a Query using Sum, Average, Count, Max, or Min Functions

Below are the functions and what they do:

Function	Description
Sum	Sums the values in the calculated field
Average	Finds the average value of the calculated field
Count	Counts the number of records in the calculated field
Max	Finds the highest value in the calculated field
Min	Finds the lowest value in the calculated field

Creating a query
- A word before you start. This can seem complicated so if you are confused try what you believe to be correct, if you are wrong just undo what you did and read the instructions again. Once you do this a few times it gets easier but the first few times I have found it hard to explain or a better way to explain it.
- First use a or create a database to learn on and do not use your database until you have tried this and get used to it. It is easier to do it a second time than it is to have to create the database all over so just use a dummy database to learn on.
- Open your database you want to do the query in.
- Select the name of the table you want to add to the query.
- Once the table is selected.
- Select Add.
  - The field list for the table will appears in the top pane of Design view.
- Select Close to close the dialog box.
  - The dialog box will close.
- Select the field by which you want to use.
  - The field and table names appear at the top in the design grid.
- Select the View menu.
  - The View menu appears.
- Select the Totals command.
- A Total row is added to the design grid.
- Select the Total row under the field you want to calculate.
  - The cursor will appears in the Total row under the appropriate field and an drop-down arrow will appear to the right of the field.
- Select the Total list.
  - A list with all of functions appears.
- Select the desired function.
- The name of the function will appears in the Total row under the appropriate field.
- Click Queries on the Menu toolbar and then Run.

Create a new Table from the results of a Query with a Make-Table Query:

In Query Design View, click the arrow next to Query Type on the toolbar, and then click Make Table.
The Make Table Dialog Box appears.
In the Table Name Box, enter the name of the table you want to create or replace.
Click Current Database to put the new table in the currently open database. Or click Another Database and type the name of the database you want to put the new table in.
- Type the path if necessary.
Click OK.
Drag from the Field List to the Query Design Grid the fields you want in the new table.
In the Criteria Cell for the fields that you've dragged to the grid, type the criteria.
To preview the new table before you create it, click View on the toolbar.
To return to Query Design View and make changes or run the query, click View on the toolbar.
To create the New Table, click Run on the toolbar.
Notes
- To stop a query after you start it, press CTRL+BREAK.
- The data in the new table you create DOES NOT inherit the Field Properties or the Primary Key setting from the original table.

Create a Parameter Query that Prompts for Criteria each time it is run:

Note
- A Parameter Query displays one or more Predefined Dialog Boxes that prompt you for the Parameter Value (Criteria). You can also create a Custom Dialog Box that prompts for the query's parameters.
Create a Select or Crosstab Query.
In Query Design View, drag the fields from the Field List to the Query Design Grid.
In the Criteria Cell for each field you want to use as a parameter, type a prompt enclosed in Square Brackets.
- Access will display this prompt when the query is run.
- The text of the prompt MUST be different from the field name, although it can include the field name.
- Example
  - For a field that displays dates, you can display the prompts "Type the beginning date:" and "Type the ending date:" to specify a range of values. In the field's criteria cell, type between [Type the beginning date:] and [Type the ending date:].
To view the results, click View on the toolbar, and then type a value for the parameter. To return to Query Design View, click View on the toolbar again.
Notes
- You MUST specify a data type for parameters in a Crosstab Query or in a Parameter Query that a Crosstab Query or chart is based on.
- In the Crosstab Query, you MUST also set the Column Headings property.
- To prompt the user for one or more characters to search for, and then find records that begin with or contain the characters the user specifies, create a Parameter Query that uses the LIKE operator and the wildcard symbol (*).
Example
- The statement: LIKE [Enter the first character to search by:] "*" searches for words that begin with a specified letter. The statement: LIKE "*" [Enter any character to search by:] "*" searches for words that contain the specified character.

Create a Query that contains the Table whose Records you want to append to another Table:

In Query Design View, click the arrow next to Query Type on the toolbar, and then click Append. The Append Dialog Box appears.
In the Table Name Box, enter the name of the table you want to append records to.
Click Current Database if the table is in the currently open database. Or click another database and type the name of the database where the table is stored. Type the path if necessary.
- You can also enter a path to a Microsoft Fox Pro, Paradox, or dBASE database, or a connection string to an SQL Database.
Click OK.
Drag from the field list to the Query Design Grid the fields you want to append and any fields you want to use for setting criteria.
- Also, you may or may not want to add the Primary Key Field if it has an Auto Number Data Type.
- If all the fields in both tables have the same names, you can just drag the Asterisk (*) to the Query Design Grid.
- However, if you're working in a database replica, you'll need to add all the fields instead.
- If the fields you've selected have the same name in both tables, Access automatically fills the matching name in the Append To Row.
- If the fields in the two tables don't have the same name, in the Append To Row, enter the names of the fields in the table you're appending to.
In the Criteria Cell for the fields that you have dragged to the grid, type the criteria on which additions will be made.
To Preview the records that the query will append, click View on the toolbar.
To return to Query Design View, click View on the toolbar again.
- Make any changes you want in Design View.
Click Run on the toolbar to add the records.
Note
- To stop a query after you start it, press CTRL+BREAK.

Create a Query selecting the Tables or Queries that include the records you want to update and the fields you want to use for Setting Criteria:

In Query Design View, click the arrow next to Query Type on the toolbar, and then click Update Query.
Drag from the field list to the Query Design Grid the fields you want to update or you want to specify criteria for.
In the Criteria Cell, specify the criteria if necessary.
In the Update To Cell for the fields you want to update, type the expression or value you want to use to change the fields.
To see a list of the records that will be updated, click View on the toolbar.
- This list will not show the new values.
- To return to Query Design View, click View on the toolbar again.
Make any changes you want in Design View.
Click Run on the toolbar to update the records.
Note
- To stop a query after you start it, press CTRL+BREAK.

Create a Simple Select Query with a Wizard:

Notes
- The Simple Select Query Wizard creates queries that retrieve data from the fields you specify in one or more tables or queries.
- If you want, the wizard can also Sum, Count, and Average Values for groups of records or all records, and it can calculate the minimum or maximum Value in a field.
- You CAN'T limit the records it retrieves by setting criteria, however.
In the Database Window, click queries under objects, and then click New on the Database Window Toolbar.
In the New Query Dialog Box, click Simple Query Wizard.
Click OK.
Click the name of the table or query you want to base your query on, and then select the fields whose data you want to retrieve.
Click an additional table or query if desired, and then select the fields you want to use from it.
Repeat this step until you have all the fields you need.
Follow the directions in the Wizard Dialog Boxes.
In the last Dialog Box, you can choose either to run the query or to see the query's structure in Design View.
If the resulting query isn't exactly what you want, you can rerun the wizard or change the query in Design View.

Delete Records from one Table or Tables in a One-to-One Relationship:

Create a new query that contains the tables from which you want to delete records.
In Query Design View, click the arrow next to Query Type on the toolbar, and then click Delete Query.
For the tables you want to delete records from, drag the Asterisk (*) from the Field List to the Query Design Grid.
From appears in the delete cell under these fields.
To specify criteria for deleting records, drag to the Design Grid the fields on which you want to set criteria.
Where Appears in the Delete Cell under these fields.
In the Criteria Cell for the fields that you have dragged to the Grid, type the criteria.
To preview the records that will be deleted, click View on the toolbar.
- To return to Query Design View, click View on the toolbar again.
Make any changes you want in Design View. Click Run on the toolbar to delete the records.
Note
- To stop a query after you start it, press CTRL+BREAK.

Delete Records by using a Query that includes only the One Table in a One-to-many Relationship:

Note
- When you run a delete query on the "one" table in a one-to-many relationship, and you've enabled Cascading Deletes for this relationship, Access also deletes related records from the "many" side.
Create a new query that contains the "one" table from which you want to delete records.
In Query Design View, click the arrow next to Query Type on the toolbar, and then click Delete Query.
Drag the Asterisk (*) from the field list for the table to the Query Design Grid.
- From Appears in the Delete Cell under this field.
To specify criteria for deleting records, drag to the Design Grid the fields on which you want to set criteria.
- Where Appears in the Delete Cell under this field.
In the Criteria Cell for the fields that you have dragged to the Grid, type the criteria.
To preview the records that will be deleted, click View on the toolbar.
To return to Query Design View, click View on the toolbar again.
Make any changes you want in Design View.
Click Run on the toolbar to delete the records. Note
- To stop a Query after you start it, press CTRL+BREAK.

Delete Records by using a Query that includes both Tables in a One-to Many Relationship:

Create a new Query that contains the tables from which you want to delete records or set criteria.
In Query Design View, click the arrow next to Query Type on the toolbar, and then click Delete Query.
From the table with related records (on the "many" side of the one-to-many relationship), drag the Asterisk (*) from the field list to the Query Design Grid.
From appears in the delete cell under this field.
From the Primary Table (on the "one" side of the one-to-many relationship), drag to the Design Grid the fields on which you want to set criteria.
- Where appears in the delete cell under this field.
- In the Criteria Cell for the fields that you have dragged to the Grid, type the criteria.
To preview the records that will be deleted, click View on the toolbar.
To return to Query Design View, click View on the toolbar again.
Make any changes you want in Design View.
Click Run on the toolbar to delete the records from the table on the "many" side.
Select the field list for each table on the "many" side and press delete to remove it from the query.
With just the primary table remaining in the query and the field on which you want to set criteria in the Design Grid, click Run again. Access deletes the specified records from the "one" table.
Note
- To stop a query after you start it, press CTRL+BREAK.

Expressions:
Expressions That use Text Values as Criteria:

Expression Descriptions:

8/5/99
- Returns the exact number or in this case date
<8/5/99
- Returns all number or in this case dates before
>8/5/99
- Returns all numbers or in this case dates after
<=8/5/99
- Returns all numbers or in this case dates on or after
>=8/5/99
- Returns all numbers or in this case dates on or before
Not <8/5/99
- Returns all numbers or in this case dates not before
Not >8/5/99
- Returns all numbers or in this case dates not after
"London"
- Displays all entries, which have London.
"London" Or "Hedge End"
- Uses the Or operator to display all entries that have London or Hedge End.
Between #1/5/95# And #1/10/95#
- The Between...And operator to show all entries no earlier than 5-Jan-95 and no later than 10-Jan-95.
#2/2/95#
- Displays all entries that have the date 2-Feb-95.
In("Canada", "UK")
- Uses the In operator to display all entries that include Canada or the UK.
Not "USA"
- Uses the Not operator to all entries that do not have USA.
Like "S*"
- Entries that start with the letter S.
Like "*Imports"
- Entries whose names end with the word "Imports".
Like "[A-D]*"
- Entries that start with A through D.
>="N"
- Displays all entries that start with the letters N through Z.
Is Null
- Displays all entries where that field is Null (blank).
Is Not Null
- Displays all entries where the field contains a value.
Like "*ar*"
- Entries that include the letter sequence "AR".
Like "Rick Selb?"
- Entries with "Rick" as the first part of its name and a 5-letter second name in which the first 4 letters are "Selb" and the last letter is unknown.

Filter:
Filter Records by entering Criteria in place in a Form or Datasheet:

Open a table, query, or form in Datasheet View, or a form in Form View.
- To filter records in a subdatasheet, first click the Expand Indicator to display that subdatasheet.
Do one of the following:
- To specify the exact field value you want the filtered records to contain, right-click in the field in the datasheet, subdatasheet, form, or subform you're filtering, and then in the Filter For Box on the Shortcut Menu, type the Value.
- Example
  - To find all the records that have London in the City field, right-click in the City field, and then type London in the Filter For box.
- To specify more complex criteria, type the complete expression using the appropriate combination of identifiers, operators, wildcard characters, and values to produce the result you want.
- Example
  - To find all the records where the Contact Title field includes the word "Marketing" anywhere in the title, right-click in the Contact Title field, and then type *Marketing* in the Filter For Box.
  - To display only the records for orders that were shipped more than 15 days after the specified required date, type [ShippedDate]-[RequiredDate]>15. (In this last example, you don't have to right-click a specific field to enter the expression. However, in a datasheet with subdatasheets, first click in the sheet you want to filter.)
Do one of the following:
- To apply the filter and close the Shortcut Menu, press ENTER.
- To apply the filter and keep the Shortcut Menu displayed (so that you can specify additional criteria for the field), press Tab.
- Enter new criteria, and then press Tab again.
- Repeat until you have just the records you want.
Notes
- When you save a table or form, Access saves the filters you created. You can re apply the filters when you need them, the next time you open the table or form.
- When you save a query, Access saves the filters you created, but it DOES NOT add the Filter Criteria to the Query Design Grid. You can Re apply the filters after you run the query, the next time you open it.

Filter Records by entering Values in a Blank View of your Form or Datasheet:

Open a table, query, or form in Datasheet View or open a form in Form View.
To filter records in a subdatasheet, display the subdatasheet by clicking its Expand Indicator.
Click Filter By Form on the toolbar to switch to the Filter By Form Window.
You can specify criteria for the form, subform, main datasheet, or any subdatasheet that's displayed. Each subdatasheet or subform has its own Look For and Or tabs.
Click the field in which you want to specify the criteria that records must meet to be included in the filtered set of records.
Enter your criteria by selecting the value you're searching for from the list in the field (if the list includes field values), or by typing the value into the field.
To find records in which a Check Box, Toggle Button, or Option Button is or is not selected, click the Check Box or Button until it is the way you want.
To return it to a Neutral Position so that it won't be used as criteria for filtering records, continue clicking the Check Box or Button until it is Grayed.
To find records in which a particular field is empty or not empty, type Is Null or Is Not Null into the field. (You can select these options from the list in fields with a Memo, OLE Object, or Hyperlink Data Type, and in Calculated Fields in queries.)
To find records using a Criteria Expression, type the expression into the appropriate field or enter one using the Expression Builder.
- If you specify values in more than one field, the filter returns records only if they contain the same values you specified in each of those fields.
To specify Alternative Values that records can have to be included in the filter's results, click the Or Tab for the datasheet, subdatasheet, form, or subform you're filtering, and enter more criteria.
The filter returns records if they have all the values specified on the Look For tab or all the values specified on the first Or Tab or all the values specified on the second Or Tab, and so on.
Click Apply Filter on the toolbar.
Notes
- When you save a table or form, Access saves the filters you created. You can re apply the filters when you need them, the next time you open the table or form.
- If you created a filter on a subdatasheet or subform, this filter is also available when you open the table or form for the subdatasheet or subform independently.
- When you save a query, Access saves the filters you created, but it DOES NOT add the filter criteria to the Query Design Grid. You can re apply the filters after you run the query, the next time you open it.

Filter Records by Selecting Values in a Form or Datasheet:

In a field on a form, subform, datasheet, or subdatasheet, find one instance of the value you want records to contain in order to be included in the filter's results.
Select the value, and then click Filter By Selection on the toolbar.
- How you select the value determines what records the filter returns.
Repeat step 2 until you have the set of records you want.
Notes
- When you save a table or form, Access saves the filters you created. You can re apply the filters when you need them, the next time you open the table or form.
- When you save a query, Access saves the filters you created, but it DOES NOT add the filter criteria to the Query Design Grid. You can re apply the filters after you run the query, the next time you open it.
- If you created a filter on a subdatasheet or subform, this filter is also available when you open the table or form for the subdatasheet or subform independently.
- You can also filter for records that do not have a certain value. After selecting a value, right-click it, and then click Filter Excluding Selection.

Filter Records by using the Advance Filter/Sort Window:

Open a table, query, or form in datasheet view or open a form in Form View.
Click in the datasheet, subdatasheet, form, or subform you want to filter.
On the Records Menu, point to Filter, and then click Advanced Filter/Sort.
Add to the Design Grid the field or fields you'll need to specify the values or other criteria the filter will use to find records.
To specify a Sort Order, click in the Sort Cell for a field, click the arrow, and select a sort order.
Note
- Access first sorts the left most field in the Design Grid, and then it sorts the next field to the right, and so on.
In the Criteria Cell for the fields you have included, enter the value you're looking for or enter an expression.
Apply the filter by clicking Apply Filter on the toolbar.
Notes
- When you save a table or form, Access saves the filters. You can re apply the filters when you need them, the next time you open the table or form.
- When you save a query, Access saves the filters you created, but it DOES NOT add the filter criteria to the Query Design Grid. You can re apply the filters after you run the query, the next time you open it.
- If you created a filter on a subdatasheet or subform, this filter is also available when you open the table or form for the subdatasheet or subform independently.
- Instead of creating a filter from scratch, you can use an existing query as a filter.

Find:
Find Unmatched Records between Tables using a Query Wizard:

Note
- Using the Find Unmatched Query Wizard, you can find records in one table that don't have related records in another table.
Example
- You can find customers who don't have orders.
In the Database Window, click Queries under Objects, and then click New on the Database Window Toolbar.
In the New Query Dialog Box, click Find Unmatched Query Wizard.
Click OK.
Follow the directions in the Wizard Dialog Boxes. In the last Dialog Box, you can choose to run the query or see the Query's Structure in Design View.
Note
- If the resulting query isn't exactly what you want, you can re run the wizard or change the query in Design View.

Insert:
Insert or Delete a Criteria Row in a Query or Advanced Filter:

To insert a criteria row, in Query Design View or in the Advanced Filter/Sort Window, click in the row that is below where you want the new row to appear, and then click Insert Row on the Insert Menu.
A new row is inserted above the Row that you clicked.
To delete a criteria row, click anywhere in the row, and then click Delete Rows on the Edit Menu.

Joins:
Join Multiple Tables and Queries in a Query:

In Query Design View, drag a field from the Field List for one table or query to the equivalent field (a field of the same or compatible data type containing similar data) in the Field List for the other table or query.
- With this type of Join, Access selects records from both tables or queries only when the values in the joined fields are equal.
Notes
- Join numeric fields ONLY if the field size property settings for both are byte, integer, or long integer.
- You can change the type of Join so that Access selects all the records from one table or query whether or not it has matching records in the other table or query.

Change the Type of Join between Tables and Queries in a Query:

Open a query in Design View.
Double-click the Join Line between the Field Lists for the tables or queries.
In the Join Properties Dialog Box, click the Join Option you want.
Click OK.
Notes
- From the Relationships Window, you can change the default join type for tables. All of the joins created using the Join Properties Dialog Box select records only if the values in the joined fields are equal.
- You can create joins that select records only if the value in one join field is greater than, less than, not equal to, greater than or equal to, or less than or equal to the value in the other join field.

Multiple functions:
Apply Text and Graphic Formatting Multiple Times:

Select the item whose format you would like to copy.
To copy the selected format to several items, DOUBLE-CLICK the Format Painter Button. The button stays selected, or "Sticky."
Select the text or graphic where you want to apply the new format.
When you're finished applying the format, click the Format Painter Button again or press ESC.
Note
- Applying text and graphics formatting multiple times does not work between programs.
- Example
  - You cannot apply formatting from Word to Power Point.

Insert a Drawing Object Multiple Times:

To view the Drawing Toolbar, right-click any toolbar and select Drawing.
To insert a drawing object several times, DOUBLE-CLICK the Drawing Object Button (such as Rectangle) on the Drawing Toolbar. The button stays selected or "Sticky."
Insert your drawing object several times by clicking in the appropriate locations.
When you're finished inserting the objects, click the Drawing Object Button again, or press ESC.
Note
- You can also turn any of the drawing objects available from the Auto Shapes Button on the Drawing Toolbar into "Sticky" Drawing Object Buttons.
- Simply click the Auto Shapes Button, point to a category (such as Flowchart), and a submenu of shapes opens up.
- Drag the move handle at the top of the submenu to create a Floating Toolbar.
- Then double-click the Drawing Object Button (such as Flowchart: Process).
- The button now stays "Sticky."

Moving Text, Graphics and Formatting Multiple Times:

Select the item whose format you would like to copy.
To copy the selected format to several items, DOUBLE-CLICK the Format Painter Button. The button stays selected, or "Sticky."
Select the text or graphic where you want to apply the new format.
When you're finished applying the format, click the Format Painter Button again or press ESC.
Note
- Applying text and graphics formatting multiple times does not work between programs.
- Example
  - You cannot apply formatting from Word to Power Point.

Move Text and Graphics with the "Spike":

Select an Item you want to move, and then press CTRL+F3.
Repeat this step until all the items are on the Spike.
Click a place in the current document, or in a new document, where you want to paste the items.
Do one of the following:
- If you want to paste the items once and empty the Spike, press CTRL+SHIFT+F3.
- If you want to paste the items multiple times, type "Spike", and then press F3.

Properties:
Set Properties for a Query, its Fields, or its Field Lists:

Note
- You can set properties for queries, Field Lists in a query, and fields you add to the Design Grid in a query.
Open a query in Design View.
- To select a field, click the cell in the field row.
- To select a field list, click anywhere in the list.
- To select the whole query, click anywhere in Query Design View outside the Design Grid and the field lists.
Click properties on the toolbar to display the Property Sheet for the selected object.
In the Property Sheet, click the property you want to set, and then do one of the following:
- If an arrow appears in the Property Box, click the arrow and then click a value from the list.
- Type a setting or expression in the Property Box.
- If the Build Button appears next to the Property Box, click it to display a builder.
- If you need more space to enter or edit a property setting, press SHIFT+F2 to open the Zoom Box.
- Note
  - To get more information on a property and its settings, click the property in the Property Sheet, and then press F1.

Sort:
Sort Records by using the Design Grid of a Query or Advanced Filter:

Note
- Before you specify a Sort Order, there are certain things you should consider.
Open a query in Design View or display the Advanced Filter/Sort Window for a table, query, or form.
To sort on more than one field, first arrange the fields in the Design Grid in the order you want the sorts performed.
- Access sorts on the left most field first, then on the next field to the right, and so on.
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.
To see the results of the query, click view on the toolbar.

DISCLAIMER: The information provided on ALL of these pages is provided as-is. No warranty is provided or implied. Use at your own risk.
All content on these pages has been gathered and compiled by Rick Selby. Privacy Policy