Ways to get started if you're using a database for the first time

From Microsoft Access Help

Ways to get started if you're using a database for the first time
Working with databases and database objects can be a daunting task when you first get started. The following information should help you become more familiar with the components that make up a Microsoft Access database.

Databases: What they are and how they work


A database is a collection of information that's related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection. If your database isn't stored on a computer, or only parts of it are, you may be tracking information from a variety of sources that you're having to coordinate and organize yourself.

For example, suppose the phone numbers of your suppliers are stored in various locations: in a card file containing supplier phone numbers, in product information files in a file cabinet, and in a spreadsheet containing order information. If a supplier's phone number changes, you might have to update that information in all three places. In a database, however, you only have to update that information in one place — the supplier's phone number is automatically updated wherever you use it in the database.

Access database files

Using Microsoft Access, you can manage all your information from a single database file. Within the file, you can use:

Tables to store your data.
Queries to find and retrieve just the data you want.
Forms to view, add, and update data in tables.
Reports to analyze or print data in a specific layout.
Data access pages to view, update, or analyze the database's data from the Internet or an intranet.


Store data once in one table, but view it from multiple locations. When you update the data, it's automatically updated everywhere it appears.

Display data in a query

Display data in a form

Display data in a report

Display data in a data access page

Tables and relationships

To store your data, create one table for each type of information that you track. To bring the data from multiple tables together in a query, form, report, or data access page, define relationships between the tables.



Customer information that once existed in a mailing list now resides in the Customers table.

Order information that once existed in a spreadsheet now resides in the Orders table.

A unique ID, such as a Customer ID, distinguishes one record from another within a table. By adding one table's unique ID field to another table and defining a relationship, Microsoft Access can match related records from both tables so that you can bring them together in a form, report, or query.

Queries

To find and retrieve just the data that meets conditions that you specify, including data from multiple tables, create a query. A query can also update or delete multiple records at the same time, and perform predefined or custom calculations on your data.



This query accesses separate tables to retrieve the Order ID, Required Date, Company Name, and City information for customers in London whose orders were required in April.

Forms

To easily view, enter, and change data directly in a table, create a form. When you open a form, Microsoft Access retrieves the data from one or more tables, and displays it on the screen with the layout you choose in the Form Wizard, or with the layout that you created on your own in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).



A table displays many records at the same time, but you might have to scroll to see all of the data in a single record. Also, when viewing a table, you can't update data from more than one table at the same time.

A form focuses on one record at a time, and it can display fields from more than one table. It can also display pictures and other objects.

A form can contain a button that prints, opens other objects, or otherwise automates tasks.

Reports

To analyze your data or present it a certain way in print, create a report. For example, you might print one report that groups data and calculates totals, and another report with different data formatted for printing mailing labels.



Use a report to create mailing labels.

Use a report to show totals in a chart.

Use a report to calculate totals.

Data access pages

To make data available on the Internet or an intranet for interactive reporting, data entry, or data analysis, use a data access page. Microsoft Access retrieves the data from one or more tables and displays it on the screen with the layout that you created on your own in Design view, or with the layout you chose in the Page Wizard.



Click the expand indicator ...

... to display the data and record navigation toolbar for the next level of detail.

Use the record navigation toolbars to move to, sort, and filter records, and to get Help.


Tables: Store and manage your data

A table is a collection of data about a specific topic, such as products or suppliers. Using a separate table for each topic means that you store that data only once. This results in a more efficient database and fewer data-entry errors.

How data is organized in tables

Tables organize data into columns (called fields) and rows (called records).



For example, each field in a Products table contains the same type of information for every product, such as the product's name. Each record in that table contains all the information about one product, such as the product's name, supplier ID number, units in stock, and so on.

Table Design view

In table Design view, you can create an entire table from scratch, or add, delete, or customize the fields in an existing table.



If you want to track additional data in a table, add more fields. If an existing field name isn't descriptive enough, you can rename the field.

Setting a field's data type (field data type: A characteristic of a field that determines what kind of data it can store. For example, a field whose data type is Text can store data consisting of either text or number characters, but a Number field can only store numerical data.)defines what kind of values you can enter in a field. For example, if you want a field to store numerical values that you can use in calculations, set its data type to Number or Currency.

You use a unique tag, called a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.), to identify each record in your table. A table's primary key is used to refer to related records in other tables.

Field properties are a set of characteristics that provide additional control over how the data in a field is stored, entered, or displayed. Which properties are available depends on a field's data type.

How to relate two tables

A common field relates two tables so that Microsoft Access can bring together the data from the two tables for viewing, editing, or printing. In one table, the field is a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) that you set in table Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.). That same field also exists in the related table as a foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.).



In the Suppliers table, you enter a supplier ID, company name, and so on, for each supplier. SupplierID is the primary key that you set in table Design view.

In the Products table, you include the SupplierID field, so that when you enter a new product, you can identify its supplier by entering that supplier's unique ID number. SupplierID is the foreign key in the Products table.

Table Datasheet view

In a table or query, Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.) provides the tools you need to work with data.

Using the Table Datasheet and Query Datasheet toolbars

The Table Datasheet and Query Datasheet toolbars (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, use the Customize dialog box (point to Toolbars on the View menu and click Customize). To see more buttons, click Toolbar Options at the end of the toolbar.) provide many of the tools you need to find, edit, and print records.



Print or preview data

Check spelling

Cut, copy, or paste selected text, fields, whole records, or the entire datasheet

Sort records

Filter records, and find or replace values

Add or delete records

Working with columns, rows, and subdatasheets

You can find tools for working with columns, rows, and subdatasheets (subdatasheet: A datasheet that is nested within another datasheet and that contains data related or joined to the first datasheet.) in the datasheet itself, or by right-clicking a column selector (column selector: The horizontal bar at the top of a column. You can click a column selector to select an entire column in the query design grid or the filter design grid.).


Use the column selector to move, hide, or rename a column.

Resize columns or rows.

Use subdatasheets to view related data.

Freeze the leftmost column so that it is displayed as you scroll to the right.

Moving through records

You can use the navigation toolbar to move through the records in a datasheet.


Go to the first record.

Go to the previous record.

Type a record number to move to.

Go to the next record.

Go to the last record.

Go to a blank (new) record.

Queries: Getting answers to your questions

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 (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.). 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 (criteria: Conditions you specify to limit which records are included in the result set of a query or filter. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.)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 (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.)or a history table that contains old records.
SQL queries

An SQL query is a query you create by using an SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.). 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 (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), 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 (SQL view: A window that displays the SQL statement for the current query or is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, Access constructs the SQL equivalent 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 (SQL-specific query: A query that consists of an SQL statement. Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.), can't be created in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.). For pass-through (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of having the Microsoft Jet database engine process the data.), data-definition (data-definition query: A SQL-specific query that contains data definition language (DDL) statements. These statements allow you to create or alter objects in the database.), and union queries (union query: A query that uses the UNION operator to combine the results of two or more select queries.), you must create the SQL statements directly in SQL view. For subqueries (subquery: An SQL SELECT statement inside another select or action query.), you enter the SQL in the Field row or the Criteria row of the query design grid.


Forms: Putting a friendly face on your data

A form is a type of a database object that is primarily used to enter or display data in a database. You can also use a form as a switchboard that opens other forms and reports in the database, or as a custom dialog box that accepts user input and carries out an action based on the input.



A data-entry form

A switchboard form

A custom dialog box

Most forms are bound to one or more tables and queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) in the database. A form's record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) refers to the fields in the underlying tables and queries. A form need not contain all the fields from each of the tables or queries that it is based on.

A bound form stores or retrieves data from its underlying record source. Other information on the form, such as the title, date, and page number, is stored in the form's design.



Graphic elements, such as lines and rectangles, are stored in the form's design.

Data comes from the fields in the underlying record source.

A calculation comes from an expression, which is stored in the form's design.

Descriptive text is stored in the form's design.

You create a link between a form and its record source by using graphical objects called controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). The most common type of control used to display and enter data is a text box.



Labels display descriptive text.

Text boxes display data from and enter data into the Products table.

A text box uses an expression to calculate a total.

You can also open a form in PivotTable view or PivotChart view to analyze data. In these views, you can dynamically change the layout of a form to present data in different ways. You can rearrange row headings, column headings, and filter fields until you achieve the desired layout. Each time you change the layout, the form immediately recalculates the data based on the new arrangement.



In PivotTable view, you can view detail or summarized data by arranging fields in the filter, row, column, and detail areas.

In PivotChart view, you can display data visually by selecting a chart type and viewing data by arranging fields in the filter, series, category, and data areas.

Creating a form

You can create a form quickly by using the AutoForm command or a wizard. AutoForm creates a form that displays all fields and records in the underlying table or query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.). A wizard asks you questions and creates a form based on your answers. You can then customize the form the way you want it in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).

Customizing a form

In Design view

You can customize a form in Design view in the following ways:

Record source Change the tables and queries that a form is based on.

Controlling and assisting the user You can set form properties to allow or prevent users from adding, deleting, or editing records displayed in a form. You can also add custom Help to a form to assist your users with using the form.

Form window You can add or remove Maximize and Minimize buttons, short cut menus, and other Form window elements.

Sections You can add, remove, hide, or resize the header, footer, and details sections of a form. You can also set section properties to control the appearance and printing of a form.

Controls You can move, resize, or set the font properties of a control. You can also add controls to display calculated values, totals, current date and time, and other useful information on a form.

In PivotTable or PivotChart view

You can customize a form in PivotTable or PivotChart view in the following ways:

Add, move, or remove fields You can add fields to the filter, row, column, and detail areas in PivotTable view, and to the filter, category, series, and data areas in PivotChart view. You can also move fields from one area to another and remove fields from the view.

Filter records You can filter data displayed in the view by adding or moving a field to the filter area. You can also filter a field in the row and column area.

Sort records You can sort items in row or column fields in ascending or descending order. You can also sort items in custom order in PivotTable view.

Group records You can group items in row or column fields on intervals, or create custom groups.

Format elements and change captions In PivotTable view, you can change the font settings, alignment, background color, and number format of a field. You can also change the captions of fields and custom groups. In PivotChart view, you can change the chart type, format data markers, and more.

Reports: Presenting your data to the world

A report is an effective way to present your data in a printed format. Because you have control over the size and appearance of everything on a report, you can display the information the way you want to see it.



Create mailing labels.

Show totals in a chart.

Group records into categories.

Calculate totals.

Most reports are bound to one or more table and query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) in the database. A report's record source refers to the fields in the underlying tables and queries. A report need not contain all the fields from each of the tables or queries that it is based on.

A bound report gets its data from its underlying record source. Other information on the form, such as the title, date, and page number, is stored in the report's design.



The report title and column headings are stored in the report's design.

The date comes from an expression stored in the report's design.

Data comes from fields in the underlying table, query, or SQL statement.

Totals come from expressions, which are stored in the report's design.

You create the link between a report and its record source by using graphical objects called controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). Controls can be text boxes that display names and numbers, labels that display titles, or decorative lines that graphically organize the data and make the report more attractive.



Lines are decorative.

Labels display descriptive text.

Text boxes display data from the Sales by Date query.

A text box uses an expression to calculate a total.

Creating a report

You can create different types of reports quickly by using wizards. Use the Label Wizard to create mailing labels, the Chart Wizard to create charts, or the Report Wizard to create a standard report. The wizard asks you questions and creates a report based on your answers. You can then customize the report the way you want it in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).

Customizing a report

You can customize a report in the following ways:

Record source Change the tables and queries that a report is based on.
Sorting and grouping data You can sort data in ascending or descending order. You can also group records on one or more fields, and display subtotals and grand totals on a report.

Report window You can add or remove Maximize and Minimize buttons, change the title bar text, and other Report window elements.

Sections You can add, remove, hide, or resize the header, footer, and details sections of a report. You can also set section properties to control the appearance and printing of a report.

Controls You can move, resize, or set the font properties of a control. You can also add controls to display calculated values, totals, current date and time, and other useful information on a report.

Data access pages: Publishing your data to the Web

