How to count record of unique value

[Bob Howard]

Hello everyone !

I have a query that feeds two reports. Report 1 is the actual report (call
it a statement). Report 2 print mailing labels for mailing the statements.

Most of the time, there's only one statement per person.

I recently noticed that there are cases where the same person gets more than
one statement. I changed the report that produces the labels to print them
in a Group Header section that breaks on the "person" (not their name, but
on a field called [EINFO Record Number] which is the primary unique key
that's the internal number of the person). Previously, I printed the label
in the Detail section until I noticed that I was getting duplicate labels.

So far, so good.

In an OnOpen event for the labels, I count the number of labels to be
produced, and as an aid to the user I display that on the screen. To obtain
the count, I use the Dcount function in this OnOpen event against that same
query.

Unfortunately, the number returned by Dcount includes the duplicates. It
kinda makes the number useless.

Is there some technique that I can employ within the VBA code itself that
will give me a count of only the UNIQUE occurrences of [EINFO Record Number]
??

Thanks in advance!

Bob (@Marturerg)

 

[Duane Hookom]

Create another query based on your "have a query" that is a totals query.
You can then count the number of records in the totals query.

 

[Bob Howard]

Thanks Duane. I was aware of this as a solution, but wanted to be able to
accomplish this within the VBA. In reality, there are three queries since
the same code is used for three different components of the application (the
name of the query to be used is passed as a parameter to each report
program). So I would actually need to create three new queries and point
the Dcount function to the proper one. That's a lot of extra overhead ---
which is why I was hoping to be able to accomplish this strictly within the
VBA. Bob.

Create another query based on your "have a query" that is a totals query.
You can then count the number of records in the totals query.

--
Duane Hookom
MS Access MVP

Hello everyone !

I have a query that feeds two reports. Report 1 is the actual report
(call
it a statement). Report 2 print mailing labels for mailing the
statements.

Most of the time, there's only one statement per person.

I recently noticed that there are cases where the same person gets more
than
one statement. I changed the report that produces the labels to print
them
in a Group Header section that breaks on the "person" (not their name, but
on a field called [EINFO Record Number] which is the primary unique key
that's the internal number of the person). Previously, I printed the
label
in the Detail section until I noticed that I was getting duplicate labels.

So far, so good.

In an OnOpen event for the labels, I count the number of labels to be
produced, and as an aid to the user I display that on the screen. To
obtain
the count, I use the Dcount function in this OnOpen event against that
same
query.

Unfortunately, the number returned by Dcount includes the duplicates. It
kinda makes the number useless.

Is there some technique that I can employ within the VBA code itself that
will give me a count of only the UNIQUE occurrences of [EINFO Record
Number]
??

Thanks in advance!

Bob (@Marturerg)