Counting Number of Min/Max Instances

[zwestbrook]

I have a report that identifies the min and max values in a field over
a given population. I want to include the number of instances of that
value as well. For example, for q1 the min response value was "2",
which occurred 12 times, and the max response value was "5", which
occurred 2 times.

I am using the rpt footer to show the results (so that it doesn't
repeat for each q the number of responses). I have tried:

=Abs(Sum([q1]=Min(IIf([q1]>0,[q1],Null)))) -- "Cannot have aggregate
function..."
=DCount("[q1]","tbl_questions_responses_all","[q1]=Min([q1])") --
#ERROR

And various other permutations of those examples. I suppose I could do
individual counts on the different response values (1 - 5) and then
return the corresponding value count at the footer, but was hoping to
save my fingers some repeptitive typing (5 response values, 21 total
questions).

Advice?

 

[krissco]

I have a report that identifies the min and max values in a field over
a given population. I want to include the number of instances of that
value as well. For example, for q1 the min response value was "2",
which occurred 12 times, and the max response value was "5", which
occurred 2 times.

I am using the rpt footer to show the results (so that it doesn't
repeat for each q the number of responses). I have tried:

=Abs(Sum([q1]=Min(IIf([q1]>0,[q1],Null)))) -- "Cannot have aggregate
function..."
=DCount("[q1]","tbl_questions_responses_all","[q1]=Min([q1])") --
#ERROR

And various other permutations of those examples. I suppose I could do
individual counts on the different response values (1 - 5) and then
return the corresponding value count at the footer, but was hoping to
save my fingers some repeptitive typing (5 response values, 21 total
questions).

Advice?

Use queries. Split your reports record source into several queries:

qryMax (contains the maxs)
select max(q1) as myMax
from tblQuestions

qryMin (contains the mins)
select min(q1) as myMin
from tblQuestions

qryCountMin (contains the mins and their count)
select myMin, count(*) as minCount
from tblQuestions, qryMin
where qryMin.myMin = tblQuestions.q1

qryCountMax (contains the maxs and their count)
select myMax, count(*) as maxCount
from tblQuestions, qryMax
where qryMax.myMax = tblQuestions.q1

qryReportSource (combines the last two queries to report on)
select myMax as q1, maxCount as qCount
from qryCountMax
union all
select myMin as q1, minCount as qCount
from qryCountMin

I've broken the problem down into the smallest pieces I could see -
this results in a lot of saved queries. You can probably combine
several of them if you desire.

-Kris

 

[zwestbrook]

I have a report that identifies the min and max values in a field over
a given population. I want to include the number of instances of that
value as well. For example, for q1 the min response value was "2",
which occurred 12 times, and the max response value was "5", which
occurred 2 times.

I am using the rpt footer to show the results (so that it doesn't
repeat for each q the number of responses). I have tried:

=Abs(Sum([q1]=Min(IIf([q1]>0,[q1],Null)))) -- "Cannot have aggregate
function..."
=DCount("[q1]","tbl_questions_responses_all","[q1]=Min([q1])") --
#ERROR

And various other permutations of those examples. I suppose I could do
individual counts on the different response values (1 - 5) and then
return the corresponding value count at the footer, but was hoping to
save my fingers some repeptitive typing (5 response values, 21 total
questions).

Advice?

Use queries. Split your reports record source into several queries:

qryMax (contains the maxs)
select max(q1) as myMax
from tblQuestions

qryMin (contains the mins)
select min(q1) as myMin
from tblQuestions

qryCountMin (contains the mins and their count)
select myMin, count(*) as minCount
from tblQuestions, qryMin
where qryMin.myMin = tblQuestions.q1

qryCountMax (contains the maxs and their count)
select myMax, count(*) as maxCount
from tblQuestions, qryMax
where qryMax.myMax = tblQuestions.q1

qryReportSource (combines the last two queries to report on)
select myMax as q1, maxCount as qCount
from qryCountMax
union all
select myMin as q1, minCount as qCount
from qryCountMin

I've broken the problem down into the smallest pieces I could see -
this results in a lot of saved queries. You can probably combine
several of them if you desire.

-Kris- Hide quoted text -

- Show quoted text -

Thank you, Kris - I have created the Min and Max queries, they look
great. Now I am stumbling on the Count query. It works fine with just
one value...

SQL --
SELECT Count([Min1]) AS Min1Count
FROM qry_questions_min, tbl_scorecard_results
WHERE (((qry_questions_min.Min1)=[tbl_scorecard_results].[q1]));

