sub total screen by screen

does anyone know how - using a tabular form - i can display sub totals at the
bottom of a screen - eg after 20 records. then have the user press a button
to display the next 20. the records are not grouped and the user does not
want a main and subform.
any thoughts would be most appreciated
--
Alan

Hi Alan,

One approach is to base the form on a query that includes a calculated
field that returns a "group number" value that increments every 20
records, and uses a parameter to control which batch of 20 records is
returned. Here's an example that works with the Orders table in the
Northwind sample database:

SELECT
A.OrderID,
A.CustomerID,
(SELECT
COUNT(B.OrderID)
FROM Orders AS B
WHERE B.OrderID = A.OrderID) AS Seq,
(SELECT
COUNT(B.OrderID) \ 20 + 1
FROM Orders AS B
WHERE B.OrderID A.OrderID) AS Grp

FROM Orders AS A

WHERE (SELECT
COUNT(B.OrderID) \ 20 + 1
FROM Orders AS B
WHERE B.OrderID A.OrderID) = [Group Number?]

ORDER BY A.OrderID;

In this example, the subquery in the WHERE clause does the hard work.
The calculated fields [Seq] and [Grp] are just there to show that it
really works (Seq numbers the records from 1 onwards, and Grp generates
the group number). In real life you'd use the actual primary key of your
table in place of OrderID, and probably replace the [Group Number?]
parameter with one that uses the Forms!FormName!ControlName syntax to
get the value from a hidden textbox on your form.

To make the whole thing work, the form would have navigation buttons
that increment or decrement value in the "group number" textbox and then
requery the form. Since the form's recordset would consist of just the
20 records being displayed, you could get the totals in the usual way by
using DSUM() expressions in unbound textboxes in the form's footer.

One drawback of this approach is that the query is not updateable. If
you want to be able to edit the records you'll need to use another
method altogether.


On Sat, 2 Sep 2006 08:08:01 -0700, Alan wrote:

does anyone know how - using a tabular form - i can display sub totals at the
bottom of a screen - eg after 20 records. then have the user press a button
to display the next 20. the records are not grouped and the user does not
want a main and subform.
any thoughts would be most appreciated

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

john, you're a genius! many thanks
alan
--
Alan


"John Nurick" wrote:

Hi Alan,

One approach is to base the form on a query that includes a calculated
field that returns a "group number" value that increments every 20
records, and uses a parameter to control which batch of 20 records is
returned. Here's an example that works with the Orders table in the
Northwind sample database:

SELECT
A.OrderID,
A.CustomerID,
(SELECT
COUNT(B.OrderID)
FROM Orders AS B
WHERE B.OrderID = A.OrderID) AS Seq,
(SELECT
COUNT(B.OrderID) \ 20 + 1
FROM Orders AS B
WHERE B.OrderID A.OrderID) AS Grp

FROM Orders AS A

WHERE (SELECT
COUNT(B.OrderID) \ 20 + 1
FROM Orders AS B
WHERE B.OrderID A.OrderID) = [Group Number?]

ORDER BY A.OrderID;

In this example, the subquery in the WHERE clause does the hard work.
The calculated fields [Seq] and [Grp] are just there to show that it
really works (Seq numbers the records from 1 onwards, and Grp generates
the group number). In real life you'd use the actual primary key of your
table in place of OrderID, and probably replace the [Group Number?]
parameter with one that uses the Forms!FormName!ControlName syntax to
get the value from a hidden textbox on your form.

To make the whole thing work, the form would have navigation buttons
that increment or decrement value in the "group number" textbox and then
requery the form. Since the form's recordset would consist of just the
20 records being displayed, you could get the totals in the usual way by
using DSUM() expressions in unbound textboxes in the form's footer.

One drawback of this approach is that the query is not updateable. If
you want to be able to edit the records you'll need to use another
method altogether.


On Sat, 2 Sep 2006 08:08:01 -0700, Alan wrote:

does anyone know how - using a tabular form - i can display sub totals at the
bottom of a screen - eg after 20 records. then have the user press a button
to display the next 20. the records are not grouped and the user does not
want a main and subform.
any thoughts would be most appreciated

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.