Lets say I have a table with a "Number" field. In the number field there are the numbers 0-9. What is the best way to design a query that would have fields "Number1, Number2, ....Number7" that would give me all the possible combinations of the numbers in the "Number" field without repeating any numbers in each record? (ie. 1234567, 1234568, 0987654, etc)

I know there are extremely easy ways to do this programmatically without using a database at all but this question is basically an extremely simplified version of what I'm trying to do. Currently the only way I am coming up with is to open 7 instances of the table and select the number field from each one with the criteria being "<>Number1 and <>Number2" and so on to exclude the numbers chosen in the fields before it.

I'm sure I am making this way harder than it needs to be and probably just confused more people when trying to explain it.

Do youi just need all of the combinations, or do you need every permutation of every combination? In other words, if you already have 1234567 is 7654321 necessary or forbidden?

What have you covered in class so far? Specifically, can you tell what this assignment is trying to teach you?

-PatP

Actually I need all the permutations.

It isn't an assignment for a class, its actually for a game I'm currently designing. It is going to be a 3D version of the game Boggle played on a 3x3x3 cube that can be rotated to see the various faces. I'm currently working on a way to find all of the allowable words on the cube. I'm starting with 7 letter words only and then I'll expand from there. I have a table in the DB with all 7 letter words (from the English language) divided into 7 seperate fields, each field is a text field max length 1 character. I then have a table that is generated for each new cube. The fields are FaceName (byte), Letter (text), X Coord (byte), Y Coord (byte), Z Coord (byte).

I'm working on generating possible 7 letter combinations where ((X1-X2)^2 + (Y1-Y2)^2 + (Z1-Z2)^2) <=2. That will give me only the cubes bordering each other. I just need a way to eliminate it from repeating the same cubeface twice.

I'm basically making the game purely for my own amusement right now. I'll be programming it in VB but thought that using a DB would be easier and allow me to do my since I don't know enough code to do everything purely in VB.