Returns Min1Count of 15 (correct count of the minimum entry, "2")

The problem is that when I begin to add the other question values
(Min2, Min3, etc.) the query only returns 1 for the MinCount value of
each question (Min1Count, Min2Count, Min3Count). Should be 15, 3, and
1, respectively.

SQL --
SELECT Count([Min1]) AS Min1Count, Count([Min2]) AS Min2Count,
Count([Min3]) AS Min3Count
FROM qry_questions_min, tbl_scorecard_results
WHERE (((qry_questions_min.Min1)=[tbl_scorecard_results].[q1]) AND
((qry_questions_min.Min2)=[tbl_scorecard_results].[q2]) AND
((qry_questions_min.Min3)=[tbl_scorecard_results].[q3]));

It is looking like I would have to do a separate MinCount Qry for each
question...I was hoping that there was a more straightforward
alternative. I have 21 questions and that would be a big gaggle of
queries! 21 for each MinCount and another 21 for each MaxCount!

 

[krissco]

SELECT Count([Min1]) AS Min1Count

FROM qry_questions_min, tbl_scorecard_results
WHERE (((qry_questions_min.Min1)=[tbl_scorecard_results].[q1]));

Oh. I see.
Your qry_Questions_min has lost "Which question" it was referring to.
Sorry. I didn't catch that in your original post.

change your qry_questions_min to something like this:
select min(q1) as minQ1, min(q2) as minQ2 . . ., max(q1) as maxQ1,
max(q2) as maxQ2 . . .
from tbl_scorecard_results. This should return ONE record.

Your second query will look something like this:
select minQ1, minQ2, minQ3, . . . maxQ1, maxQ2. . .,
count(iif(q1=minQ1, q1, null)) as countMinQ1, count(iif(q1=maxQ1, q1,
null)) as countMaxQ1, count(iif(q2=minQ2, q2, null)) as
countMinQ2. . .
from tbl_scorecard_results as t, qryTheFirstQuery as f
group by minq1, minq2, minq3 . . . maxq1, maxq2 . . .

I think that will work without joining the tables, but you may need to
join them too:
where nz(t.q1, "") = minQ1
or nz(t.q1, "") = maxQ1
or nz(t.q2, "") = minQ2
or nz(t.q2, "") = maxQ2 . . . .

It is looking like I would have to do a separate MinCount Qry for each
question...I was hoping that there was a more straightforward
alternative. I have 21 questions and that would be a big gaggle of
queries! 21 for each MinCount and another 21 for each MaxCount!

That would be nasty.

-Kris

 

[zwestbrook]

SELECT Count([Min1]) AS Min1Count
FROM qry_questions_min, tbl_scorecard_results
WHERE (((qry_questions_min.Min1)=[tbl_scorecard_results].[q1]));

Oh. I see.
Your qry_Questions_min has lost "Which question" it was referring to.
Sorry. I didn't catch that in your original post.

change your qry_questions_min to something like this:
select min(q1) as minQ1, min(q2) as minQ2 . . ., max(q1) as maxQ1,
max(q2) as maxQ2 . . .
from tbl_scorecard_results. This should return ONE record.

Your second query will look something like this:
select minQ1, minQ2, minQ3, . . . maxQ1, maxQ2. . .,
count(iif(q1=minQ1, q1, null)) as countMinQ1, count(iif(q1=maxQ1, q1,
null)) as countMaxQ1, count(iif(q2=minQ2, q2, null)) as
countMinQ2. . .
from tbl_scorecard_results as t, qryTheFirstQuery as f
group by minq1, minq2, minq3 . . . maxq1, maxq2 . . .

I think that will work without joining the tables, but you may need to
join them too:
where nz(t.q1, "") = minQ1
or nz(t.q1, "") = maxQ1
or nz(t.q2, "") = minQ2
or nz(t.q2, "") = maxQ2 . . . .

It is looking like I would have to do a separate MinCount Qry for each
question...I was hoping that there was a more straightforward
alternative. I have 21 questions and that would be a big gaggle of
queries! 21 for each MinCount and another 21 for each MaxCount!

That would be nasty.

-Kris

Thank you for your help...I was making some progress with your
response above; however, was still stumbling with the programming and
just not quite getting what I needed. What I ended up doing was
creating 2 queries (Min/Max) and then appending the results to a temp
table. I appreciate your help, thanks!