Define relationships between tables

When you create a relationship between tables, the related fields don't have to have the same names. However, related fields must have the same data type unless the primary key field is an AutoNumber field. You can match an AutoNumber field with a Number field only if the FieldSize property of both of the matching fields is the same. For example, you can match an AutoNumber field and a Number field if the FieldSize property of both fields is Long Integer. Even when both matching fields are Number fields, they must have the same FieldSize property setting.

Define a one-to-many or a one-to-one relationship

1. Close any tables you have open. You can't create or modify relationships between open tables.
2. Press F11 to switch to the Database window.
3. Click Relationships on the toolbar.
4. If you haven't yet defined any relationships in your database, the Show Table dialog box is automatically displayed.

If you need to add the tables you want to relate and the Show Table dialog box isn't displayed, click Show Table on the toolbar.

5.       Double-click the names of the tables you want to relate, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table twice.

6. Drag the field that you want to relate from one table to the related field in the other table.

To drag multiple fields, press the CTRL key, click each field, and then drag them.

In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key in the other table.

7. The Edit Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary.

Set the relationship options if necessary. For information about a specific item in the Edit Relationships dialog box, click the question mark button, and then click the item.

8. Click the Create button to create the relationship.
9. Repeat steps 5 through 8 for each pair of tables you want to relate.

When you close the Relationships window, Microsoft Access asks if you want to save the layout. Whether you save the layout or not, the relationships you create are saved in the database.

Note   You can create relationships using queries as well as tables. However, referential integrity isn't enforced with queries.

Define a many-to-many relationship

1. Create the two tables that will have a many-to-many relationship.
2. Create a third table, called a junction table, and add to the junction table new fields with the same definitions as the primary key fields from each of the other two tables. In the junction table, the primary key fields function as foreign keys. You can add other fields to the junction table, just as you can to any other table.
3. In the junction table, set the primary key to include the primary key fields from the other two tables. For example, in an Order Details junction table, the primary key would be made up of the OrderID and ProductID fields.

How?

1.       Open a table in Design view.

2. Select the field or fields you want to define as the primary key.

To select one field, click the row selector for the desired field.

To select multiple fields, hold down the CTRL key and then click the row selector for each field.

3. Click Primary Key on the toolbar.

Note   If you want the order of the fields in a multiple-field primary key to be different from the order of those fields in the table, click Indexes on the toolbar to display the Indexes window, and then reorder the field names for the index named PrimaryKey.

4. Define a one-to-many relationship between each of the two primary tables and the junction table.

View existing relationships

1. Press F11 to switch to the Database window.
2. Click Relationships on the toolbar.
3. Do one of the following:

View all the relationships defined in the database

• Click Show All Relationships on the toolbar.

View the relationships defined for a particular table

o        Click the table, and then click Show Direct Relationships on the toolbar.

View only the relationships defined for one table without viewing the relationships defined for other tables

