Count Number of Group Headers, Not Records 1

[perrymans]

Sorry, posted twice to Reports forum, but it doesn't show up???

I have [doc_id] as a Group Header, and it is also in the Details section.

How can I create a count of all of the groupings?

I know I can do it in a query, it just seems that I should be ab;e to do this in the Report somehow.

Thanks. Sean.

 

[TheAceMan1]

How are ya perrymans . . .

Hate to see an old friend slip by! . . . Other info would normally be prompted for, but I believe I have you in hand . . . Perform the following:
[ol][li]Put an unbound textbox in the reports [blue]Report[/blue] or [blue]Page[/blue] section. Name the textbox GroupCnt.[/li]
[li] In the OnOpen event of the report, copy/paste the following ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim Db As DAO.Database, rst As DAO.Recordset
   
   Set Db = CurrentDb
   SQL = "SELECT Count([purple][b][i]FieldName[/i][/b][/purple]) AS Cnt " & _
         "FROM (SELECT [purple][b][i]FieldName[/i][/b][/purple] " & _
               "FROM [purple][b][i]TableName[/i][/b][/purple] " & _
               "GROUP BY [purple][b][i]FieldName[/i][/b][/purple]);"
   Set rst = Db.OpenRecordset(SQL, dbReadOnly)
   
   If rst.BOF Then
      Me!groupCnt = 0
   Else
      Me!groupCnt = rst!Cnt
   End If
   
   Set rst = Nothing
   Set Db = Nothing[/blue]

[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[perrymans]

Thanks AceMan.

I had some crashes after putting this in, I am not sure why though (SQL not defined as string? No () after CurrentDB?)

But all is better now, except...

I have based the SELECT statement on the recordset that is loading the Report. However, this recordset has parameters entered by the user. So I am getting the too few parameters error, even after I moved to the On Page event (which allows me to enter the parameters for the main query).

Any thoughts? Thanks. Sean.

 

[perrymans]

One other thing, I don't want the count, which counts the number of times the doc_id appears, but rather the total number of records.

So in query view, I see there are 24 records.

I need that '24' at the bottom of the report.

I am just suprrised reports don't have an easier way to sum these.

Thanks. Sean.

 

[TheAceMan1]

[blue]I had some crashes after putting this in, . . . (SQL not defined as string? . . .[/blue]

[blue]   Dim Db As DAO.Database, rst As DAO.Recordset
Should be:
   Dim Db As DAO.Database, rst As DAO.Recordset, [purple][b]SQL as String[/b][/purple][/blue]

Sorry about the mess. I'm currently at work and will pick this up later today.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

[blue] . . . I don't want the count, which counts the number of times the doc_id appears, but [purple]rather the total number of records[/purple].[/blue]

Apparently the parameters are setting criteria whithin the query.

Post the RecordSource of the report!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[perrymans]

There are 46 detail records.

They compact into 24 group headings on the report (thanks to Sorting and Grouping).

I want 24 at the bottom of the report.

Thanks. Sean.

 

[TheAceMan1]

perrymans . . .

I'm not sure why I received the pinky , but was waiting on the SQL. In any event here's what I have on a suggestive basis:

Use late binding of the [blue]recordsource[/blue] in the following manner (any code here resides in the reports [blue]On Open[/blue] event):
[ol][li]Use the [blue]InputBox[/blue] to retrive and store parameters in variables.[/li]
[li]Construct a seperate [blue]Where Clause[/blue] of the variables for appending to SQL.[/li]
[li]In variable [blue]srcSQL[/blue] concatnate proper the new where clause with the base SQL of the report.This gets rid of the parameters and having to input the data twice.[/li]
[li]Construct a [blue]Sum[/blue] query (with the new Where clause) using [blue]Select Distinct[/blue] (this is the count your looking for!). Open a recordset to grab the count and put it in your unbound textbox. Close the recordset.[/li]
[li]Finally store srcSQL in the recordsource of the report.[/li][/ol]
Thats it!

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886