Re: Counting Number of Min/Max Instances

On Aug 23, 12:50 pm, krissco <kris.sco...@xxxxxxxxx> wrote:
On Aug 23, 7:51 am, zwestbrook <zwestbr...@xxxxxxxxx> wrote:





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! :(

.

Relevant Pages

  • Re: Counting Number of Min/Max Instances
    ... For example, for q1 the min response value was "2", ... Split your reports record source into several queries: ... from tblQuestions ... qryCountMin ...
    (microsoft.public.access.reports)
  • Re: Need to wait() Thread in EJB code
    ... a solution that works inside the J2EE container. ... Your solution does work for a setup where a client is sending queries and concurrently listening for responses to past queries. ... Unfortunately I also have the situation where the initial request thread is fired up by an HTTP request and so needs to wind up by building a response and returning that to the browser. ... send off the first query to a queue then execute the second query. ...
    (comp.lang.java.programmer)
  • Re: Fastest way to pass messages?
    ... and then gather a response from each. ... Any advice is appreciated. ... If your messages would make sense as a method call: String ... If your queries and results are likely to evolve over time, ...
    (comp.lang.java.programmer)
  • Re: Why do I get "invalid arguement" when using make table query?
    ... Disregard the "you can't do this" response. ... The person posting the response has used several different aliases, ... Microsoft Office/Access MVP ... I have tried using existing queries and creating new queries and neither ...
    (microsoft.public.access.queries)
  • Re: blackhole
    ... > response to queries to ports that have nothing listening. ...
    (comp.unix.bsd.openbsd.misc)