A data access page is a special type of Web page designed for viewing and working with data from the Internet or an intranet — data that is stored in a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) or a Microsoft SQL Server database (Microsoft SQL Server database: A database in Microsoft SQL Server, it consists of tables, views, indexes, stored procedures, functions, and triggers.). The data access page may also include data from other sources, such as Microsoft Excel.

A page in Page view or Microsoft Internet Explorer 5 or later

Using a data access page is similar to using a form: You can view, enter, edit, and delete data in a database. However, you can also use a page outside a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.), so users can update or view data over the Internet or an intranet.

The following illustration shows how the Product data access page, which is used for entering and updating product information, looks in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Internet Explorer 5.0 or later.) or in Microsoft Internet Explorer 5 or later.



Enter product information in the text boxes, drop-down list box, and check box.

Use the record navigation toolbar to navigate, add, delete, save, sort, and filter records, and to get Help.

A page in Design view

The following illustration shows the Products data access page in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).



Title in the body of the page

Group header for the Products group level

Record navigation section for the Products group level

Parts of a data access page

The body The body is the basic design surface of a data access page. On a page that supports data entry, you can use it to display informational text, controls bound to data, and sections.

Sections You use sections to display text, data from a database, and toolbars.

Two types of sections (section: A part of a form, report, or data access page such as a header, footer, or detail section.) are typically used on pages that support data entry: group header and record navigation sections. A page can also have footer and caption sections.

Group header and footer Used to display data and calculate values.
Record navigation Used to display the record navigation control (record navigation control: A control used on a data access page to display a record navigation toolbar. In a grouped page, you can add a navigation toolbar to each group level. You can customize the record navigation control by changing its properties.) for the group level. A record navigation section for a group appears after the group header section. You can't place bound controls (bound control: A control used on a form, report, or data access page to enter or display the contents of a field in the underlying table, query, or SQL statement. The control's ControlSource property stores the field name to which the control is bound.) in a record navigation section.
Caption Used to display captions for text boxes and other controls. It appears immediately before the group header. You can't place bound controls in a caption section.
Each group level in a data access page has a record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.). The name of the record source is displayed on the section bar (section bar: The horizontal bar above a data access page section in Design view. The section bar displays the type and name of the section. Use it to access the section's property sheet.) for each section used for a group level.

Designing different types of data access pages

You design data access pages in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) in Microsoft Access. The page is a separate file that is stored outside Access; however, when you create the file, Access automatically adds a shortcut to the file in the Database window. Designing a data access page is similar to designing forms and reports — you use a field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.), the toolbox (toolbox: A set of tools that are available in Design view to add controls to a form, report, or data access page. The toolset available in page Design view is different from the toolset available in form and report Design view.), controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.), and so on. However, there are some significant differences in the way that you design and interact with data access pages as opposed to forms and reports. How you design the page depends on what it will be used for.

Interactive reporting This type of data access page is often used to consolidate and group information that is stored in the database, and then publish summaries of the data. For example, a page might publish the sales performance for each region in which you do business. Using expand indicators (expand indicator: A button that is used to expand or collapse groups of records; it displays the plus (+) or minus (–) sign.), you can go from a general summary of the information, such as a list of all the regions and their combined sales total, to specific details on individual sales within each region. The data access page might provide toolbar buttons for sorting and filtering (filter: A set of criteria applied to data in order to display a subset of the data or to sort the data. In Access, you can use filtering techniques, such as Filter By Selection and Filter By Form to filter data.) the data, as well as for adding, editing, and deleting the data in some or all group levels (group level: The depth at which a group in a report or data access page is nested inside other groups. Groups are nested when a set of records is grouped by more than one field, expression, or group record source.).
Data analysis This type of data access page may include a PivotTable list (PivotTable list: A Microsoft Office Web Component used to analyze data interactively on a Web page. Data displayed in a row and column format can be moved, filtered, sorted, and calculated in ways that are meaningful for your audience.), similar to a Microsoft Excel PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources including ones external to Excel.), that lets you reorganize the data to analyze it in different ways. The page might contain a chart that you can use to analyze trends, detect patterns, and compare data in your database. Or it might contain a spreadsheet, in which you can enter and edit data and use formulas to calculate as you do in Excel.
Using data access pages in Internet Explorer

A data access page is connected directly to a database. When users display the data access page in Internet Explorer, they are viewing their own copy of the page. That means any filtering, sorting, and other changes they make to the way the data is displayed — including changes they make within a PivotTable list (PivotTable list: A Microsoft Office Web Component used to analyze data interactively on a Web page. Data displayed in a row and column format can be moved, filtered, sorted, and calculated in ways that are meaningful for your audience.) or spreadsheet (Spreadsheet Component: A Microsoft Office Web Component that provides the interactive functionality of a spreadsheet on a Web page. You can enter data, add formulas and functions, apply filters, change formatting, and recalculate right on the Web page.) — affect only their copy of the data access page. However, changes that they make to the data itself — such as modifying values, and adding or deleting data — are stored in the underlying database, and therefore are available to everyone viewing the data access page.

Users get Help on how to work with the page in Internet Explorer by clicking the Help button on the record navigation toolbar. The Help file that appears is automatically included with any data access page that's published with a record navigation toolbar. If you delete the record navigation toolbar or if you disable its Help button, you should provide instructions for using the page to those who will use the page.

Note To view and work with the data access page on the Internet or an intranet, users need Internet Explorer 5 or later.

Using data access pages in Microsoft Access

You can also work with a data access page in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Internet Explorer 5.0 or later.) in Access. Data access pages can supplement the forms and reports that you use in your database application. When deciding whether to design a data access page, form, or report, consider the tasks that you want to perform.

You get Help on how to work with the page in Page view by clicking the Help button on the Page View toolbar to display Access Help. Clicking the Help button on a record navigation toolbar on the page displays Help for working with the page in Microsoft Internet Explorer. As mentioned previously, this Help file is automatically included with any data access page that's published with a record navigation toolbar. You can delete the Help button on pages that are used only within Access, or you can modify the button to provide custom Help.

Where a data access page gets its data

Types of data sources for a data access page

A data access page gets its data from a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.), or a Microsoft SQL Server database (Microsoft SQL Server database: A database in Microsoft SQL Server, it consists of tables, views, indexes, stored procedures, functions, and triggers.) version 6.5 or later. To design a page using data from one of these databases, the page must be connected to the database. If you have already opened an Access database or an Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.) that's connected to a SQL Server database, the data access page you create automatically connects to the current database and stores that path in the ConnectionString property of the data access page. When a user browses to the page in Microsoft Internet Explorer 5 or displays the page in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Internet Explorer 5.0 or later.), it displays current data from the underlying database by using the path defined in the ConnectionString property. If you design the data access page while the database is on a local drive, Access will use the local path, which means the data won't be accessible to others. For this reason, it's important that you move or copy the database to a network location that's accessible to users of your page. Once the database is on the network share, open the database using the UNC (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename. ) address. If you move or copy the database after you've already designed the page, you'll have to update the path in the ConnectionString property to point to the new location.

Instead of having to update the ConnectionString property of each page in the database separately, you can choose to create a connection file. A connection file stores the connection information for a data access page, and can be shared between multiple data access pages. When you open a data access page that uses a connection file, the page reads the connection file and connects to the appropriate database. After creating the connection file, if you move or copy the database, all you have to do is edit the connection information in the connection file.

Types of data sources for Microsoft Office Web Components on a data access page

Although the data access page gets its data from a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) or Microsoft SQL Server database (Microsoft SQL Server database: A database in Microsoft SQL Server, it consists of tables, views, indexes, stored procedures, functions, and triggers.), Microsoft Office Web Component (Microsoft Office Web Components: Interactive components, such as worksheets, charts, and PivotTable lists on Web pages that facilitate data analysis. To use these components, you must have a Microsoft Office XP license.) controls on the page can display data from either of these databases or from other sources. For example, the page may contain a PivotTable list (PivotTable list: A Microsoft Office Web Component used to analyze data interactively on a Web page. Data displayed in a row and column format can be moved, filtered, sorted, and calculated in ways that are meaningful for your audience.), spreadsheet (Spreadsheet Component: A Microsoft Office Web Component that provides the interactive functionality of a spreadsheet on a Web page. You can enter data, add formulas and functions, apply filters, change formatting, and recalculate right on the Web page.), or chart (chart: A graphical representation of data in a form, report, or data access page.) with data from a Microsoft Excel worksheet or a database other than Access or SQL Server. Depending on the data source, the data in these controls may be a snapshot of the original data or it may be live data displayed through a separate connection that the control has with its data source.

To create an Office Web Component with data from a source other than the current database, you might have to use a program other than Access to first publish the data to a Web page. For example, to create a PivotTable list that contains data from an Excel worksheet, you have to publish the data from Excel. Then, in Access, you can open the Web page and turn it into a data access page by adding controls that are bound to an Access or SQL Server database. You can further customize the page by adding labels, pictures, or other features to enhance the page's appearance. From within Access, you can create a PivotTable list that uses data from an external source other than Excel.

Macros: Putting your data to work

Macros are a set of actions that you can create to help you to automate common tasks. By using groups of macros, you can perform several tasks at once.

What is a macro?

A macro is a set of one or more actions (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.) that each perform a particular operation, such as opening a form or printing a report. Macros can help you to automate common tasks. For example, you can run a macro that prints a report when a user clicks a command button.



When you create a macro, you enter the actions you want to carry out in this portion of the Macro window (Macro window: The window in which you create and modify macros.).

You can specify arguments for an action in this portion of the window.

A macro can be one macro composed of a sequence of actions, or it can be a macro group (macro group: A collection of related macros that are stored together under a single macro name. The collection is often referred to simply as a macro.). You can also use a conditional expression ( conditional expression: An expression that's evaluated and compared to a value — for example, If...Then and Select Case statements. If the condition is met, one or more operations are performed. If it isn't met, the operation is skipped.) to determine whether in some cases an action will be carried out when a macro runs.

The following macro is composed of a series of actions. Microsoft Access carries out these actions each time the macro runs. To run this macro, you refer to the macro name Review Products.



What is a macro group?

If you have numerous macros, grouping related macros in macro groups (macro group: A collection of related macros that are stored together under a single macro name. The collection is often referred to simply as a macro.)can help you to manage your database more easily.

For example, the following macro group, named Buttons, is made up of three related macros: Employees, Products, and Reps. Each macro carries out the OpenForm action, and the Products macro also carries out the MoveSize action.



The name in the Macro Name column identifies each macro. When you run a macro in a macro group, Microsoft Access carries out the action in the action column and any actions that immediately follow with a blank Macro Name column.

You can run a macro in a macro group in an event or event procedure (event procedure: A procedure automatically executed in response to an event initiated by the user or program code, or triggered by the system.) by typing the macro group name followed by a period and then the macro name. In the preceding example, to refer to the Employees macro in the Buttons macro group, you would type Buttons.Employees.

Conditional actions

In some cases, you may want to carry out an action or series of actions in a macro only if a particular condition (condition: Part of the criteria that a field must meet for searching or filtering. Some conditions must be used with a value; for example, the field Author with the condition equals with the value Jane. Author equals, by itself, would be incomplete.) is true. For example, if you're using a macro to validate data in a form, you might want to display one message in response to one set of values entered in a record and another message in response to a different set of values. In cases like these, you can use conditions to control the flow of the macro.

A condition is a logical expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that evaluates to True/False or Yes/No. The macro follows different paths depending on whether the condition is true or false.

When you run the macro, Microsoft Access evaluates the first conditional expression ( conditional expression: An expression that's evaluated and compared to a value — for example, If...Then and Select Case statements. If the condition is met, one or more operations are performed. If it isn't met, the operation is skipped.). If the condition is true, Microsoft Access carries out the action in that row and any of the immediately following actions that are preceded by an ellipsis (...) in the Condition column.

Microsoft Access then runs any additional actions in the macro that have a blank Condition column until it reaches another expression, a macro name, or the end of the macro.

