Combination in an array

[Mulequi]

I have searched all over and can't find examples of the code I'm looking for. I need to evaluate a combination or values in an array one at a time. Here is what would happen. I'll use coins for the example to make it easy to understand

user inputs that they have 5 coins.

so the first array would be:
(5,0,0,0) !the array being (quarters, nickels, dimes, pennies)
then run a sub to check the value of the array which would be $1.25, that part I dont need help with, just the code to find the combinations of the array.

the next array would just check the next combination and check again, but the next few examples of arrays would be:

(4,1,0,0)
(4,0,1,0)
(4,0,0,1)
(3,2,0,0)
(3,1,1,0)
(3,1,0,1)
(3,0,1,1)
(3,0,0,2)
(2,3,0,0)
(2,2,1,0)
(2,2,0,1)
(2,1,2,0)
(2,1,1,1)
(2,1,0,2)
(2,0,3,0)
(2,0,2,1)
(2,0,1,2)
(2,0,0,3)
and so forth until you got to (0,0,0,5)

Any help would be greatly appreciated!!!!!

 

[ajetrumpet]

just as a suggestion, I am wondering if there is another way you can do this, simply because of the complication involved with creating and looping through a multi-dimensional array based on an input value.

Isn't that getting into "Algorithm" land? That might be tough to tackle...

 

[WayneRyan]

Mulequi,

Not wanting to give this a lot of thought, why not use the brute force approach?

Dim Quarters As Integer
Dim Dimes As Integer
Dim Nickels As Integer
Dim Pennies As Integer
Dim TotalCoins As Integer

TotalCoins = 9 ' <-- Could be a textbox value

For Quarters = 0 To TotalCoins
   For Dimes = 0 To TotalCoins
      For Nickels = 0 To TotalCoins
            For Pennies = 0 To TotalCoins
               If Quarters + Dimes + Nickels + Pennies = TotalCoins Then
                  Debug.Print "Total = " & CStr(Quarters * 0.25 + Dimes * 0.10 + Nickels * 0.05  + Pennies * 0.01)
               End If
            Next Pennies
         Next Nickels
      Next Dimes
   Next Quarters

Wayne

 

[raskew]

Hi -

Try playing with this. Just call from debug window. See if it might be adapted to your purposes.

Public Sub Coinage()
'*******************************************
'Purpose:   Given a dollar/cents amount, computes smallest number
'           of bills/coins necessary to return the amount.
'Coded by:  raskew
're: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=53774 ...no longer available'
'*******************************************
Dim pCurAmt As Currency
Dim intAmt  As Currency
Dim strHold As String
Dim strAmt  As String
Dim intNow  As Currency
Dim n       As Integer
Dim NL, fmt, tHold, Msg

   NL = Chr(13) & Chr(10)  ' Define newline.
   fmt = "###,###,##0.00"  ' Define money format.

   strAmt = "50,20,10,5,2,1,0.5,0.25,0.1,.05,.02,.01,"
   Msg = "Enter the amount."
   pCurAmt = Format(InputBox(Msg), fmt)
   intAmt = pCurAmt
   tHold = Format(intAmt, fmt)
   For n = 1 To 12
      intNow = val(Left(strAmt, InStr(strAmt, ",") - 1))
      strAmt = IIf(n < 12, Mid(strAmt, InStr(strAmt, ",") + 1), "0")
      If Int(intAmt / intNow) > 0 Then
         strHold = strHold & LTrim(str(Int(intAmt / intNow))) & " * " & Format(LTrim(str(intNow)), fmt) & NL
         intAmt = intAmt - (Int((intAmt / intNow)) * intNow)
      End If
      If intAmt = 0 Then Exit For
   Next n
   MsgBox Left(strHold, Len(strHold) - 1), vbOKOnly, tHold

End Sub

Bob

 

[gemma-the-husky]

what do you want this for

it strikes me that recursion would get this with an easy to write, and minimalistic use of code, although it may not quite as efficient as using brute force

are you looking for every combination of 5 coins?

 

[Banana]

Create two non normalized temporary tables, one representing the coins, other representing the quantity, like this:

