Symple query

[sergiogroups]

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?

 

[Guest]

TRy this --

TRANSFORM First(T_Contacts.Name) AS FirstOfName
SELECT T_Contacts.FamilyID
FROM T_Contacts
GROUP BY T_Contacts.FamilyID
PIVOT IIf([Gender]="M","Husband","Wife");

 

[John Vinson]

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?

There are several; Karl's elegant Crosstab is one, another uses a Self
Join:

SELECT [T_Contacts_M].[Name] AS Husband, [T_Contacts_F].[Name] AS Wife
FROM [T_Contacts] AS [T_Contacts_M]
INNER JOIN [T_Contacts] AS [T_Contacts_F]
ON [T_Contacts_M].[FamilyID] = [T_Contacts_F].[FamilyID]
WHERE [T_Contacts_M].[Gender] = "M"
AND [T_Contacts_F].[Gender] = "F"
ORDER BY Wife, Husband;

John W. Vinson[MVP]