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.