ACC2000: Recordsets May Differ for a Data Access Page Depending on How You Build the Page (256853)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q256853
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

The method that you use to build a grouped data access page influences the type of recordset that is created for the page. There are three possible underlying recordsets. The difference among these recordsets is in the type of relationship that is created between the tables used in the record source. The number of records may also differ among these recordsets.

CAUSE

This behavior is by design. It allows you to decide which type of join the page is based on without creating an underlying query.

MORE INFORMATION

There are four methods that you can use to create a grouped data access page. They are:
  • Dragging fields from the field list to the page
  • Using the Group by Table feature
  • Using the Page Wizard
  • Using the Group by Promote feature
The first two methods create identical recordsets. The other two methods create a second and third type of recordset. These different recordsets are created even when the respective pages are based on the same tables.

The number of records returned by the pages is directly effected by the method that you use to build the page. The only way that you can determine the underlying recordset for a page is to retrieve this value programmatically. Visually, you cannot really determine how a completed page was built. Additionally, after you have built a page, there is really no good way to change the underlying recordset. Therefore, if you want to change the underlying recordset, you must rebuild the entire page.

The following four examples describe the different methods that you can use to create a grouped data access page. The examples also show the recordset that results from each method, including the number of records returned. Each example uses the Customers and Orders tables in the sample database Northwind.mdb as the source for each page.

Method 1: Dragging Fields from the Field List to the Page

  1. In the sample database Northwind.mdb, click Pages, and then click New.
  2. In the New Data Access Page dialog box, click Design View, and then click OK.
  3. If the field list is not displayed, click Field List on the View menu.
  4. Drag the following fields from the Customers table from the field list to the Unbound section of the page:

    CustomerID
    CompanyName

  5. Drag the OrderID field from the Orders table from the field list to the bottom of the page section until the mouse pointer changes to a line with arrows on each end. After you drop the field, a new section called Header: Orders is created, and the OrderID field is placed in this section.
  6. Drag the OrderDate field from to the Orders table from the field list to the Header: Orders section.
This drag-and-drop method creates the following record source, which returns a total of 91 records:

SHAPE
{ SELECT [CustomerID], [CompanyName] FROM [Customers] } AS [Customers]
APPEND ( { SELECT [OrderID], [CustomerID] AS [CustomerID1], [OrderDate] FROM [Orders]
WHERE ([CustomerID] = ?) }
AS [Orders]
RELATE [CustomerID] TO PARAMETER 0 ) AS [Orders]

Method 2: Group by Table

  1. In the sample database Northwind.mdb, click Pages under Objects, and then click New.
  2. In the New Data Access Page dialog box, click Design View, and then click OK.
  3. If the field list is not displayed, click Field List on the View menu.
  4. Drag the following fields from the Customers and Orders tables from the field list to the Unbound section of the page:

    CustomerID
    CompanyName
    OrderId
    OrderDate

  5. Select the CustomerID field on the page, and then click the Group By Table button. Note that two sections are created on the page: the Header: Customers and the Header: Orders sections.
The Group by Table method creates the following record source, which returns a total of 91 records:

SHAPE
{ SELECT [CustomerID], [CompanyName] FROM [Customers] } AS [Customers]
APPEND ( { SELECT [OrderID], [CustomerID] AS [CustomerID1], [OrderDate]
FROM [Orders]
WHERE ([CustomerID] = ?) }
AS [Orders] RELATE [CustomerID] TO PARAMETER 0 ) AS [Orders]

Method 3: Page Wizard

  1. In the sample database Northwind.mdb, click Pages under Objects, and then click New.
  2. In the New Data Access Page dialog box, click Page Wizard, click Customers as the table on which to base the page, and then click OK.
  3. In the Page Wizard dialog box, add the following field to the page:

    CustomerID
    CompanyName

  4. Change the name in the Tables/Queries box to Table: Orders, and then add the following fields:

    OrderID
    OrderDate

  5. Click Next, choose to group by CustomerID, and then click Finish.
The Page Wizard method creates the following record source, which returns a total of eighty-nine records:

SHAPE
( SHAPE {SELECT [Customers].[CustomerID], [Customers].[CompanyName], [Orders].[OrderID], [Orders].[OrderDate]
FROM ([Customers]
INNER JOIN [Orders] ON [Customers].[CustomerID] =[Orders].[CustomerID]) } AS [CustomersWiz] )
COMPUTE [CustomersWiz] BY [CustomerID] AS [GroupOfOrders-CustomerID]

Method 4: Group by Promote

  1. In the sample database Northwind.mdb, click Pages under Objects, and then click New.
  2. In the New Data Access Page dialog box, click Design View, and then click OK.
  3. If the field list is not displayed, click Field List on the View menu.
  4. Drag the following fields from the Customers and Orders tables from the field list to the Unbound section of the page:

    CustomerID
    CompanyName
    OrderId
    OrderDate

  5. Select the CustomerID field on the page, and then click the Promote button. Note that a new section named Header: Orders-CustomerID is created on the page.
The Promote method creates the following record source, which returns a total of eighty-nine records:

SHAPE
( SHAPE { SELECT [Orders].[OrderID], [Orders].[CustomerID] AS
[CustomerID1], [Orders].[OrderDate], [Customers].[CustomerID],
[Customers].[CompanyName]
FROM [Orders]
LEFT OUTER JOIN [Customers] ON [Orders].[CustomerID] =
[Customers].[CustomerID] }
AS [Orders] )
COMPUTE [Orders] BY [CustomerID] AS [GroupOfCustomerID], [CompanyName] AS
[GroupOfCompanyName]

Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbDAP kbprb KB256853
©2004 Microsoft Corporation. All rights reserved.