Complex query need help??

[MrCC via AccessMonster.com]

Dear all

i need to make the following query


-mantable
john
david

-DataTable
ID man1name man2name
1 john David
2 john john

need to make a query to be output

ID manname one/two
1 john 1
1 david 2
2 john 1
2 john 2

i have made already a query and it works fine but when man1name & man2name is
the same name i got one row result
ID manname one/two
2 john 2

and missing 2 john 1


so any tips how can i make such query ??


thanks all

bye

 

[Ken Snell \(MVP\)]

Use a union query to normalize the data:

SELECT T1.ID, T1.Man1Name AS ManName, 1 AS [One/Two]
FROM DataTable AS T1
UNION ALL
SELECT T2.ID, T2.Man2Name AS ManName, 2 AS [One/Two]
FROM DataTable AS T2;

 

[MrCC via AccessMonster.com]

done

thx alot

Use a union query to normalize the data:

SELECT T1.ID, T1.Man1Name AS ManName, 1 AS [One/Two]
FROM DataTable AS T1
UNION ALL
SELECT T2.ID, T2.Man2Name AS ManName, 2 AS [One/Two]
FROM DataTable AS T2;

Dear all

[quoted text clipped - 30 lines]

bye