se**********@gmail.com wrote:
If It have a table T_Contacts:
Name Gender FamilyID
John M 1
Paul M 2
Anna F 1
Kate F 2
And want to create a table T_Couples:
Husband Wife
John Anna
Paul Kate
which is the query to do that?
this should get you started .
SELECT Man.FamilyID, Man.FName As Husband, Wife.FName As Wife
FROM tblPeopl AS Man INNER JOIN tblPeopl AS Wife ON Man.FamilyID =
Wife.FamilyID
WHERE (((Man.Gender)="M") AND ((Wife.Gender)="F"));
i'm splitting out Man and Wife as two datasets in the same table, and
then joining the resulting sets of FamilyID. the where clause just
puts the men in one group and the women in another, so that I can join
on FamilyID.
Then just turn this into a maketable query and you're good to go.