Member Avatar for obicerno

hello guys, can any one know how to extract an amount for an instance..

this was in access data. i just picking up some sample..

a given amount to be extracted was 50,000.00 kilos

i have to table 1 is the master and the other was the list of pickup data from master which is the bases of my 50,000.00 target amount


in trying to used this in DAO

var = 0
given = 50000

do while var = given
if var <> to given then

else

end if

loop
end do

the sample data is show in query


Planter_Name _ kilos

ALIGATO, MARY ROSE 1,403.46
ALIGATO, ROQUE 5,744.48
ALMO, ROSEMARIE 1,248.48
ALVIOLA, MARIDEL 1,230.63
ALVIOLA, RHODEL 1,261.46
ALVIOLA, ROGELIO 684.08
AMANDORON, ROEL 2,235.95
AMBAY, ARIEL 634.64
ANTONIO, AMBOY F. 832.66
ARANAS, PRISCO 9,284.04
AREJA, RICHARD 1,074.80
AUGUIS, FE 1,168.92
AUMENTADO, CONSTANCIO 5,480.95
BALABA, LEONILA 2,151.74
BALBUTIN, ROMEO 591.06
BALDOSANO, DODONG 2,870.29
BALICAO, ELVINO, SR. 1,299.93
BALICAO, REMIE 4,370.66
BANTAYAN, FRANCISCA 4,032.38

  • 3 Contributors
  • 5 Replies
  • 107 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by AndreRet

Dani AI

Generated

Brief summary tied to the thread by , and : there are two different tasks that look similar but need different algorithms — (A) take rows in a defined order until a running total reaches the target, and (B) find a subset of rows whose kilos exactly equal the target (50,000). Choice of method depends on whether ordering matters, whether an exact match is required, and how many candidate rows exist.

Practical options and tradeoffs:

  • Ordered accumulation (fast, simple): run an ordered SELECT (by date, priority or ID), iterate the recordset using DAO, keep an accumulator stored in a fixed-point type (Access Currency or an integer scaled to avoid floating rounding), INSERT selected rows into the destination table and stop when the total meets or exceeds the target. This is the correct choice when rows should be taken in a business order.

  • Exact subset-sum (combinatorial): exact equality requires solving the subset-sum problem. For small N (roughly up to 25–30) a depth-first backtracking with sorting (largest-first) and pruning usually finds a solution quickly. For larger N, use a dynamic programming / bitset approach or a meet-in-the-middle algorithm; those are heavier to implement in VBA but feasible if the target range is modest. Performance is the key constraint: subset-sum is exponential in the worst case.

  • Offload to a scripting tool: export the candidate rows to Python/C# for specialists libraries (bitsets, efficient DP), compute the solution set there, then update Access with the returned IDs. This is often easiest for larger data or complex pruning rules.

Short pseudocode for a backtracking approach (conceptual):

sort items by kilos desc
function search(index, remaining):
  if remaining == 0: record solution; stop
  for i = index to end:
    if items[i].kilos <= remaining:
      choose items[i]; search(i+1, remaining - items[i].kilos)
      unchoose items[i]
    if sum of remaining items < remaining: prune

Implementation tips: store kilos as a fixed-point value (Currency or integer-scaled), always use the primary key when appending rows, wrap inserts in a transaction, and test on a small sample first. For background reading on the exact problem see Subset sum problem.

Recommended Answers

All 5 Replies

Member Avatar for debasisdas

I am sorry,

i really do not understand the question.

Member Avatar for AndreRet

Are you trying to do a sum of all the weight data (4000 + 40000 + 6000 = 50 000) and then add this to a field in your table?

Member Avatar for obicerno

yes, just something like that, picking of weight data which is the sum is equal to 50,000 and then put it in another table..

Member Avatar for debasisdas

You want to pick the records randomly or is there any order to follow (may be by name or date or something)

Member Avatar for AndreRet

Your code will then look something like this -

Rs.Open "SELECT Planter_Name_ kilos FROM YourTableNameHere", YourConnectionNameHere, adOpenStatic, adLockOptimistic

Dim xTotal As Integer
xTotal = 0

If Rs.EOF = True Or Rs.BOF = True Then
msgbox "No data to count"
Else
For xTotal = 0 To Rs.Recordcount - 1
xTotal = xTotal + Rs!Planter_Name_ kilos
Rs.MoveNext
Next xTotal

RsTotal.AddNew

RsTotal!YourTotalFieldNameHere = xTotal
RsTotal.Update
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.