Report - totals

[tyrynn28]

My query looks like this:
ID Docs
1 A
1 B
1 C
2 A
2 C
My report looks like this:
ID Docs
1
A
B
C
2
A
C

Now, I need the total number of IDs at the report header. How can I do
that? In this example it's two IDs. Please help. Thanks!

[Scott McDaniel]

On Tue, 19 Jun 2007 09:59:34 -0700, ty******@gmail. com wrote:

>My query looks like this:
ID Docs
1 A
1 B
1 C
2 A
2 C
My report looks like this:
ID Docs
1
A
B
C
2
A
C

Now, I need the total number of IDs at the report header. How can I do
that? In this example it's two IDs. Please help. Thanks!

I'm not sure that the Report will give you this information; I checked the properties of the Report and Group and
couldn't find where this was reported. Someone else may have another idea, however.

You can always use a Query to fill this data. For example, build a query something like this, perhaps in the Open event
of your report:

Sub Report_Open()

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.OPEN "SELECT DISTINCT Count(ID) AS RecCount FROM YourTableName WHERE [your where clause here]",
CurrentProject. Connection

Me.YourTotalBox = rst("RecCount")
Set rst = NOthing

End Sub

Scott McDaniel
scott@takemeout _infotrakker.co m
www.infotrakker.com

[bobh]

Hi,
your example looks like you are grouping by ID and listing Docs. I
would think you can just Count(Docs) since there appears to be a one-
for-one
bobh.

On Jun 19, 12:59 pm, tyryn...@gmail. com wrote:

My query looks like this:
ID Docs
1 A
1 B
1 C
2 A
2 C
My report looks like this:
ID Docs
1
A
B
C
2
A
C

Now, I need the total number of IDs at the report header. How can I do
that? In this example it's two IDs. Please help. Thanks!

[CDMAPoster]

On Jun 19, 11:59 am, tyryn...@gmail. com wrote:

My query looks like this:
ID Docs
1 A
1 B
1 C
2 A
2 C
My report looks like this:
ID Docs
1
A
B
C
2
A
C

Now, I need the total number of IDs at the report header. How can I do
that? In this example it's two IDs. Please help. Thanks!

Here's a shot in the dark. Suppose your query has the following SQL:

MyQuery: SELECT ID, Docs FROM tblSupportingDo cumentation;

MyReport.Record Source: MyQuery

MyReport SortingAndGroup ing Field/Expression ID

Detail Section: Docs

MyQueryCount:
SELECT ID FROM MyQuery GROUP BY ID;

!MyQueryCount:
ID
1
2

MyCountQuery:
SELECT Count(MyQueryCo unt.ID) AS MyCount
FROM MyQueryCount;

!MyCountQuery:
MyCount
2

Textbox ControlSource in Report Header:

=DLookUp("[MyCount]","[MyCountQuery]")

Those two queries can be combined using syntax I don't like:

qryAlsoTry:

SELECT Count(*) AS IDCount FROM [SELECT DISTINCT ID AS DistinctID FROM
tblSupportingDo cumentation]. qryDistinct;

or

SELECT Count(*) AS IDCount FROM [SELECT DISTINCT ID AS DistinctID FROM
MyQuery]. qryDistinct;

with

=DLookUp("[IDCount]", "[qryAlsoTry]")

There may be better ways to do this.

James A. Fortune
CD********@Fort uneJames.com