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

B

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.
Click to expand...
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
 
G

Guest

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.
 
K

kingston via AccessMonster.com

How about creating another group in your report for Report Case#? Then you
could count within that group.
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.
Click to expand...
 
G

Guest

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

Bob Quintal said:
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.
Click to expand...
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
Click to expand...
 
B

Bob Quintal

=?Utf-8?B?S2VuIFNoZXJpZGFu?=
The line:

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

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
Click to expand...
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.
Click to expand...
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.
Click to expand...
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

Bob Quintal said:
=?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.
Click to expand...
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
Click to expand...
Click to expand...
 
G

Guest

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


Ken Sheridan said:
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

Bob Quintal said:
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.
Click to expand...
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HOW CAN I COUNT ALL THE GROUPS IN A REPORT 1
Count of records for groups on a report 1
report data grouping failure? 4
Totals on report 11
show counts in a report of multiple years 3
How can I count each possible response in a report to each questio 2
Display Count Range of Grouped Items 2
Display a weekending date even if there is no data 5

Top