If the condition is false, Microsoft Access ignores the action and any immediately following actions that are preceded by an ellipsis in the Condition column and moves to the next action row (action row: A row in the upper part of the Macro window in which you enter macro names, actions, conditions, and comments associated with a particular macro or macro group.) that contains another condition or a blank Condition column.

The following macro runs the MsgBox and the StopMacro actions only when the expression in the Condition column is true (when there is a Null (Null: A value you can enter in a field or use in expressions and queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null values.) value in the SupplierID field).



Action arguments

Action arguments are additional information required by some macro actions — for example, the object affected by the action or special conditions under which the action is carried out. After you add an action to a macro, you set the arguments for the action in the lower portion of the Macro window (Macro window: The window in which you create and modify macros.). These arguments give Microsoft Access additional information on how to carry out the action.

Tips for setting action arguments:

In general, it's a good idea to set action arguments in the order they're listed, because choices for one argument may determine those for arguments that follow.
If you add an action to your macro by dragging a database object (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) from the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), Microsoft Access automatically sets appropriate arguments for that action.
If an action has an argument that calls for the name of a database object, you can set the argument and the corresponding object type argument automatically by dragging the object from the Database window to the argument box.
You can use an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) preceded by an equal sign (=) to set many action arguments.

Modules: Automate routine tasks and create business solutions

A module is essentially a collection of declarations, statements, and procedures stored together as one named unit to organize your Microsoft Visual Basic (Microsoft Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.)code. Microsoft Access has two types of modules: standard modules (standard module: A module in which you can place Sub and Function procedures that you want to be available to other procedures throughout your database.)and class modules (class module: A module that can contain the definition for a new object. Each instance of a class creates a new object. Procedures defined in the module become properties and methods of the object. Class modules can exist alone or with forms and reports.).

What is a module?

A module is a collection of Visual Basic declarations and procedures that are stored together as a unit.



Object box

Procedure box

Declarations

Procedures

Procedure View button

Full Module View button

Class modules

Form (form module: A module that includes code for all event procedures triggered by events occurring on a specific form or its controls.) and report modules (report module: A module that includes code for all event procedures triggered by events occurring on a specific report or its controls.) are class modules that are associated with a particular form or report. Form and report modules often contain event procedures (event procedure: A procedure automatically executed in response to an event initiated by the user or program code, or triggered by the system.) that run in response to an event on the form or report. You can use event procedures to control the behavior of your forms and reports, and their response to user actions, such as clicking a command button.

When you create the first event procedure for a form or report, Microsoft Access automatically creates an associated form or report module.



To view or add a form procedure, choose one of the form's objects from the Object box ...

... and then choose an event from the Procedure box. Names of events that already have procedures are bold.

Procedures in your form and report modules can call procedures you have added to standard modules.

In Access 97 or later, class modules can also exist independent of a form or report, and this type of class module is listed in the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.). You can use a class module to create a definition for a custom object. In Access 95, class modules exist in association only with a form or report.

Standard modules

Standard modules contain general procedures that aren't associated with any other object, and frequently used procedures that can be run from anywhere within your database. The major difference between a standard module and a class module that is not associated with a particular object is one of scope and lifetime. The value of any variables or constants that are declared or exist in a class module without an associated object are available for use only while that code is running and only from that object.



Function name and arguments

Visual Basic declarations and code statements

Standard modules are listed in Modules under Objects in the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.). Form, report, and standard modules are also listed in the Object Browser (Object Browser: A dialog box that displays information about objects, properties, methods, and constants in the current project and in referenced object libraries, and that you use to search for an element, get help on it, or paste it into a module.).


Relationships in a database

After you've set up different tables for each subject in your Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.), you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships (relationship: An association established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. For example, this form includes information from four tables:



The Customers table

The Orders table

The Products table

The Order Details table

How relationships work

In the previous example, the fields in four tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. A relationship works by matching data in key fields — usually a field with the same name in both tables. In most cases, these matching fields are the primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) from one table, which provides a unique identifier for each record, and a foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) in the other table. For example, employees can be associated with orders they're responsible for by creating a relationship between the EmployeeID fields.



EmployeeID appears in both tables—-as a primary key ...

... and as a foreign key.

A one-to-many relationship

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.




One supplier ...

... can supply more than one product ...

... but each product has only one supplier.

A many-to-many relationship

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) consists of two fields — the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.




Primary key from the Orders table

Primary key from the Products table

One order can have many products ...

... and each product can appear on many orders.

A one-to-one relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game. Each soccer player in the Soccer Players table has one matching record in the Employees table.



Each soccer player has one matching record in the Employees table.

This set of values is a subset of the EmployeeID field and the Employees table.

About defining relationships

The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

A one-to-many relationship is created if only one of the related fields is a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or has a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.).
A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields — the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from the two other tables.
You can also create a relationship between a table and itself. This is useful in situations where you need to perform a Lookup within the same table. In the Employees table, for example, you can define a relationship between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

Note If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) line between the tables, but referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) won't be enforced, and there's no guarantee that records are unique in either table.

Referential integrity

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

The matching field from the primary table (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.) is a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or has a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.).
The related fields have the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).). There are two exceptions. An AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field can be related to a Number field with a FieldSize property setting of Long Integer, and an AutoNumber field with a FieldSize property setting of Replication ID can be related to a Number field with a FieldSize property setting of Replication ID.
Both tables belong to the same Microsoft Access database. If the tables are linked tables (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you can't change its structure.), they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity can't be enforced for linked tables from databases in other formats.
The following rules apply when you use referential integrity:

You can't enter a value in the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) field of the related table that doesn't exist in the primary key of the primary table. However, you can enter a Null (Null: A value you can enter in a field or use in expressions and queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null values.) value in the foreign key, specifying that the records are unrelated. For example, you can't have an order that is assigned to a customer that doesn't exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
You can't change a primary key value in the primary table, if that record has related records. For example, you can't change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.
Cascading updates and deletes

For relationships in which referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) is enforced, you can specify whether you want Microsoft Access to automatically cascade update (cascading update: For relationships that enforce referential integrity between tables, the updating of all related records in the related table or tables when a record in the primary table is changed.) and cascade delete (cascading delete: For relationships that enforce referential integrity between tables, the deletion of all related records in the related table or tables when a record in the primary table is deleted.) related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) values in a primary table (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.), Microsoft Access makes necessary changes to related tables to preserve referential integrity.

If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship isn't broken. Microsoft Access cascades updates without displaying any message.

Note If the primary key in the primary table is an AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field, setting the Cascade Update Related Fields check box will have no effect, because you can't change the value in an AutoNumber field.

If you select the Cascade Delete Related Records check box when defining a relationship, any time you delete records in the primary table, Microsoft Access automatically deletes related records in the related table. For example, if you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table (this includes records in the Order Details table related to the Orders records). When you delete records from a form or datasheet with the Cascade Delete Related Records check box selected, Microsoft Access warns you that related records may also be deleted. However, when you delete records using a delete query (delete query: A query (SQL statement) that removes rows matching the criteria you specify from one or more tables.), Microsoft Access automatically deletes the records in related tables without displaying a warning.
Read More..

What's New

From Microsoft Access Help


Key new features in Microsoft Access
PivotChart and PivotTable Views


Microsoft Access 2002 introduces PivotTable and PivotChart views to tables, queries, views, stored procedures, functions, and forms. You can now perform data analysis and build rich PivotTable and PivotChart view solutions more quickly than ever before. PivotTable and PivotChart views can be saved as data access pages that can be viewed by anyone who has Microsoft Internet Explorer 5 or later. You can also use subforms in PivotTable and PivotChart views in exactly the same fashion that they are used with forms in Datasheet view today. Developers will also find it easy to write code behind forms in PivotTable and PivotChart views and take advantage of new events available in PivotTable and PivotChart views.


XML Support
In addition to being the standard technology for interchanging data on the Web, Extensible Markup Language (XML) is quickly becoming the preferred technology for exchanging data between business software applications. Microsoft Access 2002 provides powerful, intuitive ways of sharing XML data regardless of differences in the platform, data format, protocol, schema, or business rules. By using Access' familiar user interface, you can easily create XML data or schema documents from Jet or SQL Server structures and data. You can also use XML data from other applications in your forms, reports and data access pages. For example, suppose your data is scattered across a wide variety of sources — internal SQL servers, Excel spreadsheets, and other data providers like SAP. Since these sources use XML as their data interchange format, you could create a series of aggregation queries in Access to pull this data into views and then design forms and reports by using those views.

Access also provides methods for easily controlling your data by making it simple to create and apply schemas and style sheets. Access allows you to easily describe and deliver rich, structured XML data to and from any application in a standard, consistent way. For example, you can use Access to create a schema that describes the structure of your data and then send the schema to your vendors so that they know exactly how to expect your data to appear in their invoices.

Extended Property Support with Microsoft SQL Server 2000
The built-in integration between Microsoft Access 2002 and Microsoft SQL Server 2000 has improved significantly by the inclusion of support for extended SQL database properties from within your Access project. By using extended properties in your Access 2002 projects, you can implement such features as lookup relationships, validation rules (also called constraints), text formatting, and subdatasheets. You can use extended properties with tables, views, stored procedures, and functions, just like you can with similar objects in Access data files. Using extended properties makes it easy to save column widths, row heights, fonts, and input mask settings from one Access project session to another. Extended properties make it even easier to migrate your business applications from Access databases to Access projects connected to Microsoft SQL Server.

More new features in Access
Round-tripping
You can now work with and modify Access 2000 files in Access 2002 without converting the file format. This allows you to easily share different versions of database files with other Access users.

Multiple Undo and Redo
You now have the ability to undo and redo multiple actions in Design view in all objects in your Microsoft Access databases and in views, stored procedures, and functions in your Microsoft Access project.

Batch Updates in Access Projects using Microsoft SQL Server
You can now have Access 2002 projects batch all data entry and send it to the server when the user navigates from a record, closes a form, or selects a command. You can also create a button on your form that saves all records or undoes all changes to records, programmatically.

Updateable Off-line Data Access Pages
You can now take the data access pages in your Access project offline, make changes to them on your laptop, and have them automatically synchronize when you reconnect to the SQL server. Changes to the off-line pages are made to an Access project connected to a local Microsoft SQL Server 2000 Desktop Engine (formerly MSDE).

Conversion Error Logging
If problems are encountered when converting from Access 95 and later versions, Access 2002 will now create a table that lists information about each error, making it much easier to identify and solve problems.

Subforms/Subreports that Live in Design view
You can now open subforms or subreports in their own Design view window directly from within the form or report or from the View menu. Scrolling has also been improved so that it is easier to work with subforms and subreports in Design view.

Password Security in an Access Project
You can now change the logon password specified in an Access project connected to a Microsoft SQL Server 6.5 or later version database directly from within your Access 2002 menu.

The Linked Table Wizard
The Linked Table Wizard guides you through the process of linking your tables to a SQL Server database, and does this all from within your Access project.

Improved Support for International Complex-scripts
Access 2002 now exposes complex-script interface items easily, which means that with a simple click, you can switch the reading direction from left-to-right to right-to-left in language-specific objects.

Improved Accessibility Features
Access 2002 now provides even greater ease in working with forms and reports.

Pressing F8 in form or report Design will now display the field list.
Pressing ENTER after selecting a field in the field list in form or report Design view will automatically add the field to the form or report design surface.
Pressing CTRL+TAB will move the focus from a form or report section to a subsection.
Two additional powers (1000% and 500%) have been added to the Zoom option in print preview.
New Microsoft Office features
Everyday tasks

Office task panes The most common tasks in Microsoft Office are now organized in panes that display in place with your Office document. Continue working while you search for a file using the Search task pane, pick from a gallery of items to paste in the Office Clipboard task pane, and quickly create new documents or open files using the task pane that appears when you start an Office program. Other task panes vary per Office program.

New look Microsoft Office XP has a cleaner, simpler look to its interface. Softer colors also contribute to this updated feel.

