joining tables across mulitiple columns

If I have two tables (Members, Teams) with Teams having multiple foreign keys to the Members table how would go about selecting each team and getting the members names into the recordset as well. for example

--- Members --__----------- Teams ---------
ID_Name______ID_Member1_Member2
1__Rob_______1__1_______2
2__Tom_______2__3_______4
3__Jim
4__Dave


And I wanna return from a query...
TeamID__Member1__Member2
1_______Rob______Tom
2_______Jim_______Dave

What combinations of joins, subqueries, etc would I have to use in order to get that result??? Thanks, rob

Is this your complete table structure? Just 2 members per team?

well actually it is 4 members but just as an example i showed two

The would be much easier if you had 3 tables.


Members_Table
MemID (Primary Key)
FirstName
LastName
Address
etc.

Teams_Table
TeamID (Primary Key)
TeamName

Roster_Table
TeamID (Foreign Key)
MemID (Foreign Key)

You could then just select the MemID and TeamID from the Roster_Table. No joins. No subqueries. Just a simple select.