If you can change your database design, you could do something like
this:

Table Teams
TeamID (autonumber, Primary Key)
TeamName (text)

Table Match
MatchID (autonumber)
TeamID  (number)
HomeTeam (true/false)

In the Match table, the combination of MatchID and TeamID would make the
primary key.  So records might look like this:

Teams:
   TeamID   TeamName
     21     Red Sox
     22     Yankees
     23     Mets
     24     Cubs

Match
   MatchID   TeamID   HomeTeam
      1        22       True
      1        24       False
      2        22       False
      2        21       True
      3        23       True
      3        21       False


So this means:
Match 1:  Yankees vs. Cubs (Yankees are the home team)
Match 2:  Yankees vs. Red Sox (Red Sox are the home team)
Match 3:  Mets vs. Red Sox (Mets are the home team)

Hope this helps.
Pete


> -----Original Message-----
> From: Frode [mailto:fstroemm@o...]
> Sent: Wednesday, May 01, 2002 4:12 PM
> To: ASP Databases
> Subject: [asp_databases] Table relationship
> 
> 
> Hi
> 
> Say I have a Table Match with MatchID,HomeTeam,AwayTeam and a 
> Table Teams 
> with TeamID,TeamName.
> 
> How could I make it so both HomeTeam and AwayTeam relate to the Teams 
> table (access or sql server) ?
> 
> Using a SELECT statement and then using recordset("HomeTeam") 
> gives me 
> just the number while using recordset("TeamName") will give 
> the same name 
> when I'm just using one recordset query.
> 
> thanks 
> 
> - Frode
> 

Hi

Say I have a Table Match with MatchID,HomeTeam,AwayTeam and a Table Teams 
with TeamID,TeamName.

How could I make it so both HomeTeam and AwayTeam relate to the Teams 
table (access or sql server) ?

Using a SELECT statement and then using recordset("HomeTeam") gives me 
just the number while using recordset("TeamName") will give the same name 
when I'm just using one recordset query.

thanks 

- Frode