My Assistant
Custom Search

All Possible Combinations Of Single Digit Numbers, Access 2016 
Sep 19 2019, 09:25 AM Post#1  
Posts: 102 Joined: 3February 03  I have a 5 digit number on the bottom like: 32324 I have a 5 digit number on the Top like: 23451 I'm trying to create a vba function that will create a list of all of the bottom digits + the top digits like this without missing any of the possible combinations : B1 + T1 B2 + T2 B3 + T3 B4 +T4 B5 + T5 = (55775) B1 + T1 B2 + T2 B3 + T3 B4 +T4 B5 = (55774) B1 + T1 B2 + T2 B3 + T3 B4 B5 = (55724) B1 + T1 B2 + T2 B3 B4 B5 = (55324) B1 + T1 B2 B3 + T3 B4 +T4 B5 + T5 = (52775) B1 + T1 B2 B3 + T3 B4 +T4 B5 = (52774) B1 + T1 B2 B3 + T3 B4 B5 = (52724) B1 + T1 B2 B3 B4 +T4 B5 + T5 = (52375) B1 + T1 B2 B3 B4 +T4 B5 = (52374) B1 + T1 B2 B3 B4 B5 + T5 = (52325) B1 + T1 B2 B3 B4 B5 =(52324) B1 B2 + T2 B3 + T3 B4 +T4 B5 + T5 = (35775) B1 B2 + T2 B3 + T3 B4 +T4 B5 = (35774) B1 B2 + T2 B3 + T3 B4 B5 = (35724) B1 B2 + T2 B3 B4 +T4 B5 = (35375) B1 B2 B3 + T3 B4 +T4 B5 + T5 = (32775) B1 B2 B3 + T3 B4 +T4 B5 = (32774) B1 B2 B3 + T3 B4 B5 = (32724) B1 B2 B3 B4 +T4 B5 + T5 = (32375) B1 B2 B3 B4 +T4 B5 = (32374) B1 B2 B3 B4 B5 + T5 = (32325) B1 B2 B3 B4 B5 = (32324) The top and bottom numbers will always be 5 digits. It will always be bottom + top, never top + bottom. Any help or direction would be greatly appreciated! Thanks, Sup 
Sep 19 2019, 10:29 AM Post#2  
Access Wiki and Forums Moderator Posts: 76,381 Joined: 19June 07 From: SunnySandyEggo  Hi. What is this for? When you say bottom + top, does the + means addition? For example, bottom = 123456 and top = 98765, then bottom + top = 111110? Then get all the possible combinations of that?  Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know"  Kansas Access Website  Access Blog  Email 
Sep 19 2019, 10:35 AM Post#3  
Posts: 68 Joined: 18August 16 From: Bristol, UK  I can't think of any reason why, but thought I'd have a look anyway and came up with the below: Sub test() tp = 23451 bt = 32324 For x = 1 To 5 For y = 0 To 4 If (Int(tp / (10 ^ y)) * (10 ^ y)) Mod (10 ^ x) = 0 Then GoTo lp Debug.Print (Int(tp / (10 ^ y)) * (10 ^ y)) Mod (10 ^ x) + bt lp: Next y Next x Debug.Print bt End Sub 
Sep 19 2019, 10:38 AM Post#4  
Access Wiki and Forums Moderator Posts: 76,381 Joined: 19June 07 From: SunnySandyEggo  Hi Vince. Glad to hear you understood what the OP wanted, 'cause I couldn't. Cheers!  Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know"  Kansas Access Website  Access Blog  Email 
Sep 19 2019, 10:49 AM Post#5  
Posts: 68 Joined: 18August 16 From: Bristol, UK  I'm not sure I fully understood. As far as I can tell there can only be a total of 15 combinations from the description (excluding zeroes), but the example seem to have 22? I still can't think of why though! 
Sep 19 2019, 08:25 PM Post#6  
UtterAccess VIP Posts: 10,081 Joined: 25October 10 From: Gulf South USA  Hi: Since you said you wanted all possible combinations, and the numbers would always be five digits, I thought I would see if all the possible combinations were listed in your post before looking at the logic to create the results. My analysis counts 29 combinations, not just the 22 you listed. Here's what I got, your combinations and my additional seven ... B1 + T1 B2 + T2 B3 + T3 B4 + T4 B5 + T5 = (55775) B1 + T1 B2 + T2 B3 + T3 B4 + T4 B5 + 00 = (55774) B1 + T1 B2 + T2 B3 + T3 B4 + 00 B5 + 00 = (55724) B1 + T1 B2 + T2 B3 + 00 B4 + 00 B5 + 00 = (55324) B1 + T1 B2 + 00 B3 + T3 B4 + T4 B5 + T5 = (52775) B1 + T1 B2 + 00 B3 + T3 B4 + T4 B5 + 00 = (52774) B1 + T1 B2 + 00 B3 + T3 B4 + 00 B5 + 00 = (52724) B1 + T1 B2 + 00 B3 + 00 B4 + T4 B5 + T5 = (52375) B1 + T1 B2 + 00 B3 + 00 B4 + T4 B5 + 00 = (52374) B1 + T1 B2 + 00 B3 + 00 B4 + 00 B5 + T5 = (52325) B1 + T1 B2 + 00 B3 + 00 B4 + 00 B5 + 00 = (52324) B1 + 00 B2 + T2 B3 + T3 B4 + T4 B5 + T5 = (35775) B1 + 00 B2 + T2 B3 + T3 B4 + T4 B5 + 00 = (35774) B1 + 00 B2 + T2 B3 + T3 B4 + 00 B5 + 00 = (35724) B1 + 00 B2 + T2 B3 + 00 B4 + T4 B5 + 00 = (35375) B1 + 00 B2 + 00 B3 + T3 B4 + T4 B5 + T5 = (32775) B1 + 00 B2 + 00 B3 + T3 B4 + T4 B5 + 00 = (32774) B1 + 00 B2 + 00 B3 + T3 B4 + 00 B5 + 00 = (32724) B1 + 00 B2 + 00 B3 + 00 B4 + T4 B5 + T5 = (32375) B1 + 00 B2 + 00 B3 + 00 B4 + T4 B5 + 00 = (32374) B1 + 00 B2 + 00 B3 + 00 B4 + 00 B5 + T5 = (32325) B1 + 00 B2 + 00 B3 + 00 B4 + 00 B5 + 00 = (32324) B1 + T1 B2 + T2 B3 + T3 B4 + 00 B5 + T5 = (55725) B1 + T1 B2 + T2 B3 + 00 B4 + T4 B5 + T5 = (55375) B1 + T1 B2 + T2 B3 + 00 B4 + T4 B5 + 00 = (55375) B1 + T1 B2 + T2 B3 + 00 B4 + 00 B5 + T5 = (55325) B1 + T1 B2 + 00 B3 + T3 B4 + 00 B5 + T5 = (52725) B1 + 00 B2 + T2 B3 + 00 B4 + 00 B5 + T5 = (35325) B1 + 00 B2 + 00 B3 + T3 B4 + 00 B5 + T5 = (32725) I reformatted the display so i could see the combinations better, then made a combinations logic chart on paper to see what turned up. Is this correct? Or am I misunderstanding your logic? Take a look and see if we can understand the required results before starting to write code to produce them. And I am with theDBguy  what is this for? And what is the range of individual digits that can be included? Will a digit in B ever be added to a digit in T that sums to more than 9? If so, how do you carry over the result in that position, if at all? HTH Joe  "Each problem that I solved became a rule, which served afterwards to solve other problems." "You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing." Rene Descartes 15961650 (Mathematician and Philosopher) 
Sep 19 2019, 10:00 PM Post#7  
Posts: 102 Joined: 3February 03  I guess I should have explained what this is for: I am master keying a series of locks, and I want to make sure that none of the keys in the system will be capable of opening more than one lock. Master keying a lock requires multiple pins for each key cut which allows more than one key to open the lock. The pins are referenced as bottom pins and top pins, and the numbers 1 thru 5 apply to the 5 individual cuts of the key. The bottom pin for each cut is the smallest cut of either the user key or the master key. The top pin makes up the difference between the smallest cut and the deepest cut. As a byproduct of having bottom and top pins, there are a number of combinations of key cuts that will operate the lock. My objective is to make sure that none of the combinations for a single lock can be opened by a different key that is also being used in the same system. The possible combinations are all the variations of bottom pins and bottom and top pins. I began to write some code that would insert all of the possible keys for a mastered lock into a table that could then be compared against any new key that might be added to the system and be rejected if that key works on another existing lock. As I was writing the code to cover each of the possibilities line by line, I realized there must be a way to do a for next loop that not only makes it less complicated but also makes sure that I haven't missed any possible combinations. Vince, I'm sure that you are on the right track. I ran your code and it returned 12 possibilities, all of which are correct combinations. However, as you can see by my manual matrix there are at least 22 combinations if I haven't missed any. I'm unfamiliar with the ^ operator so not sure how to modify your code to include the missing possibilities. Thanks, Neal 
Sep 20 2019, 02:11 AM Post#8  
Posts: 68 Joined: 18August 16 From: Bristol, UK  To understand what I have done take a look at the attached Excel file (which also contains the VBA as above, but it is not used in the sheet): First strip digits from end: 23451 =INT($A$2/(10^$A21))*(10^$A21) 0 23451 1 23450 2 23400 3 23000 4 20000 Then strip digits from start: =MOD($A$10,10^B$10) 23451 5 4 3 2 1 23451 3451 451 51 1 Combine the two: 23451 5 4 3 2 1 0 23451 3451 451 51 1 1 23450 3450 450 50 0 2 23400 3400 400 0 0 3 23000 3000 0 0 0 4 20000 0 0 0 0 The ^ operator raises to the power of, so by using 10^x (where x=0,1,2,3,4) you get 1,10,100,1000, etc. What I have not allowed for is having the individual digits in different positions which I'm guessing must be a possibility in your situation? I'll have a bit more of a think, but maybe you could use a query to get the individual digits, then use a cartesian join to get all combinations? Attached File(s) 
Sep 20 2019, 07:57 AM Post#9  
Posts: 102 Joined: 3February 03  RJD, Thanks for confirming that I was missing some possibilities! I think I have a clearer way of explaining what I'm trying to do: _______________Cut1____ Cut2____Cut3____ Cut4____Cut5 bottom number____3________2______3________2______4 top number_______2________3______4________5______1 There are 2 possibilities for each cut number ( bottom number only or (bottom number + top number)) _______________Cut1____ Cut2____Cut3____ Cut4____Cut5 Possibility 1_______3________2______3________2______4 Possibility 2_______5________5______7________7______5 The order of the cut numbers do not change. Cut1 will always be 3 or 5 Cut2 will always be 2 or 5 Cut3 will always be 3 or 7 Cut4 will always be 2 or 7 Cut5 will always be 4 or 5 I am trying to find all of the possible combinations of these 2 possibilities of these 5 cut numbers. Thank you very much for your assistance! Sup 
Sep 20 2019, 08:50 AM Post#10  
Posts: 68 Joined: 18August 16 From: Bristol, UK  This has been driving me nuts! I did as one of my previous suggestions and used repeated cartesian join queries to drop each digit in turn to zero. Obviously this gave lots of repeats, but picking unique values gave the following for the 'top' number which does show an definite sequence. I've run out of time now, but the following sequence does make it easier to visualise so you may be able to take it from here. I make it 32 combinations. 0 32324 1 32325 50 32374 51 32375 400 32724 401 32725 450 32774 451 32775 3000 35324 3001 35325 3050 35374 3051 35375 3400 35724 3401 35725 3450 35774 3451 35775 20000 52324 20001 52325 20050 52374 20051 52375 20400 52724 20401 52725 20450 52774 20451 52775 23000 55324 23001 55325 23050 55374 23051 55375 23400 55724 23401 55725 23450 55774 23451 55775 This post has been edited by Vince: Sep 20 2019, 08:53 AM 
Sep 20 2019, 10:03 AM Post#11  
UtterAccess VIP Posts: 10,081 Joined: 25October 10 From: Gulf South USA  QUOTE Vince: I make it 32 combinations. You are correct. I left out a set in my logic chart analysis, and now I get 32 as you indicated, not the 29 I previously posted (a bit sloppy on my part). B1 + T1 B2 + 00 B3 + T3 B4 + T4 B5 + 00 = (52774) B1 + T1 B2 + 00 B3 + T3 B4 + 00 B5 + T5 = (52725) B1 + T1 B2 + 00 B3 + 00 B4 + T4 B5 + T5 = (52375) At least now we seem to have a complete set of possible results... Joe  "Each problem that I solved became a rule, which served afterwards to solve other problems." "You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing." Rene Descartes 15961650 (Mathematician and Philosopher) 
Sep 20 2019, 04:46 PM Post#12  
UtterAccess VIP Posts: 10,081 Joined: 25October 10 From: Gulf South USA  Hi: Well, you can write some looping code or calculations to get most of the results you want  or you can just bruteforce the whole thing. Since you will always have five digits and a result set of 32 items, then once the bruteforce (onebyone calculations) setup is done, no further work seems required except usage. And the bruteforce calculations fell out pretty easily. Here is a demo using bruteforce  that is, specifying the B and T values, then specifying how to calculate each of the 32 results. Please note that in no case can the total of any position exceed nine in this solution. I had asked before about how you would handle carryovers if two digits added to more than 9, but it doesn't seem we have addressed that yet  or maybe I just missed it. Take a look and see if this is what you are trying to do. This includes creation of a table of results and a report thereof. Note that you must run the list creation before the report. You can change this, of course, to force a new list before the report, but I will leave that to you. And you could alter specifying the B and T values, but, again, I leave that to you. HTH Joe Attached File(s)  "Each problem that I solved became a rule, which served afterwards to solve other problems." "You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing." Rene Descartes 15961650 (Mathematician and Philosopher) 
Sep 21 2019, 01:07 PM Post#13  
Posts: 1,445 Joined: 2April 09 From: somewhere out there...  I am getting 32 combination: CODE Public Function fncKeys(tk1 As Integer, tk2 As Integer, tk3 As Integer, tk4 As Integer, tk5 As Integer, _ bk1 As Integer, bk2 As Integer, bk3 As Integer, bk4 As Integer, bk5 As Integer) As Collection ' tkX = top key ' bkX = bottom key ' ak(X) = top + bottom key ' ae = loop counter ' ' returns an collection of long integer ' Dim ak1 As Integer, ak2 As Integer, ak3 As Integer, ak4 As Integer, ak5 As Integer Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer Dim m1 As Integer, m2 As Integer, m3 As Integer, m4 As Integer, m5 As Integer Dim ak(1 To 5) As Integer Dim result As New Collection ak(1) = CInt(Right$(tk1 + bk1, 1)) ak(2) = CInt(Right$(tk2 + bk2, 1)) ak(3) = CInt(Right$(tk3 + bk3, 1)) ak(4) = CInt(Right$(tk4 + bk4, 1)) ak(5) = CInt(Right$(tk5 + bk5, 1)) For a = 1 To 2 For b = 1 To 2 For c = 1 To 2 For d = 1 To 2 For e = 1 To 2 m1 = Choose(a, bk1, ak(1)) m2 = Choose(b, bk2, ak(2)) m3 = Choose(c, bk3, ak(3)) m4 = Choose(d, bk4, ak(4)) m5 = Choose(e, bk5, ak(5)) On Error Resume Next result.Add CLng(m1 & m2 & m3 & m4 & m5), (m1 & m2 & m3 & m4 & m5) On Error GoTo 0 Next e Next d Next c Next b Next a Set fncKeys = result End Function Public Function getDIGIT(num As Long, idx As Integer) As Integer getDIGIT = CInt(Mid$(num, idx, 1)) End Function ''''''''' ' arnelgp ' test ''''''''' Private Sub test() Const bottom As Long = 32324 Const top As Long = 23451 Dim b1 As Integer, b2 As Integer, b3 As Integer, b4 As Integer, b5 As Integer Dim t1 As Integer, t2 As Integer, t3 As Integer, t4 As Integer, t5 As Integer Dim result As Variant Dim i As Integer b1 = getDIGIT(bottom, 1) b2 = getDIGIT(bottom, 2) b3 = getDIGIT(bottom, 3) b4 = getDIGIT(bottom, 4) b5 = getDIGIT(bottom, 5) t1 = getDIGIT(top, 1) t2 = getDIGIT(top, 2) t3 = getDIGIT(top, 3) t4 = getDIGIT(top, 4) t5 = getDIGIT(top, 5) Set result = fncKeys(t1, t2, t3, t4, t5, b1, b2, b3, b4, b5) Debug.Print result.count Debug.Print For i = 1 To result.count Debug.Print result(i) Next End Sub This post has been edited by arnelgp: Sep 21 2019, 01:14 PM  Never stop learning, because life never stops teaching. 
Sep 22 2019, 07:30 AM Post#14  
Posts: 102 Joined: 3February 03  I started out with brute force, but realized I was likely missing some of the possibilities. I tried to create a loop that covered them all, but couldn't figure it out. Thank you Vince , Thank you RJD, and Thank you arnelgp for your assistance! Sup 
Sep 23 2019, 09:11 AM Post#15  
Posts: 68 Joined: 18August 16 From: Bristol, UK  For what it's worth attached is a solution using just queries. I've had to split into five separate queries, but the full end result is qry_lvl5. Someone a lot better than me may be able to combine it into one query? Attached File(s) 
Sep 24 2019, 08:48 AM Post#16  
Posts: 2,678 Joined: 4February 07 From: USA, Florida, Delray Beach 
This post has been edited by ADezii: Sep 24 2019, 08:50 AM 
Sep 26 2019, 07:40 AM Post#17  
Posts: 2,678 Joined: 4February 07 From: USA, Florida, Delray Beach 

Sep 26 2019, 01:47 PM Post#18  
Posts: 2,678 Joined: 4February 07 From: USA, Florida, Delray Beach  @supmktg: QUOTE There be be a Bug in my Logic as far as the last post goes, but I'm not really sure. I am referring to the occasions where 5s may exist in both the Bottom & Top numbers for the same Cut position. Can you please replace the question marks with the appropriate Values? Thanks. CODE _______________Cut1____ Cut2____Cut3____ Cut4____Cut5 bottom number____3________2______3________2______5 top number_______2________3______4________5______5 There are 2 possibilities for each cut number (bottom number only or (bottom number + top number) _______________Cut1____ Cut2____Cut3____ Cut4____Cut5 Possibility 1_______3________2______3________2______5 Possibility 2_______5________5______7________7______? <== need clarification The order of the cut numbers do not change. Cut1 will always be 3 or 5 Cut2 will always be 2 or 5 Cut3 will always be 3 or 7 Cut4 will always be 2 or 7 Cut5 will always be 5 or ? <== need clarification This post has been edited by ADezii: Sep 26 2019, 01:48 PM 
Custom Search

Search Top LoFi  16th October 2019  02:31 PM 