How many records do you have?

jseger22,

If you were going to calculate that on a piece of paper, how would you do it?

Sincerely,

Chris O.

Not an easy problem. It seems a variation of the well known 'back pack"
problem, which is a complex algorithm (and generally, people only use an
heuristic, giving an approximate solution, rather than the exact algorithm).

Indeed, define, for each record, the variable x[i] such that  x[i]=0 if we
don't pick the record and x[i]=1 if we pick it.

We want:

Optimize (Maximize)
          SUM( x[i] )      ' the sum is about those x[i]=1,
                                   so this is the total number of
                                   records we picked
under
           SUM( x[i] * Score[i] )    =  45 * SUM( x[i] )
                                  ' the mean constraint
and
           x[i]  element_of  {0, 1}

but that is the same as:
(since  MAX { cte*z }    =  cte* MAX{ z }  )

Optimize
           SUM( x[i] * Score[i] )
under
           SUM( x[i] * Score[i] )    =  45 * SUM( x[i] )
and
           x[i]  element_of  {0, 1}

which seems harder than the back-pack problem:

Optimize
       SUM(x[i] * Score[i])
under
       SUM(  x[i]* Score[i] )  <=  W_constant
and
        x[i]  element_of  {0, 1}

So, for an exact solution, I would look for an algorithm based on Balas
family of algorithms, or other dynamic approach, but for all I know, that is
far stretched to do by SQL, unless you consider brutal implicit enumeration.

Sorry, but still, I can be wrong, it may just look harder than the backpack,
problem, while be easier, after all.

Vanderghast, Access MVP

This is a variant of the NP-complete "Satisfiability Problem", which is
strongly suspected (but not proved, after a century of effort) to have no
"efficient" solution. The other replies in this thread give you some feel for
the magnitude of the "brute force" solution, and I'd strongly suspect that if
you were to come up with a fast and elegant solution, you'ld be on the cover
of Time Magazine as the mathematical genius of the century.

In short... good luck. This probably cannot be done.

            John W. Vinson [MVP]