tblCoins
======
Penny
Nickel
Dime
Quarter

tblQuantity
=======
0
1
2
3
4
5

Then do a cartesian query:

SELECT * FROM tblCoins, tblQuantity;

This should give you all possible results for the array. You can then use crosstab report to present it in a crosstab if you want. Also, to get totals or whatever you want to do with the query, you can use total query or if you prefer, use that as the basis for your VBA procedure. This also will work with multiple tables.

 

[WayneRyan]

Banana,

Don't you still have to brute force the Cartesian product?

I think that with a little thought, one could nest the for loops with
something like:

For Pennies = 0 to (9 - Quarters + Dimes + Nickels)

That would remove the "brute force" approach and avoid the cases
where you have things like 9Q + 9D +9N + 9P.

But, I still don't want to think about it ... yet.

Wayne

 

[Banana]

Wayne, my thought was more of to using existing tools rather than re-invent the wheel- if it can be done with cartesian product, better off with that instead of figuring out how to get it tick in VBA and cross your fingers that there won't be bugs. I also kind of wondered if Jet can possibly optimize the cartesian join better than any VBA module.

But now I've just realized that we don't want any products such as 9P+9N+9D+9Q, which cartesian would assuredly give..

 

[Banana]

If the array is supposed to never ever exceed a given value (say 5 coins at a time), move one integer to right at a time.

The algothrim would then do something very similar to how we subtract and carry one zero over; we start at the rightmost column then if it's below a certain value, we "borrow" one from the next left column, and if this left column is zero, go left again until we get at least one, then move it to right then repeat the steps.

 

[WayneRyan]

Banana,

A lesser brute force approach, but still needs an IF statement.

Dim Q As Integer
Dim D As Integer
Dim N As Integer
Dim P As Integer
Dim TotalCoins As Integer

TotalCoins = 9

For Q = 0 To TotalCoins
   For D = 0 To (TotalCoins - Q)
      For N = 0 To (TotalCoins - Q + D)
            For P = 0 To (TotalCoins - Q + D + N)
               If Q + D + N + P = 9 Then Debug.Print Q, D, N, P, Q * 0.25 + D * 0.1 + N * 0.05 + P * 0.01
            Next P
         Next N
      Next D
   Next Q

I can't see it being done without code.

btw, Is that THE Dr. Dobbs?

Wayne

 

[Banana]

This would probably be the most simple to implement. Or I can't think of a better solution right now.



I'm afraid I don't know a Dr. Dobbs. However, I've realized that I blundered and called him Rob when it should be Bob.

This is Bob Dobbs. A bit more about him.

 

[georgedwilkinson]

Dr. Dobb's Journal was a magazine for C++ geeks in the early 90s and beyond.

 

[Banana]

Dr. Dobb's Journal was a magazine for C++ geeks in the early 90s and beyond.

Ah, didn't know about that. But My ignorance is quite appropriate, given my protege's dedication to pursuing Total Slack.

 

[raskew]

Try creating this:

Table: Nums
Field: Num (Number)
Add 10 records: 0 to 9

Run this query:

PARAMETERS Amount Currency
  , [number of coins] Long;
SELECT
    Nums.Num AS Q
  , Nums_1.Num AS D
  , Nums_2.Num AS N
  , Nums_3.Num AS P
  , [nums].[num]*0.25 +[nums_1].[num]*0.1 +[nums_2].[num]*0.05 +[nums_3].[num]*0.01 AS TheTotal
  , [Nums].[Num] +[Nums_1].[num] +[nums_2].[num] +[nums_3].[num] AS [Number of Coins]
FROM
   Nums
  , Nums AS Nums_1
  , Nums AS Nums_2
  , Nums AS Nums_3
WHERE
   ((([nums].[num]*0.25 +[nums_1].[num]*0.1+[nums_2].[num]*0.05 +[nums_3].[num]*0.01)=[amount]) 
AND
   (([Nums].[Num] +[Nums_1].[num] +[nums_2].[num] +[nums_3].[num])<=[number of coins]));

When prompted for Amount, enter a dollar amount. For Number of Coins, enter max number of coins you want to calculate.

Best Wishes - Bob