group totals

[pwicr]

I'm running a query that I want a grand total for as well as a group subtotal for the various groups within the results. I don't want a report as it is harder to filter etc for the person I am creating the query for. is there a way to get this result in datasheet view?

 

[plog]

I bet some masochist could spend a few hours building sub-queries and sub-sub-queries and then one master union table to get this just like you want it. So the answer is technically yes.

Most likely someone with that ability and knowledge would just use a report. Would ordering the in Access like you want, dumping it into Excel and then going through row by row adding totals and sub-totals where necessary work?

 

[Pat Hartman]

Filtering reports is easy enough if you know how and is a better way to present data to the user. In fact, if you are using A2010 (and maybe A2007), the interface gives you this ability and you don't even have to write code to do it.

The simple way to filter if you want/need to do it yourself is to build a form. On the form, you place textboxes and combos that will hold the selection criteria. If you have many reports that use the same criteria, you can add an option group or a combo for those also. Then in the run button, you build the statement that opens the report you want with the criteria you want.

Dim stReport as String
Dim stCriteria as String

Select Case Me.frReports   'Option group method
    Case 1
        stReport = "report1"
    Case 2
        stReport = "report2"
    Case Else
        stReport = "report3"
End Select

If Not IsNull(Me.PersonID) Then
    stCriteria = "PersonID = " & Me.PersonID
End If
If Not IsNull(Me.StartDate) Then
    If stCriteria = "" Then
        stCriteria = "SomeDate >= #" & Me.StartDate & "#"
    Else
        stCriteria = stCriteria & " AND SomeDate >= #" & Me.StartDate & "#"
End If

If stCriteria = "" Then
    DoCmd.OpenReport stReport, acViewPreview
Else
    DoCmd.OpenReport stReport, acViewPreview,, stCriteria
End If

 

[crxftw]

Maybe Crosstab query helps you?