All Combinations of Lists with an addition

[bjorgen]

I would like to output all the permutations of a list.

I can do this by just dumping each field and setting unique to yes, however I'd like to be able to include an "ALL" value for each field as the first value.

Any input here is greatly appreciated!

 

[Norie]

Create a query for each field with the unique values in that field.

Add all of those queries to a new query.

Add the single field from each table to the query.

Don't join anything or enter any criteria.

You should now have a list of all the permutations of all the values in all the fields.

I tried it with the data you posted and it results in 24 records which is 2x2x2x3.

 

[SydneyGeek]

One option is to create a unique list for each column. You can use SELECT DISTINCT for that; you will also need a query for each column
eg:
SELECT DISTINCT Vertical FROM MyList;

Now you have 4 queries (qryVertical, qryMedium, qryPartner, qryCostModel)

New query. Add those 4 queries, no joins.
Add the single field from each query to the grid. Sort as required

Denis

 

[bjorgen]

Thanks guys, this should get it done!