1.       Click Clear Layout on the toolbar to remove all tables from the Relationships window (this won't delete the tables or relationships; it just removes them from the Relationships window).

2.       To add the table back, click Show Table on the toolbar, double-click the table, and then click Close.

3.       Click Show Direct Relationships on the toolbar.

Edit an existing relationship

1. Close any tables you have open. You can't modify relationships between open tables.
2. Press F11 to switch to the Database window.
3. Click Relationships on the toolbar.
4. If the tables whose relationship you want to edit aren't displayed, click Show Table on the toolbar and double-click each table you want to add.
5. Double-click the relationship line for the relationship you want to edit.
6. Set the relationship options. For information about a specific item in the Edit Relationships dialog box, click the question mark button, and then click the item.

Define or edit a relationship between record sources in a data access page

If you add fields to the data access page and there isn't an existing relationship between the tables or queries they came from, Microsoft Access displays the Relationship Wizard. Relationships you define using the wizard apply only to the current data access page. They are not added to the database; therefore, they won't appear in the Relationships window in a Microsoft Access database or the database diagram window in a Microsoft Access project.

Define a relationship

1. In the Relationship Wizard dialog box, indicate which field you want to use to relate the record source to the data access page and the record sources that are currently part of the data access page's data definition.
2. Select the option that indicates the type of relationship you want between the two record sources.

Edit an existing relationship

1. If the data outline window is not displayed, click Data Outline on the View menu.
2. In the Data Outline window, locate the tables or queries whose relationship you want to edit. Look for entries with the page row source icon to their left.
3. Right-click the nested entry, and then click Relationships.
4. In the Relationship Wizard dialog box, indicate the fields that you want to use to relate the two objects.
5. Select the option that indicates the type of relationship you want between the two record sources.

Note   If a relationship type is unavailable at the bottom of the wizard dialog box, it means Microsoft Access determined that only one type of relationship is possible between the two record sources.

Remove a table from the Relationships window

• Click the table you want to remove, and then press the DELETE key.

This action affects only the display of the Relationships window. The table and relationships remain in the database.

Set or change the join type

Define the default join type for a relationship between two tables

Defining the join type for a relationship in the Relationships window doesn't affect the relationship itself; it sets the kind of join that will be used by default when creating queries based on the related tables in a Microsoft Access database. You can always override the default join type later when defining a query.

1. Close both of the related tables.
2. Press F11 to switch to the Database window.
3. Click Relationships on the toolbar to open the Relationships window.
4. Double-click the middle section of a join line between two tables to open the Relationships dialog box.
5. Click the Join Type button, and then click the desired join type.

Option 1 defines an inner join. This is the default.

Option 2 defines a left outer join.

Option 3 defines a right outer join.

Note   The Join Type button won't be enabled if the tables are linked tables. If the linked tables are in Microsoft Access format, you can open the database in which they are stored to set the join type.

Change the join type in a query

1. Open a query in Design view.
2. Double-click the join line between the field lists for the tables or queries.

·         Join line

3. In the Join Properties dialog box, click the join option you want, and then click OK.

Note   From the Relationships window, you can change the default join type for tables.

About designing a query

When you open a query in Design view, or open a form, report, or datasheet and show the Advanced Filter/Sort window, you see the design grid, which you can use to make a variety of changes to get the query results you want.

About types of queries

You use queries to view, change, and analyze data in different ways. You can also use them as a source of records for forms, reports, and data access pages. There are several types of queries in Microsoft Access.

Select queries

A select query is the most common type of query. It retrieves data from one or more tables and displays the results in a datasheet where you can update the records (with some restrictions). You can also use a select query to group records and calculate sums, counts, averages, and other types of totals.

Parameter queries

A parameter query is a query that when run displays its own dialog box prompting you for information, such as criteria for retrieving records or a value you want to insert in a field. You can design the query to prompt you for more than one piece of information; for example, you can design it to prompt you for two dates. Access can then retrieve all records that fall between those two dates.

Parameter queries are also handy when used as the basis for forms, reports, and data access pages. For example, you can create a monthly earnings report based on a parameter query. When you print the report, Access displays a dialog box asking for the month that you want the report to cover. You enter a month and Access prints the appropriate report.

Crosstab queries

You use crosstab queries to calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information — one down the left side of the datasheet and another across the top.

Action queries

An action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries: 

• Delete Queries   A delete query deletes a group of records from one or more tables. For example, you could use a delete query to remove products that are discontinued or for which there are no orders. With delete queries, you always delete entire records, not just selected fields within records.

·         Update Queries   An update query makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.

·         Append Queries   An append query adds a group of records from one or more tables to the end of one or more tables. For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information into your own database, you'd like to append it to your Customers table.

• Make-Table Queries   A make-table query creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for creating a table to export to other Microsoft Access databases or a history table that contains old records.

SQL queries

An SQL query is a query you create by using an SQL statement. You can use Structured Query Language (SQL) to query, update, and manage relational databases such as Access.

When you create a query in query Design view, Access constructs the equivalent SQL statements behind the scenes for you. In fact, most query properties in the property sheet in query Design view have equivalent clauses and options available in SQL view. If you want, you can view or edit the SQL statement in SQL view. However, after you make changes to a query in SQL view, the query might not be displayed the way it was previously in Design view.

Some SQL queries, called SQL-specific queries, can't be created in the design grid. For pass-through, data-definition, and union queries, you must create the SQL statements directly in SQL view. For subqueries, you enter the SQL in the Field row or the Criteria row of the query design grid.

Make a query to define calculated fields(Select Query)

A select query is a stored questions about the data stored in your database’s table and select queries are the foundation of much of what you do in Access.

·         summarize data, supply the result of calculations, or cross tabulate your data.

·         Underlie most of your forms, reports and allowing you to view data reports.

A. Make query by design view

1.       Select the Query tab of the database window then click New button.

2.       Select the Design View option then click on OK button.

3.       Choose name of the table from the list of Tables/Queries/Both tab of the show table window then click Add button and click on Close button to close the window.

4.       Drag the name of Fields of the selected table to the grid lines of the query design screen.

5.       Save structure of the query then Run the query.

A.      Make a query by Simple Query Design

1.       Select the Query tab of the database window then click New button.

2.       Select the Simple Query Wizard option then click on OK button.

3.       Choose the name of table or query from drop down list button.

4.       Select the name of field and then click on > button to insert selected field to list or click on >> button to insert all listed fields to selections. If you want to delete unwanted fields of the list then click on < to remove only one selected field or click on << to remove all listed fields from list.

5.       Repeat above steps 3 & 4 to insert all necessary fields to the list then click on Next button.

6.       Choose Modify the query design to design query by manually or select Open the query to view information option to the screen then click on Finish button.

B.      Make a Cross tab query.

A cross tab query summarized query results by displaying one field in a table down the left side of the data sheet and additional facts across the top of the datasheet. A cross tab query can summarize the number of orders placed each month by sales persons.

1.       Select the Query tab of the database window then click New button.

2.       Select the Cross Tab Query Wizard option then click on OK button.

3.       Select Tables, Queries, or Both option button then select required name of table or query then click Next button.

4.       Select the name of field from list then click on > button to insert as Row headings. You may allow selecting only three fields. Then click on Next button.

5.       Click on required name of field for column heading then click Next button.

6.       Select the function to calculate the cross tabulation report then click on Next button.

7.       Type the name of query on text box then selects the View the Query or Modify the Design option then click on Finish Button.

8.       If selection is View then you get the summary report of the cross tab query or if selection is design then you get query girds.

9.       Save query the run.

Add or remove tables, queries, and fields

You can add a table or query if the data you need isn't in the query, or remove a table or query if you decide you don't need them. Once you add the tables or queries you need, you can then add the fields that you want to work with to the design grid, or remove them if you decide you don't need them.

A join line between field lists tells Microsoft Access how the data in one table is related to the data in the other.

You drag a field from the field list to a column in the design grid to show the field in the query results.

Calculate amounts

You can add the values in a field or do other computations with the data by specifying the type of calculation to perform. Use an aggregate function, such as Sum or Avg, to calculate one amount for all the records in each field in the design grid. Use Group By to calculate separate amounts for groups of records in a field. Limit results by using criteria You can limit the records that you see in the query's results or the records that are included in a calculation by specifying criteria. To limit the records in the query's results, enter criteria in one or more fields. Use the Or row for alternative criteria in the same field. Enter criteria for different fields. For example, for orders between 6/1/01 and 6/15/01 calculate total order amounts, but display only those that are more than $100,000

Make Calculated field on Query.

1.       Click on Query tab of the database window and select the name of query then click over Design button.

2.       Click at blank grid box of the query design screen then click on Build tool (        ) or Right click on field grid button and select Build option.

3.       Type name of the field on Expression builder then type colon ( : ) to separate the field name with expression(formula). If you want to insert the fieldname of formula then double click on listed field name.

4.       After completion of expression then click OK to return

5.       Save the query then run to view calculated output on field.

 .

Sort records

You can sort the query's results by specifying a sort order in the design grid.

If you specify a sort order for more than one field, Microsoft Access sorts the leftmost field first, so you should arrange the fields you want to sort from left to right in the design grid.

Sort by ascending or descending order, or remove a sort.

About action queries

An action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries: delete, update, append, and make-table.

Delete query

A delete query deletes a group of records from one or more tables. For example, you could use a delete query to remove products that are discontinued or for which there are no orders. With delete queries, you always delete entire records, not just selected fields within records.

You can use a delete query to delete records from a single table, from multiple tables in a one-to-one relationship, or from multiple tables in a one-to-many relationship, if cascading deletes are enabled. For example, you could use a delete query to delete all customers from Ireland and all their orders. However, if you need to include the "many" table along with the "one" table in order to add criteria, you must run the query twice, since a query cannot delete records from the primary table and the related tables at the same time.

You can distinguish a one-to-many relationship in query Design view by looking at the joins between tables. If one end of a join is marked with the infinity symbol, it is a one-to-many relationship. If both ends are marked with a 1, it's a one-to-one relationship.

Important considerations when using a delete query

• Once you delete records using a delete query, you can't undo the operation. Therefore, you should preview the data that the query selected for deletion before you run the query. You can do this by clicking View on the toolbar, and viewing the query in Datasheet view.
• You should maintain backup copies of your data at all times. If you delete the wrong records, you can retrieve them from your backup copies.
• In some cases, running a delete query might delete records in related tables, even if they're not included in the query. This can happen when your query contains only the table that's on the "one" side of a one-to-many relationship, and you've enabled cascading deletes for that relationship. When you delete records from the "one" table, you'll also delete records from the "many" table.
• When a delete query contains more than one table, such as a query that deletes duplicate records from one of the tables, the query’s UniqueRecords property must be set to Yes.

1.       Choose the Query tab of the database window and select the name of query where you want then choose the Design.

2.       Click on Query menu and select the Delete Query.

3.       Click on Criteria grid of the desired field, where you want to define condition to delete data record of the table.

4.       Save query and click on Run button.

5.       Click Yes button to complete the delete action of the query.

Update query

An update query makes global changes to a group of records in one or more tables. For example, you can raise prices by 15 percent for all DOS marks in below example as well as in other examples you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.

1. Choose the Query tab of the database window and select the name of query where you want then choose the Design.
2. Click on Query menu and select the Update Query.
3. In Update To grid of the desired field type expression to be updated.

4. In Criteria grid of another fieldname type to the support the update criteria.
5. Save the query then click on Run button.

6. Click Yes to complete the update action of the query.

Append query

An append query adds a group of records from one or more tables to the end of one or more tables. For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information in, you'd like to append it to your Customers table. Append queries are also helpful for:

• Appending fields based on criteria. For example, you might want to append only the names and addresses of customers with outstanding orders.
• Appending records when some of the fields in one table don't exist in the other table. For example, in the Northwind sample database, the Customers table has 11 fields. Suppose that you want to append records from another table that has fields that match 9 of the 11 fields in the Customers table. An append query will append the data in the matching fields and ignore the others.

1.       Choose the Query tab of the database window and select the name of query where you want then choose the Design.

2.       Click on Query menu and select the Append Query.

3.       Select the name of the table where you want to insert (Append) data records from Table Name drop down list button, then click OK button.

4.       if field names of the both table are similar than matching field automatically appears in the same grid of the field name, other wise you should specify the field name of the related field of the  both tables.

5.       Save query then Run the query.

6.       Click Yes twice to complete the given task.

Make-table query

A make-table query creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for:

• Creating a table to export to other Microsoft Access databases. For example, you might want to create a table that contains several fields from your Employees table, and then export that table to a database used by your personnel department.
• Creating forms, reports, or data access pages that display data from a specified point in time. For example, suppose you want to display a data access page on 15-May-96 that displays the first quarter's sales totals based on the data that was in the underlying tables as of 9:00 A.M. on 1-Apr-96. A data access page based on a query or SQL statement extracts the most up-to-date data from the tables (the data as of 15-May-96), rather than the records as of a specific date and time. To preserve the data exactly as it was at 9:00 A.M. on 1-Apr-96, create a make-table query at that point in time to retrieve the records you need and store them in a new table. Then use this table, rather than a query, as the basis for the data access page.

• Making a backup copy of a table automatically by using a macro or code.
• Creating a history table that contains old records. For example, you could create a table that stores all your old orders before deleting them from your current Orders table.
• Improving performance of forms, reports, and data access pages based on multiple-table queries or SQL statements. For example, suppose you want to print multiple reports that are based on a five-table query that includes totals. You might be able to speed things up by first creating a make-table query that retrieves the records you need and stores them in one table. Then you can base the reports on this table or specify the table in an SQL statement as the record source for a form, report, or page, so you don't have to rerun the query each time you open the form, report, or page. However, the data in the table is frozen at the time you run the make-table query.

1. Choose the Query tab of the database window and select the name of query where you want then choose the Design.
2. Click on Query menu and select the Make Table Query.
3. Type the name of table on Table name drop down list box and click on OK button.
4. Type criteria on desired fiend name grid then save query and click Run button to view action.
5. Click Yes to complete the action of the make table query process.

Sample of the Action queries with their icons.

Create a parameter query

1.       Click on Query tab of the database window and select existing query or make a select query by the help of design view.

2.       Click on Query menu and choose Parameter Query.

3.       Type the parameter and select the data type from list then click OK

Or   click on field name grid of the query grid then type parameter on large bracket or on quotation mark.

4.       Save query then run the query, click Yes if selected query is action query.

5.       Type parameter value on active window then click OK.

6.       Close and return to database window after the viewing records.

1. Create a select or crosstab query.
2. In query Design view, drag the fields from the field list to the query design grid.
3. Do one or more of the following:

Use one parameter

In the Criteria cell for the field you want to use as a parameter, type an expression with a prompt enclosed in square brackets. For example, in a field that displays the current number of units in stock, enter the following:

<[Number of Units in Stock:]

Use two or more parameters

In the Criteria cell for each field you want to use as a parameter, type an expression with prompts enclosed in square brackets.

For example, in 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:

Between [Type the beginning date:] And [Type the ending date:]

Use parameters with wildcards

In the Criteria cell for each field you want to use as a parameter, type an expression with a prompt enclosed in square brackets. 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 (*).

For example, the following statement searches for words that begin with a specified letter:

LIKE [Enter the first character to search by: ] & "*"

The following  statement searches for words that contain the specified character:

LIKE "*" & [Enter any character to search by: ] & "*"

4.       Specify a data type for the parameters if your parameter query:

• Is a crosstab query or the underlying query for a crosstab query. (In these cases, you must also set the ColumnHeadings property in the crosstab query.)
• Is the underlying query for a chart.  Prompts for a field with the Yes/No data type.
• Prompts for fields that come from a table in an external SQL database.

4.                   On the Query menu, click Parameters.

5.                   In the first Parameter cell, type the first prompt you entered in the query design grid.

6.                   In the Data Type cell to the right, click the appropriate data type according to the following guidelines:

7.                   Repeat Steps 2 and 3 for each parameter you want to specify a data type for.

To view the results, click View on the toolbar, and then type a value for the parameter.

Create a custom dialog box to prompt for your parameter query's criteria

1. Create a custom dialog box form.

Before you do the following procedure, create the form or report you want.

1.       Open the form or report in Design view.

2.       Double-click the form selector or report selector to open the property sheet.

3.       In the Pop Up property box, click Yes.  Do one of the following:

For a modeless pop-up form or report

1.       If you want to be able to size the form or report, in the BorderStyle property box, click Thin.

2.       Create a macro or an event procedure to open the pop-up form or report.

Note   The Suppliers form in Northwind sample application has an example of an event procedure that displays a modeless pop-up form. Open the Northwind database in the Samples subfolder of your Office folder, and then open the Suppliers form in Design view. Click the Build button next to the OnClick property box of the Review Products button.

For a modal pop-up form or report

3.       In the Modal property box, click Yes.  In the BorderStyle property box, click Dialog.

4.       Create a macro or an event procedure to open the pop-up form or report.

4.       Attach the macro or an event procedure to a form or report by specifying the macro name or event procedure as the setting for the appropriate event property. For example, type the name of the macro or event procedure in the OnClick property box of a command button.

To position a pop-up form or report on the screen, open it in Design view, maximize the Microsoft Access window, position the window where you want it, and then save the form or report. The pop-up window will appear in this location when the form or report is opened (assuming the AutoCenter property is set to No and you haven't specified its position in the event procedure that opens the pop-up object).

Note   If you want to use a form as a normal form as well as a dialog box, you can use the Dialog setting of the OpenForm action to open the form temporarily as a dialog box instead of using this procedure.

2. Create or open the parameter query that will collect its criteria values from the dialog box.
3. Add to the query design grid the fields associated with each of the dialog box controls where you'll enter criteria values.  For each of these fields, enter an expression in the Criteria cell that tells Microsoft Access to refer to the control on the dialog box for the criteria values. 
4. Add to the query design grid the fields whose values you want returned.

Customer Information Example

For example, you could create a dialog box form named Customer Info with a control for the CustomerID field. By entering a customer ID in the dialog box, you could have the query return the correct customer's company name, address, and city. To create this query, you'd do the following:

1. Add the CustomerID field to the query design grid. 
2. In its Criteria cell for the CustomerID field, enter the expression that tells Access to refer to the Customer ID control in the dialog box for the criteria values the query uses. In this case, you'd enter the expression Forms![Customer Info]![CustomerID].
3. Add to the query design grid the fields whose values you want returned based on the CustomerID value: CompanyName, Address, and City fields.

Set multiuser query options (Specify a record-locking strategy)

You can lock all records in the underlying table or only the record that you're editing. Either way, other users can't change the record in the underlying table while you are editing it in the query's results.

1. Open a query in Design view. In a multiuser environment, confirm that all users have closed the query, the underlying table or query, and other objects based on the query.
2. Select the query by clicking anywhere in query Design view outside the design grid and the field lists.
3. Click Properties on the toolbar to display the query's property sheet.
4. Click the RecordLocks property setting you want to use.

Specify how long before a query displays an ODBC time-out error

A query that uses an ODBC database as its underlying source of data might encounter delays due to network traffic or heavy use of the ODBC server.

1. Open a query in Design view.  Select the query by clicking anywhere in query Design view outside the design grid and the field lists.
2. Click Properties on the toolbar to display the query's property sheet.
3. Set the ODBCTimeout property to the number of seconds that the query waits before displaying a time-out error. The default is 60 seconds.

Note   If you set the property to zero, no time-out error occurs.

About calculations in a query

There are many types of calculations you can perform in a query. For example, you can calculate the sum or average of the values in one field, multiply the values in two fields, or calculate the date three months from the current date. You perform calculations in a query using:

• Predefined calculations, called "totals," to compute the following amounts for groups of records or for all the records combined in the query: sum, average, count, minimum, maximum, standard deviation, or variance.
• A custom calculation to perform numeric, date, and text calculations on each record using data from one or more fields. You need to create a new calculated field directly in the design grid for these types of calculations.

When you display the results of a calculation in a field, the results aren't actually stored in the underlying table. Instead, Microsoft Access reruns the calculation each time you run the query so that the results are always based on the most current data in the database. Therefore, you can't manually update the calculated results. 

Predefined calculations that use aggregate functions

To display the results of a calculation in a field, you can use a predefined calculation that Access provides or custom calculations you define. Use the predefined calculations, called aggregate functions or "totals," if you want to compute the following amounts for all records or for groups of records: sum, average, count, minimum, maximum, standard deviation, or variance. You choose one totals calculation for each field you want to calculate.

1.      Calculations on all records

2.       Calculations on groups of records

You can calculate some types of totals using the Simple Query Wizard. Or, you can calculate all types of totals using the Total row in the query design grid, where you select the aggregate function for the calculation you want to perform on a field.

In the query design grid, you can also specify criteria to affect the calculations and produce different query results. By adding criteria, you can limit the:

·         Groups before performing calculations on those groups.

·         Results after calculations on the groups are performed.

·         Records before they are grouped and before calculations are performed.

About aggregate functions and other options in the Total row of the query design grid

Aggregate functions

The following options in the query design grid's Total row are aggregate functions:

First and Last functions

You use the First or Last functions to return the first or last record in the group you are performing calculations on. These functions return the first or last record as it was entered in chronological order. Sorting the records has no effect on these functions.

Group By, Expression, and Where options

The following table summarizes what each option does: 

Custom calculations and calculated fields

With a custom calculation, you can perform numeric, date, and text calculations on each record using data from one or more fields. For example, with a custom calculation, you can multiply one field's values by a set amount, find the difference between two dates stored in separate fields, combine several values in a Text field, or create subqueries. Using the options in the Total row in the design grid, you can perform the calculation on groups of records and calculate a sum, average, count, or other type of total on the calculated field.

For custom calculations, you need to create a new calculated field directly in the design grid. You create a calculated field by entering an expression into an empty Field cell in the query design grid.

The expression can be made up of multiple calculations as the following example shows:

Sum([UnitsInStock]+[UnitsOnOrder])

You can also specify criteria for a calculated field to affect the results of the calculation.

You don't have to display the results of a calculation in a field, however. Instead, you can use them as criteria to determine the records the query selects or to determine which records to perform an action on. For example, you can specify the following expression in the Criteria row to tell the query to return only records that have values in the RequiredDate field that are between today's date and three months from today's date.

Updating data with calculations

You can also use a calculation to update data from an update query. For example, you can enter the following expression in the Update To cell to increase all the values in the UnitPrice field by 5 percent.

Handling Null values in calculations

The aggregate functions won't include records containing blank (Null) values in their calculations. For example, the Count function returns a count of all the records without Null values. If you want to find the total number of records including those with Null values, use Count with the asterisk (*) wildcard character.

Count(*)

To count Null values when using the other aggregate functions, use the Nz function, which converts Null values to zeroes so they are included in a calculation.

FreightPercentage: Sum(Nz([Freight], 0)) / Sum(Nz([Subtotal]), 0) * 100

When you use an arithmetic operator (+, -, *, /) in an expression and the value of one of the fields in the expression is Null, the result of the entire expression will be Null. If some records in one of the fields you used in the expression might have a Null value, you can convert the Null value to zero using the Nz function as shown in the following example:

Nz([UnitsInStock], 0) + Nz([UnitsOnOrder], 0

Delete a record in a datasheet or form

1. Open a datasheet, or open a form in Form view. Click the record you want to delete.
2. Click Delete Record on the toolbar. 

Note   When you delete data, you might want to delete related data in other tables. For example, if you delete a supplier, you probably want to delete the products that the supplier supplies. In some cases, you can make sure the proper data is deleted by enforcing referential integrity and turning on cascade deletions.

Delete a record in a data access page

Important   Once you delete a record, you cannot undo the deletion from the database.

• On the record navigation toolbar, click Delete.

Note   If you don't see the Delete button, it means one of the following:

• The page does not support deletions.
• The designer of the page has customized the button. Look for custom instructions on how to use the page, or contact the designer.

If you want Microsoft Access to delete all the duplicates and keep the original records automatically, don't use the Find Duplicates Wizard or convert a Find Duplicates query to a delete query.

Delete duplicate records from a table

Deleting duplicate records from a table requires two steps. First, you create a copy of the structure of the table that contains duplicates, and then make primary keys of all the fields that contain duplicates. Second, you create and run an append query from the original table to the new table. Because fields that are primary keys can't contain duplicate records, this procedure produces a table without duplicate records.

1. Copy the table and make primary keys of fields with duplicates.
1. In the Database window, click Tables under Objects.
2. Click the name of the table you want to delete duplicate records from.
3. Click Copy on the toolbar.  Click Paste on the toolbar.
4. In the Paste Table As dialog box, type a name for the copied table, click Structure Only, and then click OK.
5. Open the new table in Design view, and select the fields that contained duplicates in the table you copied.
6. Click Primary Key on the toolbar to create a primary key based on the selected fields.
7. Save and close the table.
2. Append only unique records to the new table.
1. Create a new query based on the original table that contains duplicates.
2. In query Design view, click Query Type on the toolbar, and then click Append Query.
3. In the Append dialog box, click the name of the new table in the Table Name list, and then click OK.
4. Include all the fields from the original table by dragging the asterisk (*) to the query design grid.  Click Run on the toolbar.
5. Click Yes when you receive the message that you're about to append rows.
6. Click Yes when you receive the message that Microsoft Access can't append all the records in the append query. This transfers only unique records to your new table and discards the duplicates.
7. Open the table to see the results.
8. When you're sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.

The delete query deleted records from the wrong table.

Make sure the delete query includes:

• The table you want to delete records from. Drag the asterisk (*) from the field list for that table to the query design grid.
• The field(s) you want to specify criteria for. Drag the field you want from the table's field list to the design grid.

In the query design grid, From or Where is displayed in the Delete row. From is displayed in the Delete cell of the table you'll delete records from, and Where is displayed in the Delete cell of the field(s) you specify criteria for.

The query is running under a different ANSI SQL query mode.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

Why you should avoid mixing queries created under different ANSI SQL query modes

The two ANSI SQL query modes, ANSI-89 and ANSI-92, are not compatible. When you create a Microsoft Access database, you need to decide which query mode you are going to use, because mixing queries created in both query modes could produce runtime errors or unexpected results. The range of data types, reserved words, and wildcard characters are different in each query mode.

Example of a query using wildcard characters

A query that uses wildcard characters in a criteria expression can produce different results under each query mode. For example, if you run:

• An ANSI-89 SQL query in a database set to ANSI-92 query mode, such as:

SELECT * FROM Customers WHERE Country Like 'U*'

It returns all customers from a country/region named "U*", not all countries/regions beginning with the letter "U", because the asterisk (*) is not a wildcard character in ANSI-92 SQL.

• An ANSI-92 SQL query in a database set to ANSI-89 query mode, such as:

SELECT * FROM Customers WHERE Country Like 'U%'

It returns all customers from a country/region named "U%", not all countries/regions beginning with the letter "U", because the percent sign (%) is not a wildcard character in ANSI-89 SQL.

Example of a query with a duplicate field and alias name

If a query uses an alias that is the same as a base column name and you create a calculated field using the ambiguous name, the query will produce different results under each query mode. For example, if you run the following query:

SELECT Orders.OrderID AS Freight, Orders.Freight, [Freight]*1.1 AS [New Freight] FROM Orders;

Under ANSI-92 SQL, Access calculates the New Freight value using the Freight field, and therefore increases each Freight by 10%. Under ANSI-89 SQL, Access calculates the New Freight value using the Freight alias, and therefore increases each OrderID by 10%.

How to avoid problems caused by mixing queries under different ANSI SQL query modes in the same database

In general, avoid doing the following to prevent problems caused by mixing queries created under different ANSI SQL query modes:

• Converting an Access database set to ANSI-92 SQL query mode from 2002 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 file format.
• Changing the ANSI SQL query mode for the current database after you've created one or more queries. If you must do this, retest the existing queries to ensure that they still run or produce expected results, and rewrite the queries if necessary.
• Importing queries created under one ANSI SQL query mode into an Access database set to another mode, or exporting queries created under one ANSI SQL query mode to an Access database set to another mode.
• Changing the ANSI SQL query mode in an existing application. This could break your application and require rewriting your application.

In Access 2000, you can only programmatically change the ANSI SQL query mode and any queries created under ANSI-92 mode were hidden in the Database window. In Access 2002, you or a user can change ANSI SQL query mode through the user interface and ANSI-92 queries are no longer hidden in the Database window, so you should prevent accidental or intentional changes to the ANSI SQL query mode of your application by protecting your code and preventing the changing of the query mode through the application's user interface.

·         Using ambiguous aliases and column names. To avoid confusion, ensure that aliases and column names are always unique in an SQL statement.

About SQL queries

An SQL query is a query you create by using an SQL statement. You can use Structured Query Language (SQL) to query, update, and manage relational databases such as Microsoft Access.

When you create a query in query Design view, Access constructs the equivalent SQL statements behind the scenes for you. In fact, most query properties in the property sheet in query Design view have equivalent clauses and options available in SQL view. If you want, you can view or edit the SQL statement in SQL view. However, after you make changes to a query in SQL view, the query might not be displayed the way it was previously in Design view.

Some SQL queries, called SQL-specific queries, can't be created in the design grid. For pass-through, data-definition, and union queries, you must create the SQL statements directly in SQL view. For subqueries, you enter the SQL in the Field row or the Criteria row of the query design grid.

Where SQL statements are used

You can use SQL statements in many places in Access where you can enter the name of a table, query, or field. In some cases, Access fills in the SQL statement for you. For example, when you use a wizard to create a form or report that gets data from more than one table, Access automatically creates an SQL statement that it uses as the setting for the RecordSource property of the form or report. When you create a list box or combo box with a wizard, Access creates an SQL statement and uses it as the setting for the RowSource property of the list box or combo box. Without using a wizard, you can generate an SQL statement for the RecordSource or RowSource properties by clicking the Build button next to either of these properties, and then creating a query in query Design view.

You can also use SQL statements programmatically in:

·         The SQL Statement argument of the RunSQL macro action.

·         Code as a literal string, or as an SQL statement that includes variables and controls.

·         The SQL property of a QueryDef object to change the underlying SQL statement of a query.

Using expressions in SQL

You can type an expression in an SQL SELECT statement, or in WHERE, ORDER BY, GROUP BY, or HAVING clauses. You can also type an SQL expression in several arguments and property settings. For example, you can use an SQL expression as a:

·         Where Condition argument of the OpenForm or ApplyFilter action.

·         Domain or criteria argument in a domain aggregate function.

·         Setting for the RecordSource or RowSource property in forms and reports.

SQL-specific queries

Union query

A union query combines fields from two or more tables or queries into one field in the query's results. You might use a union query to combine data from two tables. For example, you might combine company name and city data for all companies in Brazil that are listed in the Suppliers and Customers tables. You could then create a make-table query based on the union query to make a new table.

1.       The first SELECT statement returns two fields ...

2.        the second SELECT statement returns two corresponding fields, and then.

3.       combines the values in the corresponding fields from both tables into one field.

Pass-through query

A pass-through query sends commands directly to ODBC databases, such as Microsoft FoxPro, using commands that are accepted by the server. For example, you can use a pass-through query to retrieve records or change data. With pass-through queries, you work directly with the tables on the server instead of linking to them. Pass-through queries are also useful for running stored procedures on an ODBC server.

Data-definition query

A data-definition query creates, deletes, or alters tables, or creates indexes in a database table. For example, the following data-definition query uses the CREATE TABLE statement to create a table named Friends. The statement includes the name and data type for each field in the table and assigns the FriendID field an index that marks it as the primary key.

CREATE TABLE Friends
([FriendID] integer,
[LastName] text,
[FirstName] text,
[Birthdate] date,
[Phone] text,
[Notes] memo,
CONSTRAINT [Index1] PRIMARY KEY ([FriendID]));

Subquery

A subquery consists of an SQL SELECT statement inside another select query or action query. You can enter these statements in the Field row of the query design grid to define a new field, or in the Criteria row to define criteria for a field. You can use subqueries to:

• Test for a result from the subquery (by using the EXISTS or NOT EXISTS reserved words).
• Find any values in the main query that are equal to, greater than, or less than values returned by the subquery (by using the ANY, IN, or ALL reserved words).
• Create subqueries within subqueries (nested subqueries).

About select and crosstab queries

Select and crosstab queries are the two main ways you query a Microsoft Access database to retrieve just the data you want.

Select queries

What is a select query?

A select query is the most common type of query. You use it to:

·         Retrieve data from one or more tables by using criteria you specify and then display the data in the order you want.

·         Update records in the datasheet of a select query (with some restrictions).

·         Group records and calculate sums, counts, averages, and other types of totals.

1.       Bring together data from multiple tables and sort it in a particular order.

2.       Perform calculations on groups of records.

Creating a select query

You create a query with a wizard or from scratch in query Design view. In Design view, you specify the data you want to work with by adding the tables or queries that contain the data, and then by filling in the design grid.

1.       Field lists show the fields in the tables or queries you add to your query.

2.       A join line tells Microsoft Access how data in one table or query is related to data in another table or query.

3.       You add fields to the design grid by dragging them to the field lists.

4.       The fields, sort order, and criteria you add to the design grid determine what you will see in the query's results.

What is a crosstab query?

You use crosstab queries to calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information — one down the left side of the datasheet and another across the top.

1.       This select query only groups the totals vertically by employee and category. This results in more records, making comparisons between different employees' totals more difficult.

2.       A crosstab query displays the same information, but groups it both horizontally and vertically so the datasheet is more compact and easier to analyze.

Creating a crosstab query

You create a crosstab query with a wizard or from scratch in query Design view. In the design grid, you specify which field's values will become column headings, which field's values will become row headings, and which field's values to sum, average, count, or otherwise calculate.

1.       The settings in these rows determine how data is shown.

2.       This setting displays the field's values as row headings.

3.       This setting displays the field's values as column headings.

4.       These settings display the total orders.

Let Microsoft Access create a select or crosstab query for you

Access can often create a query for you so you don't have to design one from scratch.

• To create a query to use as the basis of a form, report, or data access page, try the form, report, or data access page wizards. They create the form, report or data access page, and if it's based on more than one table, they also create its underlying SQL statement. If you want, you can save the SQL statement as a query.
• To easily create queries that you want to run independently or base multiple forms, reports, and data access pages on, try one of the query wizards. Query wizards do all the basic work for you after you provide answers to a series of questions. Even if you've created many queries, you may want to use a wizard to quickly design the query. Then you can switch to Design view to customize it.
• To create queries from filters you created using Filter By Form, Filter By Selection, or Filter For Input, save the filter as a query.

If none of these methods satisfies your needs, you can create the query from scratch in query Design view.

Create a filter

There are four methods you can use to filter records in a form or datasheet: Filter By Selection, Filter By Form, Filter For Input, and Advanced Filter/Sort. You can also filter records in a data access page.

Filter records by selecting values in a form or datasheet (Filter By Selection)

1. 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.
2. Select all or part of a value in a field by doing one of the following:

Note   How you select the value determines what records the filter returns.

Find records in which the entire contents of that field match the selection

o        Select the entire contents of a field or place the insertion point in a field without selecting anything.

For example, select the value "Berlin" in the City field to return all records with Berlin as the city.

Find records in which the value in that field starts with the same characters you selected.

o        Select part of a value starting with the first character in a field.

For example, Select only "Fran" in the CompanyName field with the value "France restauration" to return all records that have a company name starting with "Fran," such as "Franchi S.p.A." and "Frankenversand".

Find records in which all or any part of the value in that field contains the same characters you selected.

o        Select part of a value starting after the first character in a field.

Select the letters "Del " in the CompanyName field with the value "Old World Delicatessen"  to  return all records that have "del" anywhere in the CompanyName field, such as "Ernst Handel", "Galería del gastrónomo", and "Que Delícia".

3.       Click Filter By Selection on the toolbar.

4.       Repeat steps 2 and 3 until you have the set of records you want.

Note   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 entering values in a blank view of your form or datasheet (Filter By Form)

1. Open a form in Form view, or a table, query, or form in Datasheet view.

To filter records in a subdatasheet, display the subdatasheet by clicking its expand indicator.

2. 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 subform or subdatasheet has its own Look For and Or tabs.

3. Click the field in which you want to specify the criteria that records must meet to be included in the filtered set of records.
4. 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.

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's 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's grayed.

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.)

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.

5. To specify alternative values that records can have to be included in the filter's results, click the Or tab for the form, subform, datasheet, or subdatasheet 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.

6. Click Apply Filter on the toolbar.

Filter records by entering criteria in place in a form or datasheet (Filter For Input)

1. Open a form in Form view, or a table, query, or form in Datasheet view.

To filter records in a subdatasheet, first click the expand indicator to display that subdatasheet.

2. Do one of the following:

Specify the exact field value you want the filtered records to contain

• Right-click in the field in the form, subform, datasheet, or subdatasheet you're filtering, and then in the Filter For box on the shortcut menu, type the value.