Create Unduplicated Combinations

[jmenche]

Howdy,

I'll just try and explain what I am looking for. Let's say I have a field called 'Flavor' with 10 flavors in it. I want to create all unique combinations against itself (Chocolate/Vanilla, Chocolate/Strawberry, etc.). This is a simple cartesian product of 100 combinations (10x10) created by adding my Flavor table to a query twice and not linking them. I can even get rid of unnecessary dupe combos like Chocolate/Chocolate. However, combos like Chocolate/Vanilla and Vanilla/Chocolate are technically the same thing but come up when I run my query. How can I get rid of these combos as well?

Thanks
:beerchug:

[CreganTur]

The only idea I can give you is to create a loop that will combine all the names and have a module level array. Within the loop you will check the array to see if the combination exists. If no, then add to array. If yes, then do not add to array.

I could come up with a much more elegant solution if only VBA allowed Lists.

[hansup]

You could add a unique number column to your flavors table ... I used an autonumber primary key, flavor_id ... then use a query similar to this to return the combinations you want:
SELECT f1.flavor_name, f2.flavor_name
FROM Flavors AS f1, Flavors AS f2
WHERE (f1.flavor_id / f2.flavor_id) > 1;