Select query - pull records to sum a specific amount

[jbarnes]

I really hope there is a way to do this. I have multiple expense
entries for a project, all that are billable. However each month, per
the contract we are only allowed to bill 20K, therefore I must put the
rest on hold. Here is my question: Is there a way to specify a given
sum amount that a query will pull records for. For example if I have
9K of records I need to put on hold can a query or vb automatically
pick the records that will best total 9K? Currently I'm going record
by record and trying to match transactions up that will total 9K,
keeping in mind I can only use whole records not partial.

 

[Michel Walsh]

Hi,


Unfortunately, the solution is not trivial. It is known as the BackPack
problem (or the Alpine Pack, or similar names).


An heuristic approach is to sort the values, in decreasing order, and to
pick the items if they fit, as long as the bag is not full, or as long as
there is possible items to put into the bag.


As example, given a bag with a capacity of 10, and given the items "weight"
of:

11, 7, 4, 2


11 cannot enter, we take 7, 4 cannot, 2 can, and that is it, our solution is
7+2 < 10

The heuristic can fail dramatically, as with:

9, 6, 4, 2


where 6+4 would have been better than 9.

If there are a large number of different items, the heuristic tends to be
near the optimum, and if there are few items, someone can always make an
explicit enumeration of all the combinations.




Hoping it may help,
Vanderghast, Access MVP