Query to retrieve 'n' rows that add up to a certain value

  • Thread starter Thread starter Juan
  • Start date Start date
J

Juan

Hi everyone,

I am triyng to execute a query that wuld retreive a number of rows
(undetermined), with the condition that a certain field must add up to a
value that I would determine. To complicate it a little more, the criteria
to select those rows must be "the maximum values of other field".

So, if I have two fields, "life" and "value", I want to be able to select
the number of rows that add up to, say, 100.000 in the field value, and
select only the rows that have the maximum value in the field "life".

Is this possible at all?

Thanks, from Spain

Juan
 
Hi everyone,

I am triyng to execute a query that wuld retreive a number of rows
(undetermined), with the condition that a certain field must add up to a
value that I would determine. To complicate it a little more, the criteria
to select those rows must be "the maximum values of other field".

So, if I have two fields, "life" and "value", I want to be able to select
the number of rows that add up to, say, 100.000 in the field value, and
select only the rows that have the maximum value in the field "life".

Is this possible at all?
Click to expand...

Probably not.

There might be tens of millions of different combinations of records
which add to 100.000 - or there might be none at all, but the query
would have to check all the possibilities none the less. To do this
Access would have to evaluate *every pair* of records in your table,
then every triplet, then every set of four, five, six...

Such a query could be written but in any reasonable sized table, it
would take FOREVER to run!

Now watch one of the SQL query gurus prove me wrong here... <g>

John W. Vinson[MVP]
 
But I don´t need it to iterate. It shoud start taking records from the top
(considering they have been orderded by the field "life") until the "value"
fields adds 100.000 (or more, I´m not looking for the exact value)
 
Although this sounds like a homework assignment and I can't say that I know
exactly how to implement this, but it sounds like you need to say use VBA to
set a recordset equal to the table , then starting at the first record, step
through each record adding the value of "Value" to say the variable summation
until the value of summation is >= 100.000 (hundred thousand?) or you have
reached the last record. If you keep track of how many times you have to
move to the next record, then you can provide some information back as to
which record number it was that finally pushed the result to or over the top
or if the value desired is achievable.
 
Back
Top