More convenient access to Help Get the full power of the Answer Wizard in an unobtrusive package. When you enter a question about an Office program in the Ask a Question box on the menu bar, you can see a list of choices and read a Help topic whether you are running the Office Assistant or not.

Control paste options and automatic changes with smart tags New in-place buttons called "smart tags" let you immediately adjust how information is pasted or how automatic changes occur in your Office programs. For example, when you paste text from Microsoft Word into Microsoft PowerPoint, a button appears next to the text. Click the button to see a list of choices for fine-tuning the formatting of the pasted text. Smart tags and their associated choices vary per Office program.

Updated Clip Organizer Hundreds of new clips, an easy task pane interface, as well as the same abilities to organize clips and find new digital art on the Web are part of the updated Clip Organizer (formerly Clip Gallery).

Conceptual diagrams Word, Microsoft Excel, and PowerPoint include a new gallery of conceptual diagrams. Choose from diagrams such as Pyramid for showing the building blocks of a relationship, Radial for showing items in relation to a core element, and more.

Voice commands and dictation In addition to mouse and keyboard methods, you can now select menu, toolbar, and dialog box items by speaking. You can also dictate text. This feature is available in the Simplified Chinese, English (U.S.), and Japanese language versions of Microsoft Office, and has some special hardware requirements.

Support for handwriting You can use handwriting recognition to enter text into an Office document. You can write by using a handwriting input device — such as a graphics tablet or a tablet-PC — or you can write using your mouse. Your natural handwriting is converted to typed characters. In Word and Microsoft Outlook, you can also choose to leave text in handwritten form.

Improved fidelity of pictures and drawings In Office XP, Word, Excel, PowerPoint, Microsoft FrontPage, and Microsoft Publisher are using an improved graphics system (GDI+). With this new graphics system, shapes and WordArt have smoother outlines and adjustable levels of transparency with true blending. Digital pictures stay sharper and clearer when you resize them.

Accessibility Office XP programs support Microsoft Active Accessibility 2.0. This technology makes accessibility aids, such as screen readers or screen enlargers, more effective.

Find printers If your organization uses Microsoft Windows 2000 and the Active Directory directory service, you can search for printers across your network from the Print dialog box in Office XP programs.

Storing documents with Microsoft Exchange Server 2000 You can store Microsoft Office documents on Exchange Server 2000 and access them through the File Open, File New, and File Save dialog boxes, as you would any other Office document.


Web documents and Web sites

Target your Web publishing efforts Save your Microsoft Office documents as Web pages for versions 3.0-6.0 of various Web browsers and give your readers the best possible viewing experience.

Share your Office documents over the Web From any Office program, you can save documents to Web sites on MSN. This gives you an instant collaboration space where you can share files with other people.

Save a Web site as a single file A special Web archive file format is available in Microsoft Access, Microsoft Excel, Microsoft PowerPoint, Microsoft Publisher, and Microsoft Word. This file format lets you save all the elements of a Web site, including text and graphics, into a single file.

Open Office Web pages for editing from the browser Office programs recognize the HTML pages that they generate. You can open an Office document that you've saved as a Web page in the program it was created in, right from Microsoft Internet Explorer.


Error Prevention and recovery

Document recovery and safer shutdown Documents you are working on can be recovered if the program encounters an error or stops responding. The documents are displayed in the Document Recovery task pane the next time you open the program.

Office Safe Mode Microsoft Office XP programs can detect and isolate startup problems. You can bypass the problem, run your Office program in safe mode, and keep getting your work done.

Office crash reporting tool Diagnostic information about program crashes can be collected and sent to your company's information technology department or to Microsoft, allowing engineers to correct these problems so they don't interrupt you again.


Security

Digital signatures You can apply a digital signature to Microsoft Word, Microsoft Excel, and Microsoft PowerPoint files to confirm that the file has not been altered.

Increased protection against macro viruses Network administrators can remove Microsoft Visual Basic for Applications, the programming language of Microsoft Office, when deploying Office. This can decrease the possibility of viruses spreading via Office documents.


Language-specific features

If you are not using the specific language version of Microsoft Office for which one of these features is designed, then the feature is only available if you have installed the Microsoft Office XP Multilingual User Interface Pack or Microsoft Office Proofing Tools for that language. In addition, you must enable support for the language through Microsoft Office Language Settings.

Improvements to changing the language of the user interface and Help More components across Microsoft Office support changing the language of their user interface. In addition, terms on the interface match the terms in Help, even when the main text of Help is in a different language than the interface. This feature is only available with the Office XP MUI Pack and a volume licensing agreement.

Hangul/Hanja converter improvements Over 20,000 new characters are supported by this converter for Korean language documents. The converter automatically uses new fonts that have the proper glyphs for the new characters.

Full support for Windows 2000 language features Microsoft Office programs now support all the languages that Microsoft Windows 2000 does. Office also supports the latest extensions to Chinese character encoding via Unicode, including support for almost 70,000 Chinese characters.

East Asian character support on non-East Asian systems Now you can enter characters from East Asian languages in all Office programs, even if your system software is a non-East Asian language version. (This was previously only supported in Microsoft Word and Microsoft Outlook, or on Windows 2000.) For example, on a computer running English (U.S.) Microsoft Windows 98, you can enter Japanese characters in Microsoft Excel.
Read More..

Get started with Access 2002

From Microsoft Access Help

Get started with Access 2002
There are different ways you can Get started with Access 2002, depending on your experience level.

Upgrading from previous versions of Microsoft Access


You can use a Microsoft Access 2000 database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) or Access 2000 project (Microsoft Access project:


An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.) in both Access 2002 and Access 2000 — even if the file is a secured Access database. If you also want to make an Access database available to users of Access 97 or earlier, use one or more of the following procedures.

Use a one-file Access database with several versions of Microsoft Access

If your Microsoft Access database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) is in one file, and you want to use it in several versions of Microsoft Access, convert it to a front-end/back-end application (front-end/back-end application: An application consisting of a "back-end" database file that contains tables, and copies of a "front-end" database file that contain all other database objects with links to the “back-end” tables.). The data remains in the oldest version of Access, and you use a front end that has been converted to Access 2000 or Access 2002.

Convert the Access database to Access 2000 or Access 2002 file format. When converting, specify a new name for the file.
How?

Make a backup copy of the Microsoft Access file (Microsoft Access file: An Access database or Access project file. An Access database stores database objects and data in an .mdb file. A project file doesn't contain data and is used to connect to a Microsoft SQL Server database. ) you're going to convert.
If possible, compile the Access file in the previous version of Access. This reduces the possibility of errors during conversion.
Close the Access file. If the file is a multiuser (multiuser (shared) database: A database that permits more than one user to access and modify the same set of data at the same time.) Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) located on a server or in a shared folder, make sure that no one else has it open.
Start Access 2002.
On the Tools menu, point to Database Utilities, point to Convert Database, and then click the file format you want for the new file.
In the Database To Convert From dialog box, select the database you want to convert and then click Convert.
In the Convert Database Into dialog box, do one of the following:
Type a name for the new file.
Select a different location for the new file. (You can keep the same name, or change it.)
You cannot convert an Access database into a file with the same name and location as the original database.

Click Save.
On the Tools menu, point to Database Utilities and use the Database Splitter to split the converted database into a front-end/back-end application.
Delete the back-end database that the Database Splitter created.
You want your data to remain in the original database, so you will use the original database as the back-end database. The back-end database should be in the oldest version of Microsoft Access that is being used.

On the Tools menu, point to Database Utilities and use the Linked Table Manager to link (link (tables): An action that establishes a connection to data from another application so that you can view and edit the data in both the original application and in Access.) the new front-end database to the tables in the previous-version database.
You can then enhance the new front-end database to support new features for users that have upgraded to Microsoft Access 2000 or Access 2002. Users of previous versions can continue to use the previous-version database. For example, if the back-end tables are in Microsoft Access version 2.0 format, you can use up to four versions of Microsoft Access: Microsoft Access version 2.0 (using the original database), Microsoft Access 95 and 97 (using an enabled version of the original database or a converted front-end database), and Microsoft Access 2000 or Access 2002 (using a converted front-end database).

Use a front-end/back-end application with several versions of Microsoft Access

If your Microsoft Access database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) is already a front-end/back-end application (front-end/back-end application: An application consisting of a "back-end" database file that contains tables, and copies of a "front-end" database file that contain all other database objects with links to the “back-end” tables.), you only need to convert the front end to Access 2000 or Access 2002 file format.

Leave the back-end database alone.
Convert the front-end database to Access 2000 or Access 2002 file format.
How?

Make a backup copy of the Microsoft Access file (Microsoft Access file: An Access database or Access project file. An Access database stores database objects and data in an .mdb file. A project file doesn't contain data and is used to connect to a Microsoft SQL Server database. ) you're going to convert.
If possible, compile the Access file in the previous version of Access. This reduces the possibility of errors during conversion.
Close the Access file. If the file is a multiuser (multiuser (shared) database: A database that permits more than one user to access and modify the same set of data at the same time.) Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) located on a server or in a shared folder, make sure that no one else has it open.
Start Access 2002.
On the Tools menu, point to Database Utilities, point to Convert Database, and then click the file format you want for the new file.
In the Database To Convert From dialog box, select the database you want to convert and then click Convert.
In the Convert Database Into dialog box, do one of the following:
Type a name for the new file.
Select a different location for the new file. (You can keep the same name, or change it.)
You cannot convert an Access database into a file with the same name and location as the original database.

Click Save.
On the Tools menu, point to Database Utilities and use the Linked Table Manager to link (link (tables): An action that establishes a connection to data from another application so that you can view and edit the data in both the original application and in Access.) the new Access 2000 or Access 2002 front-end database to the tables in the previous-version back-end database. You can then enhance the Access 2000 or Access 2002 front-end database to support new features for users that have upgraded to Access 2000 or Access 2002.
Open a database created in Access 97 or earlier without converting it

You can open a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) created in Access 97 or earlier without converting it.

Make sure that the Access database you're going to enable (enabled database: A previous-version database that has been opened in Access 2000 or later without converting its format. To change the design of the database, you must open it in the version of Access in which it was created.) is closed. If the file is a multiuser (multiuser (shared) database: A database that permits more than one user to access and modify the same set of data at the same time.) Access database located on a server or in a shared folder, make sure that no one else has it open.
Click Open on the Database toolbar.
In the Open dialog box, click the previous-version Access database you want to enable and click Open.
In the Convert/Open Database dialog box, click Open Database.
Microsoft Access enables (enabled database: A previous-version database that has been opened in Access 2000 or later without converting its format. To change the design of the database, you must open it in the version of Access in which it was created.) the previous-version database by storing additional information in the file to accommodate the default file format specified on the Advanced tab of the Options dialog box. An enabled database can still be opened with its original version of Microsoft Access.

Use a secured Access database with multiple versions of Access

With one exception, the issues involved when sharing a secured database across more than one version of Microsoft Access are the same as the issues for sharing an unsecured database across more than one version. The one exception concerns how to handle the workgroup information files (workgroup information file: A file Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.) that are used with the secured database.

Do one of the following:

If you want to share a secured database from Access 95 or 97, tell users who will be upgrading to Access 2002 to join the appropriate workgroup information file with the oldest version of Access that will be sharing the secured database.
How?

