don

[Don]

Unable to find a solution based upon the answers already posted.
I have a report in which I need to count the number of occurrences for each
of the following controls. [ReviewID] [claimID] [ServiceID] - (these are all
text or numeric fields) and [audit] (this is a yes/no checkbox field)

When I run the query, I see a lot of duplicate data as a [ReviewID] may
contain multiple [claimID]s, and each [claimID] may contain multiple
[serviceID]s. I have the contols set to hide duplicate values. I can set an
unbound control on the report to count these columns of data, but I only want
to count each unique occurrence one time, and because each column may be a
subset of another, I cannot restrict the data in the query. For example

ReviewID ClaimID ServiceID Audit
1 a 77 x
1 a 78
1 b 22
2 c 55 x
2 c 77 x
________________________________________________
Desired results
2 3 5 3
Current results
5 5 5 5

Any help would be greatly appreciated.

 

[Larry Linson]

If the question has already been asked, and answers have been proposed, then
this post should be a followup to that message thread -- I wouldn't waste
time possibly proposing answers that you've already rejected, and I haven't
the time and energy to go research to find the original thread or threads.

If this was intended to re-start from scratch, I have comments on two
issues.

(1) You mention "the query", and how you can't "restrict" the data. But you
don't show the SQL for the Query -- in the upper left of the Query toolbar,
you can select SQL view and copy it.

(2) You say you want to count the number of occurences for each of three
Fields, and your "current result" does exactly that. I might guess you
really mean you want a count of the "unique values" for each field, but for
the fact that you show a desired count of 5 for the Service ID field, which
has only 4 unique values.

If you want what you describe, you can do it with separate queries on the
unique values. That is, a query for one Field, then right-click and in
Properties set UniqueValues. Then you can Count the occurrences in that
UniqueValues Query. And, because you can't only have one RecordSource for a
Report, use a DCount on the UniqueValues Query for each Field.

(3) But, you want something different yet for "Audit"... you want a count of
the times it is True. Create a Query, extracting only the Audit field, with
a Criteria of True. Then a count of that will give you the number of Records
with Audit.

If this doesn't solve your problem, clarify (1), (2), and (3). Then perhaps
someone will be able to assist you.

Larry Linson
Microsoft Access MVP

Unable to find a solution based upon the answers already posted.
I have a report in which I need to count the number of occurrences for
each
of the following controls. [ReviewID] [claimID] [ServiceID] - (these are
all
text or numeric fields) and [audit] (this is a yes/no checkbox field)

When I run the query, I see a lot of duplicate data as a [ReviewID] may
contain multiple [claimID]s, and each [claimID] may contain multiple
[serviceID]s. I have the contols set to hide duplicate values. I can set
an
unbound control on the report to count these columns of data, but I only
want
to count each unique occurrence one time, and because each column may be a
subset of another, I cannot restrict the data in the query. For example

ReviewID ClaimID ServiceID Audit
1 a 77 x
1 a 78
1 b 22
2 c 55 x
2 c 77 x
________________________________________________
Desired results
2 3 5 3
Current results
5 5 5 5

Any help would be greatly appreciated.

 

[Don]

Let me clarify - I have searched the database for possible answers to this
dilemma but I have been unsuccessful in locating any similar problems. This
is a new issue.

The clincher here is that column 2 is a subset of column 1, and column 3 is
a subset of column 2, etc. Column 3 isn't a problem because I will be
counting all values, but the remaining columns are. I suppose I could use
multiple queries, but would that not also entail multiple reports/sub reports?

I was hoping to just have one query that would give me everything that
related to the [reviewID] and then count only the unique values in the colums
that will require it. Because I am not a technical person, I don't quite
understand your response relative to how to set up the uniqueValues Query or
what a DCount is, but I will give it a shot. Is all of this coding to be
part of the query or is it part of the report? In other words, I don't know
where I need to place the code.

Sorry to have caught you when you appear to have been having a bad day, but
your response is truely appreciated by us novices.

--
Thanks!

If the question has already been asked, and answers have been proposed, then
this post should be a followup to that message thread -- I wouldn't waste
time possibly proposing answers that you've already rejected, and I haven't
the time and energy to go research to find the original thread or threads.

If this was intended to re-start from scratch, I have comments on two
issues.

(1) You mention "the query", and how you can't "restrict" the data. But you
don't show the SQL for the Query -- in the upper left of the Query toolbar,
you can select SQL view and copy it.

(2) You say you want to count the number of occurences for each of three
Fields, and your "current result" does exactly that. I might guess you
really mean you want a count of the "unique values" for each field, but for
the fact that you show a desired count of 5 for the Service ID field, which
has only 4 unique values.

If you want what you describe, you can do it with separate queries on the
unique values. That is, a query for one Field, then right-click and in
Properties set UniqueValues. Then you can Count the occurrences in that
UniqueValues Query. And, because you can't only have one RecordSource for a
Report, use a DCount on the UniqueValues Query for each Field.

(3) But, you want something different yet for "Audit"... you want a count of
the times it is True. Create a Query, extracting only the Audit field, with
a Criteria of True. Then a count of that will give you the number of Records
with Audit.

If this doesn't solve your problem, clarify (1), (2), and (3). Then perhaps
someone will be able to assist you.

Larry Linson
Microsoft Access MVP

Unable to find a solution based upon the answers already posted.
I have a report in which I need to count the number of occurrences for
each
of the following controls. [ReviewID] [claimID] [ServiceID] - (these are
all
text or numeric fields) and [audit] (this is a yes/no checkbox field)

When I run the query, I see a lot of duplicate data as a [ReviewID] may
contain multiple [claimID]s, and each [claimID] may contain multiple
[serviceID]s. I have the contols set to hide duplicate values. I can set
an
unbound control on the report to count these columns of data, but I only
want
to count each unique occurrence one time, and because each column may be a
subset of another, I cannot restrict the data in the query. For example

ReviewID ClaimID ServiceID Audit
1 a 77 x
1 a 78
1 b 22
2 c 55 x
2 c 77 x
________________________________________________
Desired results
2 3 5 3
Current results
5 5 5 5

Any help would be greatly appreciated.