UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Which Combination Of Records Sum Up To X?, Access 2016    
 
   
WongMeister
post Nov 7 2019, 01:41 PM
Post#1



Posts: 1,570
Joined: 17-June 02
From: Redondo Beach, CA


Good Morning Everybody,
I'm looking for a solution for the following example. In a way it's a query, but I sense would require looping a couple recordsets.

My example:
I have a table, tblAmounts, with two fields, RecNo (AutoNumber) and Amount (Currency). For this example, they contain the following records, although in reality, they can have over a hundred records.

RecNo Amount
1 $2.50
2 $2.00
3 $0.50
4 $0.75
5 $0.25
6 $0.39
7 $8.00
8 $1.50

The question is what combinations of [RecNo] would give me X? In this case, X = $2.50

The answers could be:
Records 1
Records 2 & 3
Records 4,5 & 8

Each of the three combinations above will add up to $250.

I want to be able to load data for the two fields into tblAmount, and then plug in a number, and run a command button. It would loop through all the possible combinations, and return three solutions. I know, when dealing with combinations, I can have billions of combinations. I am not sure if there is an shorter way with an Access recordset loop(s).

I don't need anybody here to create a form, but only the general loops.

Thanks!
Go to the top of the page
 
GroverParkGeorge
post Nov 7 2019, 02:06 PM
Post#2


UA Admin
Posts: 36,199
Joined: 20-June 02
From: Newcastle, WA


This is a math calculation in essence.

The loop I would build would start with each record in the record set, sorted smallest to largest.

Step one would be to filter that recordset to exclude all records larger than the target, e.g. in this example, all records > 2.50 would be omitted because they can't qualify by themselves or in any combination. That leaves you with 7 of the 8 in the sample set provided.

Step one is to assign the value for the first (smallest) record to a variable.

Step two, move to the next record and add its value to that variable.

Step three check that new value of the variable against the target.

If it's smaller than the target, move to the next record as in step two.

If it's = to the target, output it to an array and step out of the recordset loop.

If it's larger than the target, abandon it and step out of the recordset loop.

When you reach the end of the recordset without totalling up the target, go back and pick the next record as the initial value of the variable, and repeat the steps.

There may be another way to calculate this, based on a mathematical formula, too, though.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
WongMeister
post Nov 7 2019, 03:08 PM
Post#3



Posts: 1,570
Joined: 17-June 02
From: Redondo Beach, CA


Thanks, George.

Yes, this is getting pretty "ugly." With the third solution (4,5,8), it would add 4 & 5; if this is still under X, I would need to save this, and then reloop the 4&5 with 1 and then 2 and then 3 and then 6, etc...

Go to the top of the page
 
GroverParkGeorge
post Nov 7 2019, 03:22 PM
Post#4


UA Admin
Posts: 36,199
Joined: 20-June 02
From: Newcastle, WA


Yes, I think it would be really ugly, because you not only have to check the summed values, but keep track of which raw values were included up to that point. I hope there is a mathematical solution that's more elegant.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
WongMeister
post Nov 7 2019, 03:29 PM
Post#5



Posts: 1,570
Joined: 17-June 02
From: Redondo Beach, CA


Ha ha. I don't come to UtterAccess for the style points!

Thanks again!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 04:05 PM