Determine all zip code combinations 4

[bustersports]

Hi,

I am trying to determine all possible zip code combinations for the first 3 digits. They then must be combined into a 6 character field used in queries based on zip codes. Ex: 111xx and 765xx would be combined into 111765 and so on thru all possible combinations. The zip codes are used in reviewing paid invoices. We do not get the entire zip code just airport or shipping port codes. Does anyone have a suggestion on how to generate this list? The process will be when pulling in the data to be reviewed, the port codes will be matched to the appropriate combination for the user to review. I understand this may sound silly to some, but it is the process the user wants to have available. I think the table should only need generated one time, but expect thousands of records. This is way too many to do it by hand. Any suggestions would be greatly appreciated. Thanks very much.

 

[MajP]

Make a table
tblDigit
digit (integer)

put in values of 0 through 9. Open the query developer. Drop that table in 3 times. Add the field three times. You will get a Cartesian product of all possibilities. Make a calculated field that concatenates the three fields. If You want to save it then use this to run a make table query.
Now you will have a table with all possibilities
000
to
999

 

[bustersports]

Thanks very much for the VERY quick response. It was quite easy.

 

[MazeWorX]

That deserves a second star

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MajP]

Since that was found interesting here are some more useful ideas.


A permutation is an arrangment of items where order matters
A combination is an arrangment of items where the order does not matter

123 and 321 are two different permutations
123 and 321 are the same combination.

A combination lock is a bad name and should be a "permutation lock" because the order matters. If you dial 321 it is different than 123 and will not open.

For both combinations and permutations there are two types
1 Permuations with replacement
2 Permutations without replacement (cannot reuse values)
3 combinations with replacement
4 combinations without replacement

In these example assume the possible values (Ni) are 1,2,3,4, and you are choosing 3 (R) items at a time.

The data is stored in
tblItms
itm (a field with 1,2,3,4)

1) Permutations with replacement means I can have values 1,1,1 up to 9,9,9
There are N*N*N possible ways to choose or N^R. In this case 4^3 = 64. This is what was previously demonstrated as a Cartesian product.

SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
ORDER BY A.itm, B.itm, C.itm;

A	B	C
1	1	1
1	1	2
1	1	3
1	1	4
1	2	1
1	2	2
1	2	3
1	2	4
1	3	1
1	3	2
1	3	3
.....
4	4	4

2) Permuations without replacement. If you pick a number you cannot reuse it. So 1,2,1 or 1,2,2 are not allowed.
So in this case you have N ways to choose the first number, N-1 for the second, to N-R + 1

= N!/(N-R)! = 4!/1! = 24

SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
WHERE (((B.itm)<>[A].[itm] And (B.itm)<>[c].[itm]) AND ((C.itm)<>[B].[itm] And (C.itm)<>[A].[itm]))
ORDER BY A.itm, B.itm, C.itm

A	B	C
1	2	3
1	2	4
1	3	2
1	3	4
1	4	2
1	4	3
2	1	3
2	1	4
2	3	1
2	3	4
2	4	1
2	4	3
3	1	2
3	1	4
3	2	1
3	2	4
3	4	1
3	4	2
4	1	2
4	1	3
4	2	1
4	2	3
4	3	1
4	3	2

3) Combination without replacement. Same as above except 1,2,3 is no different then 3,2,1 no differnt than 2,1,3
There are
N!/R!(N-R)! = 4!/3!*1! = 24/6 = 4

SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
WHERE (((B.itm)>[a].[itm]) AND ((C.itm)>[b].[itm]))
ORDER BY A.itm, B.itm, C.itm;

A	B	C
1	2	3
1	2	4
1	3	4
2	3	4

4) Combinations with Replacement. This one is a little tricky. May be an easier way. You find this by finding all the combinations where each item is different, all combinations where you repeat 2 of the items, and all the combinations where you repeat each item and so forth.

So 1,1,2 is allowed but is is no different from 2,1,1
qrySingles (all values are equal)

SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
WHERE (((B.itm)=[a].[itm]) AND ((C.itm)=[b].[itm]))
ORDER BY A.itm, B.itm, C.itm;

qryDoubles (2 values are the same)

SELECT A.itm AS A, B.itm AS B, C.itm AS C
FROM tblItems AS A, tblItems AS B, tblItems AS C
WHERE (((B.itm)=[a].[itm]) AND ((C.itm)<>[b].[itm]))
ORDER BY A.itm, B.itm, C.itm;

Triples is the same as combinations without replacement
so

select *, "3 Triples" as Type from qrycombinationsWithoutReplacement 
union select * ,"2 Doubles" from qryDoubles
UNION select *, "1 Singles" from qrySingles
ORDER BY 1, 2, 3, 4;

the number is
(N + r - 1)! /R!(n-1)! = 6!/3!*3! = 720 / 36 = 20

Type	A	B	C
1 Singles	1	1	1
1 Singles	2	2	2
1 Singles	3	3	3
1 Singles	4	4	4
2 Doubles	1	1	2
2 Doubles	1	1	3
2 Doubles	1	1	4
2 Doubles	2	2	1
2 Doubles	2	2	3
2 Doubles	2	2	4
2 Doubles	3	3	1
2 Doubles	3	3	2
2 Doubles	3	3	4
2 Doubles	4	4	1
2 Doubles	4	4	2
2 Doubles	4	4	3
3 Triples	1	2	3
3 Triples	1	2	4
3 Triples	1	3	4
3 Triples	2	3	4

http://www.mathsisfun.com/combinatorics/combinations-permutations.html

 

[JonFer]

For "4) Combinations with replacements", this WHERE clause will do the trick in a single query and can easily be expanded for more digits.

a.Digit<=b.Digit AND
b.Digit<=c.Digit

 

[MajP]

Thanks. Knew there had to be an easier way.

 

[bustersports]

All,

Thanks for the excellent responses on this. Very much appreciated, hopefully it will be beneficial to others in the future.