Important If you are setting up user-level security (user-level security: When using user-level security in an Access database, a database administrator or an object's owner can grant individual users or groups of users specific permissions to tables, queries, forms, reports, and macros.) and need to make sure that your workgroup (workgroup: A group of users in a multiuser environment who share data and the same workgroup information file.) and its permissions (permissions: A set of attributes that specify what kind of access a user has to data or objects in a database.) can't be duplicated, you should make sure the workgroup information file (workgroup information file: A file Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.) that defines the workgroup you're joining has been created with a unique workgroup ID (WID (workgroup ID: A case-sensitive alphanumeric string 4 to 20 characters long that you enter when creating a new workgroup information file by using the Workgroup Administrator. This uniquely identifies the Admins group for this workgroup file.)). If such a workgroup information file doesn't exist, you should create one.

Start Microsoft Access.
On the Tools menu, point to Security, and then click Workgroup Administrator.
In the Workgroup Administrator dialog box, click Join.
Type the path and name of the workgroup information file that defines the Microsoft Access workgroup you want to join, and then click OK, or click Browse and then use the Select Workgroup Information File dialog box to locate the workgroup information file.
The next time you start Microsoft Access, it uses the user (user account: An account identified by a user name and personal ID (PID) that is created to manage the user’s permissions to access database objects in an Access workgroup.) and group accounts (group account: A collection of user accounts in a workgroup, identified by group name and personal ID (PID). Permissions assigned to a group apply to all users in the group.) and passwords stored in the workgroup information file for the workgroup you joined.

Access 2002 can use workgroup information files that have been created with any previous version. With one exception, a previous version of Access can't use a workgroup information that was created in a later version. The exception is Access 2000, which can use a workgroup information file that was created in Access 2002 if that workgroup information file is in Access 2000 file format.

Important If users will be sharing a secured database from Microsoft Access 95 or 97, you should compact the current workgroup information file with Access 2002 before using it. Compacting the file by using Access 2002 does not change the file format, so the file can continue to be used by any Microsoft Access 95 or 97 users who are not upgrading.

If the shared database is Microsoft Access version 2.0, convert the workgroup information file that will be used with the secured database.
How?

You do not need to convert a Microsoft Access 2000 workgroup information file (workgroup information file: A file Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.) to Access 2002. However, to take advantage of security and performance improvements, you should re-create workgroup information files from Access 97 or earlier as described below.

Create a new workgroup information file, making sure to enter the exact, case-sensitive name, company name, and workgroup ID (workgroup ID: A case-sensitive alphanumeric string 4 to 20 characters long that you enter when creating a new workgroup information file by using the Workgroup Administrator. This uniquely identifies the Admins group for this workgroup file.) that was used to create the original file. Failure to re-enter the exact entries that were used to create the original file will create an invalid Admins group (Admins group: The system administrator's group account that retains full permissions on all databases used by a workgroup. The Setup program automatically adds the default Admin user account to the Admins group.).
How?

A Microsoft Access workgroup information file (workgroup information file: A file Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.) contains a list of users who share data, also know as a workgroup (workgroup: A group of users in a multiuser environment who share data and the same workgroup information file.). Users' passwords are also stored in the workgroup information file. To control who has access to your database, you must create a new workgroup information file.

Start Microsoft Access.
On the Tools menu, point to Security, and then click Workgroup Administrator.
In the Workgroup Administrator dialog box, click Create.
In the Workgroup Owner Information dialog box, type your name and organization, and then type any combination of up to 20 numbers and letters for the workgroup ID (WID) (workgroup ID: A case-sensitive alphanumeric string 4 to 20 characters long that you enter when creating a new workgroup information file by using the Workgroup Administrator. This uniquely identifies the Admins group for this workgroup file.).
Caution Be sure to write down your exact name, organization, and workgroup ID — including whether letters are uppercase or lowercase (for all three entries) — and keep them in a secure place. If you have to re-create the workgroup information file, you must supply exactly the same name, organization, and workgroup ID. If you forget or lose these entries, you can't recover them and might lose access to your databases.

Type a new name for the new workgroup information file. By default, the workgroup information file is saved in the language folder. To save in a different location, type a new path or click Browse to specify the new path.
Click OK.
The new workgroup information file is used the next time you start Microsoft Access. Any user (user account: An account identified by a user name and personal ID (PID) that is created to manage the user’s permissions to access database objects in an Access workgroup.) and group accounts (group account: A collection of user accounts in a workgroup, identified by group name and personal ID (PID). Permissions assigned to a group apply to all users in the group.) or passwords that you create are saved in the new workgroup information file. To have others join the workgroup defined by your new workgroup information file, copy the file to a shared folder (if you didn't already save it in a shared folder in step 5), and then have each user run the Workgroup Administrator to join the new workgroup information file.

Re-create any group accounts, making sure to enter the exact, case-sensitive group name and personal ID (PID (personal ID: A case-sensitive alphanumeric string 4 to 20 characters long that Access uses in combination with the account name to identify a user or group in an Access workgroup.)) for each group.
How?

As part of securing a database, you can create group accounts (group account: A collection of user accounts in a workgroup, identified by group name and personal ID (PID). Permissions assigned to a group apply to all users in the group.) in your Microsoft Access workgroup (workgroup: A group of users in a multiuser environment who share data and the same workgroup information file.)that you use to assign a common set of permissions (permissions: A set of attributes that specify what kind of access a user has to data or objects in a database.) to multiple users.

To complete this procedure, you must be logged on as a member of the Admins group (Admins group: The system administrator's group account that retains full permissions on all databases used by a workgroup. The Setup program automatically adds the default Admin user account to the Admins group.).

Start Microsoft Access by using the workgroup in which you want to use the account.
Important The accounts you create for users must be stored in the workgroup information file (workgroup information file: A file Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.) that those users will use. If you're using a different workgroup to create the database, change your workgroup before creating the accounts. You can change workgroups by using the Workgroup Administrator.

Open a database.
On the Tools menu, point to Security, and then click User And Group Accounts.
On the Groups tab, click New.
In the New User/Group dialog box, type the name of the new account and a personal ID (PID) (personal ID: A case-sensitive alphanumeric string 4 to 20 characters long that Access uses in combination with the account name to identify a user or group in an Access workgroup.).
Caution Be sure to write down the exact account name and PID, including whether letters are uppercase or lowercase, and keep them in a secure place. If you have to re-create an account that has been deleted or created in a different workgroup, you must supply the same name and PID entries. If you forget or lose these entries, you can't recover them.

Note A user account name cannot be same as an existing group account name, and visa versa.

Click OK to create the new group account.
Note The PID entered in step 5 is not a password. Microsoft Access uses the PID and the user name as seeds for an encryption algorithm to generate a secure identifier for the user account (user account: An account identified by a user name and personal ID (PID) that is created to manage the user’s permissions to access database objects in an Access workgroup.).

Re-create each user account, making sure to enter the exact, case-sensitive user name and PID for each user.
How?

To complete this procedure, you must be logged on as a member of the Admins group (Admins group: The system administrator's group account that retains full permissions on all databases used by a workgroup. The Setup program automatically adds the default Admin user account to the Admins group.).

Note It is usually easier to manage security if you organize users into groups, and then assign permissions (permissions: A set of attributes that specify what kind of access a user has to data or objects in a database.) to groups rather than to individual users.

Start Microsoft Access by using the workgroup (workgroup: A group of users in a multiuser environment who share data and the same workgroup information file.) in which you want to use the account.
Important The accounts you create for users must be stored in the workgroup information file (workgroup information file: A file Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.) that those users will use. If you're using a different workgroup to create the database, change your workgroup before creating the accounts.

Open a database.
On the Tools menu, point to Security, and then click User And Group Accounts.
On the Users tab, click New.
In the New User/Group dialog box, type the name of the new account and a personal ID (PID) (personal ID: A case-sensitive alphanumeric string 4 to 20 characters long that Access uses in combination with the account name to identify a user or group in an Access workgroup.), and then click OK to create the new account, which is automatically added to the Users group (Users group: The group account that contains all user accounts. Access automatically adds user accounts to the Users group when you create them.).
Caution Be sure to write down the exact account name and PID, including whether letters are uppercase or lowercase, and keep them in a secure place. If you ever have to re-create an account that has been deleted or created in a different workgroup, you must supply the same name and PID entries. If you forget or lose these entries, you can't recover them.

Notes

A user account name cannot be same as an existing group account name, and visa versa.
The PID entered in step 5 is not a password. Microsoft Access uses the PID and the user name as seeds for an encryption algorithm to generate a secure identifier for the user account (user account: An account identified by a user name and personal ID (PID) that is created to manage the user’s permissions to access database objects in an Access workgroup.).
Tell only users who are upgrading to Microsoft Access 2000 or Access 2002 to join the converted workgroup information file. All users who are not upgrading from version 2.0 must continue to use the workgroup information file produced with their original version.


Already familiar with other database or spreadsheet applications

If you are familiar with other database or spreadsheet applications, then you probably know the basics of how these applications work and what databases are used for. Microsoft Access differs from many other database applications by allowing you to create relational databases (relational database: A type of database that stores information in tables. Uses matching values from two tables to relate data in one table to data in the other table. In a relational database, you typically store a specific type of data just once.). Access also provides many options for working with other database programs such as Microsoft SQL Server.

Relationships in a database

After you've set up different tables for each subject in your Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.), you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships (relationship: An association established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. For example, this form includes information from four tables:



The Customers table

The Orders table

The Products table

The Order Details table

How relationships work

In the previous example, the fields in four tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. A relationship works by matching data in key fields — usually a field with the same name in both tables. In most cases, these matching fields are the primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) from one table, which provides a unique identifier for each record, and a foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) in the other table. For example, employees can be associated with orders they're responsible for by creating a relationship between the EmployeeID fields.



EmployeeID appears in both tables—-as a primary key ...

... and as a foreign key.

A one-to-many relationship

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.




One supplier ...

... can supply more than one product ...

... but each product has only one supplier.

A many-to-many relationship

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) consists of two fields — the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.




Primary key from the Orders table

Primary key from the Products table

One order can have many products ...

... and each product can appear on many orders.

A one-to-one relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game. Each soccer player in the Soccer Players table has one matching record in the Employees table.



Each soccer player has one matching record in the Employees table.

This set of values is a subset of the EmployeeID field and the Employees table.

About defining relationships

The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

A one-to-many relationship is created if only one of the related fields is a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or has a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.).
A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields — the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from the two other tables.
You can also create a relationship between a table and itself. This is useful in situations where you need to perform a Lookup within the same table. In the Employees table, for example, you can define a relationship between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

Note If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) line between the tables, but referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) won't be enforced, and there's no guarantee that records are unique in either table.

Referential integrity

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

The matching field from the primary table (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.) is a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or has a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.).
The related fields have the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).). There are two exceptions. An AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field can be related to a Number field with a FieldSize property setting of Long Integer, and an AutoNumber field with a FieldSize property setting of Replication ID can be related to a Number field with a FieldSize property setting of Replication ID.
Both tables belong to the same Microsoft Access database. If the tables are linked tables (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you can't change its structure.), they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity can't be enforced for linked tables from databases in other formats.
The following rules apply when you use referential integrity:

You can't enter a value in the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) field of the related table that doesn't exist in the primary key of the primary table. However, you can enter a Null (Null: A value you can enter in a field or use in expressions and queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null values.) value in the foreign key, specifying that the records are unrelated. For example, you can't have an order that is assigned to a customer that doesn't exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
You can't change a primary key value in the primary table, if that record has related records. For example, you can't change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.
Cascading updates and deletes

For relationships in which referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) is enforced, you can specify whether you want Microsoft Access to automatically cascade update (cascading update: For relationships that enforce referential integrity between tables, the updating of all related records in the related table or tables when a record in the primary table is changed.) and cascade delete (cascading delete: For relationships that enforce referential integrity between tables, the deletion of all related records in the related table or tables when a record in the primary table is deleted.) related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) values in a primary table (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.), Microsoft Access makes necessary changes to related tables to preserve referential integrity.

If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship isn't broken. Microsoft Access cascades updates without displaying any message.

Note If the primary key in the primary table is an AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field, setting the Cascade Update Related Fields check box will have no effect, because you can't change the value in an AutoNumber field.

If you select the Cascade Delete Related Records check box when defining a relationship, any time you delete records in the primary table, Microsoft Access automatically deletes related records in the related table. For example, if you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table (this includes records in the Order Details table related to the Orders records). When you delete records from a form or datasheet with the Cascade Delete Related Records check box selected, Microsoft Access warns you that related records may also be deleted. However, when you delete records using a delete query (delete query: A query (SQL statement) that removes rows matching the criteria you specify from one or more tables.), Microsoft Access automatically deletes the records in related tables without displaying a warning.


Working with other applications

Microsoft Access 2002 provides features for working with other programs.

