Excel VBA Script for generating combinations

Hello Experts,

I need help with a script to generate a table of all PartID combinations with the following constraints:
Each PartID has a SeriesID = 1 to 7.
Each PartID has PartTypeID = A, B, C or D.
PartTypeA are connected to PartTypeB
PartTypeB are connected to PartTypeC
PartTypeC are connected to PartTypeD

The attached spreadsheet shows the possible combinations for Series 1, which I did manually. I would like a script that would can do it for all Series.

Hope the example is clear!

Cheers,

-Tom
combodata-ee.xlsx
tomfolinsbeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
So, you want a routine that reads down the "data" sheet, and generates both the "twopartcombo" and "fourpartcombo" sheets?

~bp
0
tomfolinsbeeAuthor Commented:
yes, that's right.
0
tomfolinsbeeAuthor Commented:
Update:
I no longer need the two part combo sheet.
I only need the four part combo sheet.
Cheers,
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

tomfolinsbeeAuthor Commented:
I've moved the project to Access. So now looking for a query instead of a script.

Restating the problem:

I have a table with three fields: SeriesID, PartTypeID and PartID.
(showing records only for SeriesID = 1)
1      1      3
1      1      7
1      2      6
1      2      8
1      3      4
1      4      5

I'm looking for a query that will return all possible combinations of PartID subject to contraint that every combination must have same Series and one and only one of each PartType.  

Thanks!

Tom
0
Bill PrewCommented:
See if this does what you wanted.

~bp
ee28237154.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomfolinsbeeAuthor Commented:
Sorry, I should have mentioned that when I moved to access, I changed the output table to ComboID, SeriesID, PartID, PartTypeID

Thanks for your help with this!

Tom
0
Bill PrewCommented:
So you would want to see 4 lines for the same ComboID then?

~bp
0
tomfolinsbeeAuthor Commented:
Yes, that's right.
0
tomfolinsbeeAuthor Commented:
bp, thanks for your help with this. It's exactly what I asked for in the original question.  

For my application,  I used your query to make a table (Model). I then used Union query to merge the four columns into one colum.

Cheers!
0
Bill PrewCommented:
Great, sorry I hadn't gotten back to do more yet, but glad that helped.

~bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.