how can I count unique instances of repeating data on a report

[Bob Quintal]

I have a report that is divided into groups . Within the
groups I have data that appears multiple times ( and this is
correct) but I only want to count the data that repeats , as
one instance. I have tried Count function but it counts them
as individual instances. For example...
GROUP 1
Report case# person assigned Date assigned
1 Jim
5/2/07 2 Jim
5/4/07 2 Andy
5/5/07 3 Gale
6/6/07 Total number cases = 4 .

The 4 is incorrect , it should be 3 , it is counting Case #2
twice . The field that says Total number of cases = is set up
as =Count([case#]).

Any ideas how I do it on the report itself?
Thanks in advance.
Gale Y. Freeman.

Some VB code works nicely for this.

In the Report's Declarations section, add the following
Dim vMyCase as variant
Dim iUniqueCount as integer

In the GroupHeader_Print event put
vMyCase = Null
iUniqueCount = 0

In the detail_Print event put
If me. [Report Case#] <> vmyCase then
iUniqueCount = iUniqueCount + 1
vMyCase = [Report Case#]
End if

In the GroupFooter_Format event you just put iUniqueCount into
an unbound textbox you put in the footer for that value.
me.CaseCount = iUniqueCount

 

[Gale Y Freeman MIS]

I have a report that is divided into groups . Within the groups I have data
that appears multiple times ( and this is correct) but I only want to count
the data that repeats , as one instance. I have tried Count function but it
counts them as individual instances.
For example...
GROUP 1
Report case# person assigned Date assigned
1 Jim 5/2/07
2 Jim 5/4/07
2 Andy 5/5/07
3 Gale 6/6/07
Total number cases = 4 .

The 4 is incorrect , it should be 3 , it is counting Case #2 twice .
The field that says Total number of cases = is set up as =Count([case#]).

Any ideas how I do it on the report itself?
Thanks in advance.
Gale Y. Freeman.

 

[Ken Sheridan]

The line:

If Me.[Report Case#] <> vmyCase Then

will never evaluate to True as the vmyCase variable is initialised to Null
in the group header, so the expression will always evaluate to Null.
Consequently the iUniqueCount variable will never be incremented and the
CaseCount will always be zero. The variable should be initialised to a value
of the same data type as the Case #, i.e. 0 if it’s a number or a zero length
string "" if its text. From this it follows of course that a variable of the
same data type as the Case # column can be declared rather than a variant.

Also its as well to examine the PrintCount property when using the Print
event procedure to increment a variable or control. This avoids double
counting if the event fires more than once per detail, as it can do in some
circumstances:

If PrintCount = 1 Then
If Me.[Report Case#] <> vmyCase Then
iUniqueCount = iUniqueCount + 1
vMyCase = [Report Case#]
End if
End If

As Print event procedures are used to declare and initialise the variables
the Print event procedure of the group footer should be used to assign the
value to the CaseCount control, not the Format event procedure.

If you reply to this post and I don't respond, my apologies, but I'll be
away incommunicado for a couple of weeks after tomorrow.

Ken Sheridan
Stafford, England

I have a report that is divided into groups . Within the
groups I have data that appears multiple times ( and this is
correct) but I only want to count the data that repeats , as
one instance. I have tried Count function but it counts them
as individual instances. For example...
GROUP 1
Report case# person assigned Date assigned
1 Jim
5/2/07 2 Jim
5/4/07 2 Andy
5/5/07 3 Gale
6/6/07 Total number cases = 4 .

The 4 is incorrect , it should be 3 , it is counting Case #2
twice . The field that says Total number of cases = is set up
as =Count([case#]).

Any ideas how I do it on the report itself?
Thanks in advance.
Gale Y. Freeman.

Some VB code works nicely for this.

In the Report's Declarations section, add the following
Dim vMyCase as variant
Dim iUniqueCount as integer

In the GroupHeader_Print event put
vMyCase = Null
iUniqueCount = 0

In the detail_Print event put
If me. [Report Case#] <> vmyCase then
iUniqueCount = iUniqueCount + 1
vMyCase = [Report Case#]
End if

In the GroupFooter_Format event you just put iUniqueCount into
an unbound textbox you put in the footer for that value.
me.CaseCount = iUniqueCount

 

[Bob Quintal]

=?Utf-8?B?S2VuIFNoZXJpZGFu?=

The line:

If Me.[Report Case#] <> vmyCase Then

You are absolutely right on this one. I goofed on that one. I
would usually dim the variable as a string, but I didn't know
what the O.P.s field type was and I tried to get fancy. I'm old
enough to know better.

will never evaluate to True as the vmyCase variable is
initialised to Null in the group header, so the expression
will always evaluate to Null. Consequently the iUniqueCount
variable will never be incremented and the CaseCount will
always be zero. The variable should be initialised to a value
of the same data type as the Case #, i.e. 0 if it’s a number
or a zero length string "" if its text. From this it follows
of course that a variable of the same data type as the Case #
column can be declared rather than a variant.

Also its as well to examine the PrintCount property when using
the Print event procedure to increment a variable or control.
This avoids double counting if the event fires more than once
per detail, as it can do in some circumstances:

If PrintCount = 1 Then
If Me.[Report Case#] <> vmyCase Then
iUniqueCount = iUniqueCount + 1
vMyCase = [Report Case#]
End if
End If

True, or one could code a descending counter in the on retreat
event of the section.

As Print event procedures are used to declare and initialise
the variables the Print event procedure of the group footer
should be used to assign the value to the CaseCount control,
not the Format event procedure.

I've sometimes found that the print event does not react
properly when assigning vars to textboxes.

If you reply to this post and I don't respond, my apologies,
but I'll be away incommunicado for a couple of weeks after
tomorrow.

Have a great time if going on vacation, a prosperous time if on
business. If it's a health issue, best of luck.

Ken Sheridan
Stafford, England

=?Utf-8?B?R2FsZSBZIEZyZWVtYW4gTUlT?= <[email protected]>
wrote in

I have a report that is divided into groups . Within the
groups I have data that appears multiple times ( and this
is correct) but I only want to count the data that repeats
, as one instance. I have tried Count function but it
counts them as individual instances. For example...
GROUP 1
Report case# person assigned Date
assigned 1 Jim
5/2/07 2 Jim

5/4/07 2 Andy

5/5/07 3 Gale

6/6/07 Total number cases = 4 .

The 4 is incorrect , it should be 3 , it is counting Case
#2 twice . The field that says Total number of cases = is
set up as =Count([case#]).

Any ideas how I do it on the report itself?
Thanks in advance.
Gale Y. Freeman.

Some VB code works nicely for this.

In the Report's Declarations section, add the following
Dim vMyCase as variant
Dim iUniqueCount as integer

In the GroupHeader_Print event put
vMyCase = Null
iUniqueCount = 0

In the detail_Print event put
If me. [Report Case#] <> vmyCase then
iUniqueCount = iUniqueCount + 1
vMyCase = [Report Case#]
End if

In the GroupFooter_Format event you just put iUniqueCount
into an unbound textbox you put in the footer for that value.
me.CaseCount = iUniqueCount

 

[Gale Y Freeman MIS]

Thanks for trying to help me , but I am afraid the answer is a little over my
head.
I am going to give it a whirl and see what happens. Thanks to everyone who
responded.
Galey

The line:

If Me.[Report Case#] <> vmyCase Then

will never evaluate to True as the vmyCase variable is initialised to Null
in the group header, so the expression will always evaluate to Null.
Consequently the iUniqueCount variable will never be incremented and the
CaseCount will always be zero. The variable should be initialised to a value
of the same data type as the Case #, i.e. 0 if it’s a number or a zero length
string "" if its text. From this it follows of course that a variable of the
same data type as the Case # column can be declared rather than a variant.

Also its as well to examine the PrintCount property when using the Print
event procedure to increment a variable or control. This avoids double
counting if the event fires more than once per detail, as it can do in some
circumstances:

If PrintCount = 1 Then
If Me.[Report Case#] <> vmyCase Then
iUniqueCount = iUniqueCount + 1
vMyCase = [Report Case#]
End if
End If

As Print event procedures are used to declare and initialise the variables
the Print event procedure of the group footer should be used to assign the
value to the CaseCount control, not the Format event procedure.

If you reply to this post and I don't respond, my apologies, but I'll be
away incommunicado for a couple of weeks after tomorrow.

Ken Sheridan
Stafford, England

I have a report that is divided into groups . Within the
groups I have data that appears multiple times ( and this is
correct) but I only want to count the data that repeats , as
one instance. I have tried Count function but it counts them
as individual instances. For example...
GROUP 1
Report case# person assigned Date assigned
1 Jim
5/2/07 2 Jim
5/4/07 2 Andy
5/5/07 3 Gale
6/6/07 Total number cases = 4 .

The 4 is incorrect , it should be 3 , it is counting Case #2
twice . The field that says Total number of cases = is set up
as =Count([case#]).

Any ideas how I do it on the report itself?
Thanks in advance.
Gale Y. Freeman.

Some VB code works nicely for this.

In the Report's Declarations section, add the following
Dim vMyCase as variant
Dim iUniqueCount as integer

In the GroupHeader_Print event put
vMyCase = Null
iUniqueCount = 0

In the detail_Print event put
If me. [Report Case#] <> vmyCase then
iUniqueCount = iUniqueCount + 1
vMyCase = [Report Case#]
End if

In the GroupFooter_Format event you just put iUniqueCount into
an unbound textbox you put in the footer for that value.
me.CaseCount = iUniqueCount