Work with Microsoft SQL Server You can create a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.) that is easy to connect to a Microsoft SQL Server database, or use the Microsoft SQL Server Database Wizard to quickly create a SQL Server database and an Access project at the same time. Working with an Access project is similar to working with a Microsoft Access database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) — the process of creating forms, reports, data access pages, macros, and modules is the same. Once you connect to a SQL Server database, you can view, create, modify, and delete tables, views, stored procedures, and database diagrams using the Microsoft SQL Server Design Tools.
Create a new Access database from data in another file format You can open a file that is in another file format — such as text, dBASE, Paradox, or spreadsheet format — in Access; Microsoft Access automatically creates an Access database and links the file for you.
Import or link data from Microsoft Outlook or Microsoft Exchange You can use the Exchange Import Wizard or the Outlook Import Wizard to import or link data from Microsoft Outlook and Microsoft Exchange Server. For example, you might want to link to your Microsoft Outlook Contacts folder and then create form letters and mailing labels by merging the data with the Microsoft Word Mail Merge Wizard.
Export to other sources You can export data to a number of formats and applications from Access.
Data formats you can export to

Application Version or format supported
Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) 2.0, 7.0/95, 8.0/97, 9.0/2000, 10.0/Access 2002
Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.) 9.0/2000, 10.0/Access 2002
dBASE III, IV, and 5; and 7 (Requires Borland Database Engine 4.x or later. Updated drivers may be available from Microsoft Technical Support)
Paradox, Paradox for Microsoft Windows 3.x, 4.x, and 5.0; and 8.0 (Requires Borland Database Engine 4.x or later. Updated drivers may be available from Microsoft Technical Support)
Microsoft Excel 3.0, 4.0, 5.0, 7.0/95, 8.0/97, 9.0/2000, and 10.0/Excel 2002
Microsoft Word, Rich Text Format All
Lotus 1-2-3 .wj2, .wk1 and .wk3 formats
Delimited text files (delimited text file: A file containing data where individual field values are separated by a character, such as comma or a tab.) All character sets
Fixed-width text files (fixed-width text file: A file containing data, where each field has a fixed width.) All character sets
HTML (HTML: The standard markup language used for documents on the World Wide Web. HTML uses tags to indicate how Web browsers should display page elements such as text and graphics and how to respond to user actions.) and IDC/HTX (IDC/HTX files: Microsoft Internet Information Server uses an IDC file and an HTX file to retrieve data from an ODBC data source and format it as an HTML document.) 1.0 (if a list)
2.0, 3.x, 4.x (if a table or list)
Microsoft Active Server Pages (Active Server Page (ASP): A file that contains embedded server-side scripting that is executed on a server and sent to and displayed in a client Web browser as a standard HTML file. ) All
XML (Extensible Markup Language (XML): A format for delivering rich, structured data from an application in a standard, consistent way. XML describes the content of a Web document, whereas HTML tags describe how the document looks.) Documents All
SQL tables, Microsoft Visual FoxPro, and programs and databases that support the ODBC (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) protocol Visual FoxPro 3.0, 5.0, and 6.x
For an up-to-date list of supported ODBC drivers (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.), see the Microsoft Knowledge Base.


Import or link data to an Access database Just as you can export to a number of formats and applications, you can also work with the data from other sources and applications.
Data formats you can import or link to

Data source Version or format supported
Microsoft Access database 2.0, 7.0/95, 8.0/97, 9.0/2000, 10.0/Access 2002
Microsoft Access project 9.0/2000, 10.0/Access 2002
dBASE III, IV, and 5; and 7 (Linking requires updated ISAM drivers available from Microsoft Technical Support)
Paradox, Paradox for Microsoft Windows 3.x, 4.x, and 5.0; and 8.0 (Linking requires updated ISAM drivers available from Microsoft Technical Support)
Microsoft Excel spreadsheets 3.0, 4.0, 5.0, 7.0/95, 8.0/97, 9.0/2000, and 10.0/Excel 2002
Lotus 1-2-3 spreadsheets (Linking is read-only) .wks, .wk1, .wk3, and .wk4
Microsoft Exchange All versions
Delimited text files (delimited text file: A file containing data where individual field values are separated by a character, such as comma or a tab.) All character sets
Fixed-width text files (fixed-width text file: A file containing data, where each field has a fixed width.) All character sets
HTML (HTML: The standard markup language used for documents on the World Wide Web. HTML uses tags to indicate how Web browsers should display page elements such as text and graphics and how to respond to user actions.) 1.0 (if a list)
2.0, 3.x (if a table or list)
XML (Extensible Markup Language (XML): A format for delivering rich, structured data from an application in a standard, consistent way. XML describes the content of a Web document, whereas HTML tags describe how the document looks.) Documents All versions
SQL tables, Microsoft Visual FoxPro, and data from other programs and databases that support the ODBC (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) protocol Visual FoxPro 2.x, 3.0, 5.0, and 6.x (import (import: To copy data from a text file, spreadsheet, or database table into an Access table. You can use the imported data to create a new table, or you can append (add) it to an existing table that has a matching data structure.) only)
For an up-to-date list of supported ODBC drivers (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.), see the Microsoft Knowledge Base.



Using a database for the first time

Working with databases and database objects can be a daunting task when you first get started. The following information should help you become more familiar with the components that make up a Microsoft Access database.

Databases: What they are and how they work

A database is a collection of information that's related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection. If your database isn't stored on a computer, or only parts of it are, you may be tracking information from a variety of sources that you're having to coordinate and organize yourself.

For example, suppose the phone numbers of your suppliers are stored in various locations: in a card file containing supplier phone numbers, in product information files in a file cabinet, and in a spreadsheet containing order information. If a supplier's phone number changes, you might have to update that information in all three places. In a database, however, you only have to update that information in one place — the supplier's phone number is automatically updated wherever you use it in the database.

Access database files

Using Microsoft Access, you can manage all your information from a single database file. Within the file, you can use:

Tables to store your data.
Queries to find and retrieve just the data you want.
Forms to view, add, and update data in tables.
Reports to analyze or print data in a specific layout.
Data access pages to view, update, or analyze the database's data from the Internet or an intranet.


Store data once in one table, but view it from multiple locations. When you update the data, it's automatically updated everywhere it appears.

Display data in a query

Display data in a form

Display data in a report

Display data in a data access page

Tables and relationships

To store your data, create one table for each type of information that you track. To bring the data from multiple tables together in a query, form, report, or data access page, define relationships between the tables.



Customer information that once existed in a mailing list now resides in the Customers table.

Order information that once existed in a spreadsheet now resides in the Orders table.

A unique ID, such as a Customer ID, distinguishes one record from another within a table. By adding one table's unique ID field to another table and defining a relationship, Microsoft Access can match related records from both tables so that you can bring them together in a form, report, or query.

Queries

To find and retrieve just the data that meets conditions that you specify, including data from multiple tables, create a query. A query can also update or delete multiple records at the same time, and perform predefined or custom calculations on your data.



This query accesses separate tables to retrieve the Order ID, Required Date, Company Name, and City information for customers in London whose orders were required in April.

Forms

To easily view, enter, and change data directly in a table, create a form. When you open a form, Microsoft Access retrieves the data from one or more tables, and displays it on the screen with the layout you choose in the Form Wizard, or with the layout that you created on your own in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).



A table displays many records at the same time, but you might have to scroll to see all of the data in a single record. Also, when viewing a table, you can't update data from more than one table at the same time.

A form focuses on one record at a time, and it can display fields from more than one table. It can also display pictures and other objects.

A form can contain a button that prints, opens other objects, or otherwise automates tasks.

Reports

To analyze your data or present it a certain way in print, create a report. For example, you might print one report that groups data and calculates totals, and another report with different data formatted for printing mailing labels.



Use a report to create mailing labels.

Use a report to show totals in a chart.

Use a report to calculate totals.

Data access pages

To make data available on the Internet or an intranet for interactive reporting, data entry, or data analysis, use a data access page. Microsoft Access retrieves the data from one or more tables and displays it on the screen with the layout that you created on your own in Design view, or with the layout you chose in the Page Wizard.



Click the expand indicator ...

... to display the data and record navigation toolbar for the next level of detail.

Use the record navigation toolbars to move to, sort, and filter records, and to get Help.


Tables: Store and manage your data

A table is a collection of data about a specific topic, such as products or suppliers. Using a separate table for each topic means that you store that data only once. This results in a more efficient database and fewer data-entry errors.

How data is organized in tables

Tables organize data into columns (called fields) and rows (called records).



For example, each field in a Products table contains the same type of information for every product, such as the product's name. Each record in that table contains all the information about one product, such as the product's name, supplier ID number, units in stock, and so on.

Table Design view

In table Design view, you can create an entire table from scratch, or add, delete, or customize the fields in an existing table.



If you want to track additional data in a table, add more fields. If an existing field name isn't descriptive enough, you can rename the field.

Setting a field's data type (field data type: A characteristic of a field that determines what kind of data it can store. For example, a field whose data type is Text can store data consisting of either text or number characters, but a Number field can only store numerical data.)defines what kind of values you can enter in a field. For example, if you want a field to store numerical values that you can use in calculations, set its data type to Number or Currency.

You use a unique tag, called a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.), to identify each record in your table. A table's primary key is used to refer to related records in other tables.

Field properties are a set of characteristics that provide additional control over how the data in a field is stored, entered, or displayed. Which properties are available depends on a field's data type.

How to relate two tables

A common field relates two tables so that Microsoft Access can bring together the data from the two tables for viewing, editing, or printing. In one table, the field is a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) that you set in table Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.). That same field also exists in the related table as a foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.).



In the Suppliers table, you enter a supplier ID, company name, and so on, for each supplier. SupplierID is the primary key that you set in table Design view.

In the Products table, you include the SupplierID field, so that when you enter a new product, you can identify its supplier by entering that supplier's unique ID number. SupplierID is the foreign key in the Products table.

Table Datasheet view

In a table or query, Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.) provides the tools you need to work with data.

Using the Table Datasheet and Query Datasheet toolbars

The Table Datasheet and Query Datasheet toolbars (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, use the Customize dialog box (point to Toolbars on the View menu and click Customize). To see more buttons, click Toolbar Options at the end of the toolbar.) provide many of the tools you need to find, edit, and print records.



Print or preview data

Check spelling

Cut, copy, or paste selected text, fields, whole records, or the entire datasheet

Sort records

Filter records, and find or replace values

Add or delete records

Working with columns, rows, and subdatasheets

You can find tools for working with columns, rows, and subdatasheets (subdatasheet: A datasheet that is nested within another datasheet and that contains data related or joined to the first datasheet.) in the datasheet itself, or by right-clicking a column selector (column selector: The horizontal bar at the top of a column. You can click a column selector to select an entire column in the query design grid or the filter design grid.).


Use the column selector to move, hide, or rename a column.

Resize columns or rows.

Use subdatasheets to view related data.

Freeze the leftmost column so that it is displayed as you scroll to the right.

Moving through records

You can use the navigation toolbar to move through the records in a datasheet.


Go to the first record.

Go to the previous record.

Type a record number to move to.

Go to the next record.

Go to the last record.

Go to a blank (new) record.

Queries: Getting answers to your questions

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 (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.). 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 (criteria: Conditions you specify to limit which records are included in the result set of a query or filter. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.)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 (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.)or a history table that contains old records.
SQL queries

An SQL query is a query you create by using an SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.). 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 (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), 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 (SQL view: A window that displays the SQL statement for the current query or is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, Access constructs the SQL equivalent 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 (SQL-specific query: A query that consists of an SQL statement. Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.), can't be created in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.). For pass-through (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of having the Microsoft Jet database engine process the data.), data-definition (data-definition query: A SQL-specific query that contains data definition language (DDL) statements. These statements allow you to create or alter objects in the database.), and union queries (union query: A query that uses the UNION operator to combine the results of two or more select queries.), you must create the SQL statements directly in SQL view. For subqueries (subquery: An SQL SELECT statement inside another select or action query.), you enter the SQL in the Field row or the Criteria row of the query design grid.


Forms: Putting a friendly face on your data

A form is a type of a database object that is primarily used to enter or display data in a database. You can also use a form as a switchboard that opens other forms and reports in the database, or as a custom dialog box that accepts user input and carries out an action based on the input.



A data-entry form

A switchboard form

A custom dialog box

Most forms are bound to one or more tables and queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) in the database. A form's record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) refers to the fields in the underlying tables and queries. A form need not contain all the fields from each of the tables or queries that it is based on.

