Hey All,

Thanks for all your input. I've come up with some code that does what I want to do. It's a little more complicated than the simply finding all permutations, in that I want the names and addresses to come from the same record, not across all records. So all Name & Address combinations of a single record. However, it may not always be limited to simply name and address, sometimes there may be company names and/or phone numbers involved. Also there may be more than one field containing the desired data. So maybe 3 name fields, 2 business fields, 1 address field and 2 phone number fields.

Below is the code that does what I want to do. (Notice the flexibility in that any amount of groups can be added and each group can have any amount of group members) From here I guess I can scan through the resulting cursor to build the SQL SELECT command. Thank you all for your help and input!

Aloha,

James

LOCAL lnMember as Integer
LOCAL loGroups as Collection, loGroup as Collection, loNames as Collection, loAddress as Collection
STORE NULL TO loGroups,loGroup,loNames,loAddress
STORE 0 TO lnMember

loNames = CREATEOBJECT([Collection])
loAddress = CREATEOBJECT([Collection])
loPhone = CREATEOBJECT([Collection])
loGroups = CREATEOBJECT([Collection])

loNames.Add([Name1])
loNames.Add([Name2])
loNames.Add([Name3])
loAddress.Add([ResAddress])
loAddress.Add([BizAddress])
loAddress.Add([MailAddress])
loPhone.Add([HomePhone])
loPhone.Add([BizPhone])
loPhone.Add([MobilePhone])

loGroups.Add(loNames,[Names])
loGroups.Add(loAddress,[Address])
loGroups.Add(loPhone,[Phone])

CREATE CURSOR crsCombos (Combo c(50))

FOR EACH loGroup IN loGroups
	IF RECCOUNT([crsCombos]) = 0
		FOR EACH lcMember IN loGroup
			INSERT INTO crsCombos VALUES (lcMember)
		ENDFOR 
	ELSE 
		SELECT * FROM crsCombos INTO CURSOR crsOrigState
		FOR lnMember = 1 TO loGroup.Count
			lcMember = loGroup.Item(lnMember)
			IF lnMember = 1
				UPDATE crsCombos SET Combo=TRIM(Combo) + [+] + lcMember
			ELSE 
				SELECT * FROM crsOrigState INTO CURSOR crsTempAdd READWRITE 
				UPDATE crsTempAdd SET Combo=TRIM(Combo) + [+] + lcMember
				INSERT INTO crsCombos SELECT * FROM crsTempAdd
			ENDIF 
		ENDFOR 	
	ENDIF 
ENDFOR 

USE IN [crsTempAdd]
USE IN [crsOrigState]

SELECT crsCombos

BROWSE 

>>Hi All,
>>
>>I'm hoping someone can help me out here.
>>
>>I want to find out how I can generically obtain all the different combinations of group members. The trick is the number of groups is not set and neither is the number of group members.
>>
>>Example:
>>
>>A table has a group of name fields, could be one name, could be three or four names. The same table has a group of addresses, again could be one or two or three.
>>
>>I want to create a cursor that contains one record for each combination of name and address.
>>
>>Record 1: name1+address1
>>Record 2: name2+address1
>>Record 3: name3+address1
>>Record 4: name1+address2
>>Record 5: name2+address2
>>Record 6: name3+address2
>>
>>If it were only name and address that would be alright, however the trick is to get it to work so that another group could be added, say company names and there are more one or more company names as well.
>>
>>In that case the cursor should look something like the following:
>>
>>Record 1: name1+company1+address1
>>Record 2: name2+company1+address1
>>Record 3: name3+company1+address1
>>Record 4: name1+company2+address1
>>Record 5: name2+company2+address1
>>Record 6: name3+company2+address1
>>Record 7: name1+company1+address2
>>Record 8: name2+company1+address2
>>Record 9: name3+company1+address2
>>Record10: name1+company2+address2
>>Record11: name2+company2+address2
>>Record12: name3+company2+address2
>>
>>Aloha,
>>
>>James
>
>James,
>You mean a cartesian join?
>
>ie:
>

>select a.First_name,b.Last_name, c.address ;
>  from employee a,employee b, employee c ;
>  ORDER BY 1,2,3
>

>PS: If it's that be carefull result set could quickly get into huge sizes:
>Result set reccount = reccount(a)*reccount(b)*reccount(c) ... For employee with 3 fields = 15^3
>
>Cetin