Grouping and Subtotaling

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Most of the apps that I build are reporting tools. I combine data from a
variety of disparate sources, and build analytical reports. Access of course
is a terrific tool for this purpose. I need to be able to subtotal different
categories, such as Date, then Dept within a date, etc. The paper reports are
fine. My question has more to do with forms. In an effort to reduce paper
costs, I build continuous form views of all my reports. Unfortunately, I have
not figured a way to add grouping as you can in reports. So I am stuck with
one grand total in my footers. Anybody have any suggestions?

Any thoughts are appreciated
Rosco
 
Hi, Rosco.

Is there any reason your users can't use Preview mode to look it?
Otherwise, you might consider building your totals in a Totals query, and
joining it to your detail table. Then you could have a main form with a
detail subform to show the subtotal as a normal textbox in the subform footer.

For example, if you had a separate Sales total for each salesman, the
records in the compound query might look like:

SalesID SalesName Amount SumofAmount
--------------- -------------------------- -------------------
-----------------------
1 John $100.00 $300.00
1 John $200.00 $300.00
3 Sue $200.00
$3200.00
3 Sue $1000.00 $3200.00
3 Sue $2000.00 $3200.00

Not sure if that's exactly what you're after. Good luck.

Sprinks
 
do the grouping in the underlying query of your form. you use the 'group
by' keyword in SQL or the sigma button on the query design screen.
 
The thing is there are multiple filtering choices ( upto a dozen) a user can
make to customize the data results. The interface is point and click simple.
Check boxes and list boxes. To make the coding easier, I create a filter from
the user selections and open the results form with the filter directly from
the underlying table. Also I am looking at multiple subtotals.

I think coding multple subforms with underlying groupby queries, properly
nested, and maintaining the filtering capability will be a nightmare.

Something to look at though.

Thanks for the input
Rosco
 
A couple of more thoughts

From the main form which has the check and list boxes create a button to
open your desired form (with an underlying grouping query of all your
records to get your totals)

In the subroutine behind that buttons click event create the WHERE clause in
the docmd.openform command from your checkboxes and listboxes.

Then when the user selects his options and clicks the button, it will open a
form with your totals filtered according to the users wishes. I have some
sample code of a simple one that I have done. If have options that would
display different columns or totals (not just a filter of the records
withstandard totals), you may have to have different forms for that (called
with stDocName) as well as the filtering criteria (with stLinkCriteria).

-------------
Private Sub cmdOpenNewForm_Click()

Dim strProjectName, strPONum As String
Dim stDocName As String
Dim stLinkCriteria As String

'getting parameters from my calling form that I will use to set my filter
criteria
strProjectID = me.txtProjectID
strPONum = me.txtPONum

'making string with filter clause based on form info
If Trim(strPONum) <> "0" Then
stLinkCriteria = "[ProjectID]=" & "'" & strProjectID & "' AND
[POnum] = '" & strPONum & "'"
Else
stLinkCriteria = "[ProjectID]='" & strProjectID & "' "
End If

'calling the form with he filter clause
stDocName = "NewPOrder"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub
---------------------

You could also possible use a similar technique to set a new SQL statement
for the source of your subform and poke that into the subform. but I have
never tried that so it could lead to frustration.
 
Thanks Tom,
I already am creating sql form the filters to execute some other functions,
so I suppose I could expand upon the usage.

Thanks for your thoughts. It's appreciated.
Rosco

Tom Ross said:
A couple of more thoughts

From the main form which has the check and list boxes create a button to
open your desired form (with an underlying grouping query of all your
records to get your totals)

In the subroutine behind that buttons click event create the WHERE clause in
the docmd.openform command from your checkboxes and listboxes.

Then when the user selects his options and clicks the button, it will open a
form with your totals filtered according to the users wishes. I have some
sample code of a simple one that I have done. If have options that would
display different columns or totals (not just a filter of the records
withstandard totals), you may have to have different forms for that (called
with stDocName) as well as the filtering criteria (with stLinkCriteria).

-------------
Private Sub cmdOpenNewForm_Click()

Dim strProjectName, strPONum As String
Dim stDocName As String
Dim stLinkCriteria As String

'getting parameters from my calling form that I will use to set my filter
criteria
strProjectID = me.txtProjectID
strPONum = me.txtPONum

'making string with filter clause based on form info
If Trim(strPONum) <> "0" Then
stLinkCriteria = "[ProjectID]=" & "'" & strProjectID & "' AND
[POnum] = '" & strPONum & "'"
Else
stLinkCriteria = "[ProjectID]='" & strProjectID & "' "
End If

'calling the form with he filter clause
stDocName = "NewPOrder"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub
---------------------

You could also possible use a similar technique to set a new SQL statement
for the source of your subform and poke that into the subform. but I have
never tried that so it could lead to frustration.




Rosco said:
The thing is there are multiple filtering choices ( upto a dozen) a user can
make to customize the data results. The interface is point and click simple.
Check boxes and list boxes. To make the coding easier, I create a filter from
the user selections and open the results form with the filter directly from
the underlying table. Also I am looking at multiple subtotals.

I think coding multple subforms with underlying groupby queries, properly
nested, and maintaining the filtering capability will be a nightmare.

Something to look at though.

Thanks for the input
Rosco
Click to expand...
Click to expand...
 
Back
Top