A bound form stores or retrieves data from its underlying record source. Other information on the form, such as the title, date, and page number, is stored in the form's design.



Graphic elements, such as lines and rectangles, are stored in the form's design.

Data comes from the fields in the underlying record source.

A calculation comes from an expression, which is stored in the form's design.

Descriptive text is stored in the form's design.

You create a link between a form and its record source by using graphical objects called controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). The most common type of control used to display and enter data is a text box.



Labels display descriptive text.

Text boxes display data from and enter data into the Products table.

A text box uses an expression to calculate a total.

You can also open a form in PivotTable view or PivotChart view to analyze data. In these views, you can dynamically change the layout of a form to present data in different ways. You can rearrange row headings, column headings, and filter fields until you achieve the desired layout. Each time you change the layout, the form immediately recalculates the data based on the new arrangement.



In PivotTable view, you can view detail or summarized data by arranging fields in the filter, row, column, and detail areas.

In PivotChart view, you can display data visually by selecting a chart type and viewing data by arranging fields in the filter, series, category, and data areas.

Creating a form

You can create a form quickly by using the AutoForm command or a wizard. AutoForm creates a form that displays all fields and records in the underlying table or query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.). A wizard asks you questions and creates a form based on your answers. You can then customize the form the way you want it in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).

Customizing a form

In Design view

You can customize a form in Design view in the following ways:

Record source Change the tables and queries that a form is based on.

Controlling and assisting the user You can set form properties to allow or prevent users from adding, deleting, or editing records displayed in a form. You can also add custom Help to a form to assist your users with using the form.

Form window You can add or remove Maximize and Minimize buttons, short cut menus, and other Form window elements.

Sections You can add, remove, hide, or resize the header, footer, and details sections of a form. You can also set section properties to control the appearance and printing of a form.

Controls You can move, resize, or set the font properties of a control. You can also add controls to display calculated values, totals, current date and time, and other useful information on a form.

In PivotTable or PivotChart view

You can customize a form in PivotTable or PivotChart view in the following ways:

Add, move, or remove fields You can add fields to the filter, row, column, and detail areas in PivotTable view, and to the filter, category, series, and data areas in PivotChart view. You can also move fields from one area to another and remove fields from the view.

Filter records You can filter data displayed in the view by adding or moving a field to the filter area. You can also filter a field in the row and column area.

Sort records You can sort items in row or column fields in ascending or descending order. You can also sort items in custom order in PivotTable view.

Group records You can group items in row or column fields on intervals, or create custom groups.

Format elements and change captions In PivotTable view, you can change the font settings, alignment, background color, and number format of a field. You can also change the captions of fields and custom groups. In PivotChart view, you can change the chart type, format data markers, and more.

Reports: Presenting your data to the world

A report is an effective way to present your data in a printed format. Because you have control over the size and appearance of everything on a report, you can display the information the way you want to see it.



Create mailing labels.

Show totals in a chart.

Group records into categories.

Calculate totals.

Most reports are bound to one or more table and query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) in the database. A report's record source refers to the fields in the underlying tables and queries. A report need not contain all the fields from each of the tables or queries that it is based on.

A bound report gets its data from its underlying record source. Other information on the form, such as the title, date, and page number, is stored in the report's design.



The report title and column headings are stored in the report's design.

The date comes from an expression stored in the report's design.

Data comes from fields in the underlying table, query, or SQL statement.

Totals come from expressions, which are stored in the report's design.

You create the link between a report and its record source by using graphical objects called controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). Controls can be text boxes that display names and numbers, labels that display titles, or decorative lines that graphically organize the data and make the report more attractive.



Lines are decorative.

Labels display descriptive text.

Text boxes display data from the Sales by Date query.

A text box uses an expression to calculate a total.

Creating a report

You can create different types of reports quickly by using wizards. Use the Label Wizard to create mailing labels, the Chart Wizard to create charts, or the Report Wizard to create a standard report. The wizard asks you questions and creates a report based on your answers. You can then customize the report the way you want it in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).

Customizing a report

You can customize a report in the following ways:

Record source Change the tables and queries that a report is based on.
Sorting and grouping data You can sort data in ascending or descending order. You can also group records on one or more fields, and display subtotals and grand totals on a report.

Report window You can add or remove Maximize and Minimize buttons, change the title bar text, and other Report window elements.

Sections You can add, remove, hide, or resize the header, footer, and details sections of a report. You can also set section properties to control the appearance and printing of a report.

Controls You can move, resize, or set the font properties of a control. You can also add controls to display calculated values, totals, current date and time, and other useful information on a report.

Data access pages: Publishing your data to the Web

A data access page is a special type of Web page designed for viewing and working with data from the Internet or an intranet — data that is stored in a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) or a Microsoft SQL Server database (Microsoft SQL Server database: A database in Microsoft SQL Server, it consists of tables, views, indexes, stored procedures, functions, and triggers.). The data access page may also include data from other sources, such as Microsoft Excel.

A page in Page view or Microsoft Internet Explorer 5 or later

Using a data access page is similar to using a form: You can view, enter, edit, and delete data in a database. However, you can also use a page outside a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.), so users can update or view data over the Internet or an intranet.

The following illustration shows how the Product data access page, which is used for entering and updating product information, looks in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Internet Explorer 5.0 or later.) or in Microsoft Internet Explorer 5 or later.



Enter product information in the text boxes, drop-down list box, and check box.

Use the record navigation toolbar to navigate, add, delete, save, sort, and filter records, and to get Help.

A page in Design view

The following illustration shows the Products data access page in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).



Title in the body of the page

Group header for the Products group level

Record navigation section for the Products group level

Parts of a data access page

The body The body is the basic design surface of a data access page. On a page that supports data entry, you can use it to display informational text, controls bound to data, and sections.

Sections You use sections to display text, data from a database, and toolbars.

Two types of sections (section: A part of a form, report, or data access page such as a header, footer, or detail section.) are typically used on pages that support data entry: group header and record navigation sections. A page can also have footer and caption sections.

Group header and footer Used to display data and calculate values.
Record navigation Used to display the record navigation control (record navigation control: A control used on a data access page to display a record navigation toolbar. In a grouped page, you can add a navigation toolbar to each group level. You can customize the record navigation control by changing its properties.) for the group level. A record navigation section for a group appears after the group header section. You can't place bound controls (bound control: A control used on a form, report, or data access page to enter or display the contents of a field in the underlying table, query, or SQL statement. The control's ControlSource property stores the field name to which the control is bound.) in a record navigation section.
Caption Used to display captions for text boxes and other controls. It appears immediately before the group header. You can't place bound controls in a caption section.
Each group level in a data access page has a record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.). The name of the record source is displayed on the section bar (section bar: The horizontal bar above a data access page section in Design view. The section bar displays the type and name of the section. Use it to access the section's property sheet.) for each section used for a group level.

Designing different types of data access pages

You design data access pages in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) in Microsoft Access. The page is a separate file that is stored outside Access; however, when you create the file, Access automatically adds a shortcut to the file in the Database window. Designing a data access page is similar to designing forms and reports — you use a field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.), the toolbox (toolbox: A set of tools that are available in Design view to add controls to a form, report, or data access page. The toolset available in page Design view is different from the toolset available in form and report Design view.), controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.), and so on. However, there are some significant differences in the way that you design and interact with data access pages as opposed to forms and reports. How you design the page depends on what it will be used for.

Interactive reporting This type of data access page is often used to consolidate and group information that is stored in the database, and then publish summaries of the data. For example, a page might publish the sales performance for each region in which you do business. Using expand indicators (expand indicator: A button that is used to expand or collapse groups of records; it displays the plus (+) or minus (–) sign.), you can go from a general summary of the information, such as a list of all the regions and their combined sales total, to specific details on individual sales within each region. The data access page might provide toolbar buttons for sorting and filtering (filter: A set of criteria applied to data in order to display a subset of the data or to sort the data. In Access, you can use filtering techniques, such as Filter By Selection and Filter By Form to filter data.) the data, as well as for adding, editing, and deleting the data in some or all group levels (group level: The depth at which a group in a report or data access page is nested inside other groups. Groups are nested when a set of records is grouped by more than one field, expression, or group record source.).
Data analysis This type of data access page may include a PivotTable list (PivotTable list: A Microsoft Office Web Component used to analyze data interactively on a Web page. Data displayed in a row and column format can be moved, filtered, sorted, and calculated in ways that are meaningful for your audience.), similar to a Microsoft Excel PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources including ones external to Excel.), that lets you reorganize the data to analyze it in different ways. The page might contain a chart that you can use to analyze trends, detect patterns, and compare data in your database. Or it might contain a spreadsheet, in which you can enter and edit data and use formulas to calculate as you do in Excel.
Using data access pages in Internet Explorer

A data access page is connected directly to a database. When users display the data access page in Internet Explorer, they are viewing their own copy of the page. That means any filtering, sorting, and other changes they make to the way the data is displayed — including changes they make within a PivotTable list (PivotTable list: A Microsoft Office Web Component used to analyze data interactively on a Web page. Data displayed in a row and column format can be moved, filtered, sorted, and calculated in ways that are meaningful for your audience.) or spreadsheet (Spreadsheet Component: A Microsoft Office Web Component that provides the interactive functionality of a spreadsheet on a Web page. You can enter data, add formulas and functions, apply filters, change formatting, and recalculate right on the Web page.) — affect only their copy of the data access page. However, changes that they make to the data itself — such as modifying values, and adding or deleting data — are stored in the underlying database, and therefore are available to everyone viewing the data access page.

Users get Help on how to work with the page in Internet Explorer by clicking the Help button on the record navigation toolbar. The Help file that appears is automatically included with any data access page that's published with a record navigation toolbar. If you delete the record navigation toolbar or if you disable its Help button, you should provide instructions for using the page to those who will use the page.

Note To view and work with the data access page on the Internet or an intranet, users need Internet Explorer 5 or later.

Using data access pages in Microsoft Access

You can also work with a data access page in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Internet Explorer 5.0 or later.) in Access. Data access pages can supplement the forms and reports that you use in your database application. When deciding whether to design a data access page, form, or report, consider the tasks that you want to perform.

You get Help on how to work with the page in Page view by clicking the Help button on the Page View toolbar to display Access Help. Clicking the Help button on a record navigation toolbar on the page displays Help for working with the page in Microsoft Internet Explorer. As mentioned previously, this Help file is automatically included with any data access page that's published with a record navigation toolbar. You can delete the Help button on pages that are used only within Access, or you can modify the button to provide custom Help.

Where a data access page gets its data

Types of data sources for a data access page

Microsoft Access database, or a Microsoft SQL Server database (Microsoft SQL Server database: A database in Microsoft SQL Server, it consists of tables, views, indexes, stored procedures, functions, and triggers.) version 6.5 or later. To design a page using data from one of these databases, the page must be connected to the database. If you have already opened an Access database or an Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.) that's connected to a SQL Server database, the data access page you create automatically connects to the current database and stores that path in the ConnectionString property of the data access page. When a user browses to the page in Microsoft Internet Explorer 5 or displays the page in Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Internet Explorer 5.0 or later.), it displays current data from the underlying database by using the path defined in the ConnectionString property. If you design the data access page while the database is on a local drive, Access will use the local path, which means the data won't be accessible to others. For this reason, it's important that you move or copy the database to a network location that's accessible to users of your page. Once the database is on the network share, open the database using the UNC (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename. ) address. If you move or copy the database after you've already designed the page, you'll have to update the path in the ConnectionString property to point to the new location.

