Unique Combinations

[Guest]

Hello,

What is the most effective way to find the *unique* combinations of values
within a given field?

e.g. If the values in a field were:

Tom
Mary
Steve
John

....the full set of unique combinations would be:

Tom Mary
Tom Steve
Tom John
Mary Steve
Mary John
Steve John

Ultimately, I'd like to be able to do this for combinations of 3,4,5 and 6
values. Maybe even more, but I appreciate the exponential nature of the query
might make higher numbers more difficult!

Any ideas?

Thanks & Best,
David

 

[Guest]

Create a query and add the same table twice. If Access creates a relationship
between the tables, delete it. This will create a cartisian join. Drag and
drop the name field from each table. Reference the other field in a Not
clause in the criteria of one of the field. The SQL would looks something
like:

SELECT Clients.Client, Clients_1.Client
FROM Clients, Clients AS Clients_1
WHERE ((Not (Clients.Client)=[Clients_1]![Client]));

Hopefully this is what you want.

 

[Guest]

Thanks Jerry. This is a similar solution to what I've found elsewhere.
However, this creates all permutations, rather than the unique combinations.
How can I filter this down?

The only way I could think of was to sort each record laterally, then
vertically, and remove the duplicates. I'd need to export the data to Excel
in order to do this though. Surely there's a better way!

Dave

Create a query and add the same table twice. If Access creates a relationship
between the tables, delete it. This will create a cartisian join. Drag and
drop the name field from each table. Reference the other field in a Not
clause in the criteria of one of the field. The SQL would looks something
like:

SELECT Clients.Client, Clients_1.Client
FROM Clients, Clients AS Clients_1
WHERE ((Not (Clients.Client)=[Clients_1]![Client]));

Hopefully this is what you want.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

What is the most effective way to find the *unique* combinations of values
within a given field?

e.g. If the values in a field were:

Tom
Mary
Steve
John

...the full set of unique combinations would be:

Tom Mary
Tom Steve
Tom John
Mary Steve
Mary John
Steve John

Ultimately, I'd like to be able to do this for combinations of 3,4,5 and 6
values. Maybe even more, but I appreciate the exponential nature of the query
might make higher numbers more difficult!

Any ideas?

Thanks & Best,
David