Instead of having to update the ConnectionString property of each page in the database separately, you can choose to create a connection file. A connection file stores the connection information for a data access page, and can be shared between multiple data access pages. When you open a data access page that uses a connection file, the page reads the connection file and connects to the appropriate database. After creating the connection file, if you move or copy the database, all you have to do is edit the connection information in the connection file.

Types of data sources for Microsoft Office Web Components on a data access page

Although the data access page gets its data from a Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) or Microsoft SQL Server database (Microsoft SQL Server database: A database in Microsoft SQL Server, it consists of tables, views, indexes, stored procedures, functions, and triggers.), Microsoft Office Web Component (Microsoft Office Web Components: Interactive components, such as worksheets, charts, and PivotTable lists on Web pages that facilitate data analysis. To use these components, you must have a Microsoft Office XP license.) controls on the page can display data from either of these databases or from other sources. For example, the page may contain a PivotTable list (PivotTable list: A Microsoft Office Web Component used to analyze data interactively on a Web page. Data displayed in a row and column format can be moved, filtered, sorted, and calculated in ways that are meaningful for your audience.), spreadsheet (Spreadsheet Component: A Microsoft Office Web Component that provides the interactive functionality of a spreadsheet on a Web page. You can enter data, add formulas and functions, apply filters, change formatting, and recalculate right on the Web page.), or chart (chart: A graphical representation of data in a form, report, or data access page.) with data from a Microsoft Excel worksheet or a database other than Access or SQL Server. Depending on the data source, the data in these controls may be a snapshot of the original data or it may be live data displayed through a separate connection that the control has with its data source.

To create an Office Web Component with data from a source other than the current database, you might have to use a program other than Access to first publish the data to a Web page. For example, to create a PivotTable list that contains data from an Excel worksheet, you have to publish the data from Excel. Then, in Access, you can open the Web page and turn it into a data access page by adding controls that are bound to an Access or SQL Server database. You can further customize the page by adding labels, pictures, or other features to enhance the page's appearance. From within Access, you can create a PivotTable list that uses data from an external source other than Excel.

Macros: Putting your data to work

Macros are a set of actions that you can create to help you to automate common tasks. By using groups of macros, you can perform several tasks at once.

What is a macro?

A macro is a set of one or more actions (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.) that each perform a particular operation, such as opening a form or printing a report. Macros can help you to automate common tasks. For example, you can run a macro that prints a report when a user clicks a command button.



When you create a macro, you enter the actions you want to carry out in this portion of the Macro window (Macro window: The window in which you create and modify macros.).

You can specify arguments for an action in this portion of the window.

A macro can be one macro composed of a sequence of actions, or it can be a macro group (macro group: A collection of related macros that are stored together under a single macro name. The collection is often referred to simply as a macro.). You can also use a conditional expression ( conditional expression: An expression that's evaluated and compared to a value — for example, If...Then and Select Case statements. If the condition is met, one or more operations are performed. If it isn't met, the operation is skipped.) to determine whether in some cases an action will be carried out when a macro runs.

The following macro is composed of a series of actions. Microsoft Access carries out these actions each time the macro runs. To run this macro, you refer to the macro name Review Products.



What is a macro group?

If you have numerous macros, grouping related macros in macro groups (macro group: A collection of related macros that are stored together under a single macro name. The collection is often referred to simply as a macro.)can help you to manage your database more easily.

For example, the following macro group, named Buttons, is made up of three related macros: Employees, Products, and Reps. Each macro carries out the OpenForm action, and the Products macro also carries out the MoveSize action.



The name in the Macro Name column identifies each macro. When you run a macro in a macro group, Microsoft Access carries out the action in the action column and any actions that immediately follow with a blank Macro Name column.

You can run a macro in a macro group in an event or event procedure (event procedure: A procedure automatically executed in response to an event initiated by the user or program code, or triggered by the system.) by typing the macro group name followed by a period and then the macro name. In the preceding example, to refer to the Employees macro in the Buttons macro group, you would type Buttons.Employees.

Conditional actions

In some cases, you may want to carry out an action or series of actions in a macro only if a particular condition (condition: Part of the criteria that a field must meet for searching or filtering. Some conditions must be used with a value; for example, the field Author with the condition equals with the value Jane. Author equals, by itself, would be incomplete.) is true. For example, if you're using a macro to validate data in a form, you might want to display one message in response to one set of values entered in a record and another message in response to a different set of values. In cases like these, you can use conditions to control the flow of the macro.

A condition is a logical expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that evaluates to True/False or Yes/No. The macro follows different paths depending on whether the condition is true or false.

When you run the macro, Microsoft Access evaluates the first conditional expression ( conditional expression: An expression that's evaluated and compared to a value — for example, If...Then and Select Case statements. If the condition is met, one or more operations are performed. If it isn't met, the operation is skipped.). If the condition is true, Microsoft Access carries out the action in that row and any of the immediately following actions that are preceded by an ellipsis (...) in the Condition column.

Microsoft Access then runs any additional actions in the macro that have a blank Condition column until it reaches another expression, a macro name, or the end of the macro.

If the condition is false, Microsoft Access ignores the action and any immediately following actions that are preceded by an ellipsis in the Condition column and moves to the next action row (action row: A row in the upper part of the Macro window in which you enter macro names, actions, conditions, and comments associated with a particular macro or macro group.) that contains another condition or a blank Condition column.

The following macro runs the MsgBox and the StopMacro actions only when the expression in the Condition column is true (when there is a Null (Null: A value you can enter in a field or use in expressions and queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null values.) value in the SupplierID field).



Action arguments

Action arguments are additional information required by some macro actions — for example, the object affected by the action or special conditions under which the action is carried out. After you add an action to a macro, you set the arguments for the action in the lower portion of the Macro window (Macro window: The window in which you create and modify macros.). These arguments give Microsoft Access additional information on how to carry out the action.

Tips for setting action arguments:

In general, it's a good idea to set action arguments in the order they're listed, because choices for one argument may determine those for arguments that follow.
If you add an action to your macro by dragging a database object (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) from the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), Microsoft Access automatically sets appropriate arguments for that action.
If an action has an argument that calls for the name of a database object, you can set the argument and the corresponding object type argument automatically by dragging the object from the Database window to the argument box.
You can use an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) preceded by an equal sign (=) to set many action arguments.

Modules: Automate routine tasks and create business solutions

A module is essentially a collection of declarations, statements, and procedures stored together as one named unit to organize your Microsoft Visual Basic (Microsoft Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.)code. Microsoft Access has two types of modules: standard modules (standard module: A module in which you can place Sub and Function procedures that you want to be available to other procedures throughout your database.)and class modules (class module: A module that can contain the definition for a new object. Each instance of a class creates a new object. Procedures defined in the module become properties and methods of the object. Class modules can exist alone or with forms and reports.).

What is a module?

A module is a collection of Visual Basic declarations and procedures that are stored together as a unit.



Object box

Procedure box

Declarations

Procedures

Procedure View button

Full Module View button

Class modules

Form (form module: A module that includes code for all event procedures triggered by events occurring on a specific form or its controls.) and report modules (report module: A module that includes code for all event procedures triggered by events occurring on a specific report or its controls.) are class modules that are associated with a particular form or report. Form and report modules often contain event procedures (event procedure: A procedure automatically executed in response to an event initiated by the user or program code, or triggered by the system.) that run in response to an event on the form or report. You can use event procedures to control the behavior of your forms and reports, and their response to user actions, such as clicking a command button.

When you create the first event procedure for a form or report, Microsoft Access automatically creates an associated form or report module.



To view or add a form procedure, choose one of the form's objects from the Object box ...

... and then choose an event from the Procedure box. Names of events that already have procedures are bold.

Procedures in your form and report modules can call procedures you have added to standard modules.

In Access 97 or later, class modules can also exist independent of a form or report, and this type of class module is listed in the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.). You can use a class module to create a definition for a custom object. In Access 95, class modules exist in association only with a form or report.

Standard modules

Standard modules contain general procedures that aren't associated with any other object, and frequently used procedures that can be run from anywhere within your database. The major difference between a standard module and a class module that is not associated with a particular object is one of scope and lifetime. The value of any variables or constants that are declared or exist in a class module without an associated object are available for use only while that code is running and only from that object.



Function name and arguments

Visual Basic declarations and code statements

Standard modules are listed in Modules under Objects in the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.). Form, report, and standard modules are also listed in the Object Browser (Object Browser: A dialog box that displays information about objects, properties, methods, and constants in the current project and in referenced object libraries, and that you use to search for an element, get help on it, or paste it into a module.).


Relationships in a database

After you've set up different tables for each subject in your Microsoft Access database (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.), you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships (relationship: An association established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. For example, this form includes information from four tables:



The Customers table

The Orders table

The Products table

The Order Details table

How relationships work

In the previous example, the fields in four tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. A relationship works by matching data in key fields — usually a field with the same name in both tables. In most cases, these matching fields are the primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) from one table, which provides a unique identifier for each record, and a foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) in the other table. For example, employees can be associated with orders they're responsible for by creating a relationship between the EmployeeID fields.



EmployeeID appears in both tables—-as a primary key ...

... and as a foreign key.

A one-to-many relationship

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.




One supplier ...

... can supply more than one product ...

... but each product has only one supplier.

A many-to-many relationship

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) consists of two fields — the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.




Primary key from the Orders table

Primary key from the Products table

One order can have many products ...

... and each product can appear on many orders.

A one-to-one relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game. Each soccer player in the Soccer Players table has one matching record in the Employees table.



Each soccer player has one matching record in the Employees table.

This set of values is a subset of the EmployeeID field and the Employees table.

About defining relationships

The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

A one-to-many relationship is created if only one of the related fields is a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or has a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.).
A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields — the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from the two other tables.
You can also create a relationship between a table and itself. This is useful in situations where you need to perform a Lookup within the same table. In the Employees table, for example, you can define a relationship between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

Note If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) line between the tables, but referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) won't be enforced, and there's no guarantee that records are unique in either table.

Referential integrity

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

The matching field from the primary table (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.) is a primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or has a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.).
The related fields have the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).). There are two exceptions. An AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field can be related to a Number field with a FieldSize property setting of Long Integer, and an AutoNumber field with a FieldSize property setting of Replication ID can be related to a Number field with a FieldSize property setting of Replication ID.
Both tables belong to the same Microsoft Access database. If the tables are linked tables (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you can't change its structure.), they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity can't be enforced for linked tables from databases in other formats.
The following rules apply when you use referential integrity:

You can't enter a value in the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) field of the related table that doesn't exist in the primary key of the primary table. However, you can enter a Null (Null: A value you can enter in a field or use in expressions and queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null values.) value in the foreign key, specifying that the records are unrelated. For example, you can't have an order that is assigned to a customer that doesn't exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
You can't change a primary key value in the primary table, if that record has related records. For example, you can't change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.
Cascading updates and deletes

For relationships in which referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) is enforced, you can specify whether you want Microsoft Access to automatically cascade update (cascading update: For relationships that enforce referential integrity between tables, the updating of all related records in the related table or tables when a record in the primary table is changed.) and cascade delete (cascading delete: For relationships that enforce referential integrity between tables, the deletion of all related records in the related table or tables when a record in the primary table is deleted.) related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key (primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) values in a primary table (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.), Microsoft Access makes necessary changes to related tables to preserve referential integrity.

If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship isn't broken. Microsoft Access cascades updates without displaying any message.

Note If the primary key in the primary table is an AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field, setting the Cascade Update Related Fields check box will have no effect, because you can't change the value in an AutoNumber field.

If you select the Cascade Delete Related Records check box when defining a relationship, any time you delete records in the primary table, Microsoft Access automatically deletes related records in the related table. For example, if you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table (this includes records in the Order Details table related to the Orders records). When you delete records from a form or datasheet with the Cascade Delete Related Records check box selected, Microsoft Access warns you that related records may also be deleted. However, when you delete records using a delete query (delete query: A query (SQL statement) that removes rows matching the criteria you specify from one or more tables.), Microsoft Access automatically deletes the records in related tables without displaying a warning.
Read More..