Your tables are not structured correctly..   The best way to structure them
is to have this:
===============
Table Teams
TeamID (autonumber, Primary Key)
TeamName (text)

Table Match
MatchID (autonumber)
TeamID1  (number)      //CHANGED
TeamID2  (number)      //ADDED
HomeTeam (number)    //CHANGED

===============

Then everything becomes very easy and you don't have a lot of redundant
data.

> Match
>    MatchID   TeamID1   TeamID2   HomeTeam
>       1            22               24            22
>       2            22               21            21
>       3            23               21            23

Table Match would be your parent and Table Teams would be your children.

SELECT
    Match.MatchID,
    (SELECT Teams.TeamName FROM Teams WHERE Teams.TeamID=Match.TeamsID1) as
Team1,
    (SELECT Teams.TeamName FROM Teams WHERE Teams.TeamID=Match.TeamsID2) as
Team2,
    (SELECT Teams.TeamName FROM Teams WHERE Teams.TeamID=Match.HomeTeam) as
HomeTeam
FROM
    Match
WHERE
    MatchID=1

Select statements in a select statement is needed, because you can't have a
join on a table that is a look up from three different fields.  SQL/Access
doesn't know where the pointer is supposed to be and if it did the pointer
would be in three different places, causing it's own issues.


"Peter Foti (PeterF)" <PeterF@S...> wrote in message
news:170638@a..._databases...
>
> 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
> >
>
>

I guess if you can't change the data structure you could do:

SELECT
    Match.MatchID,
    Teams.TeamName
    Match.HomeTeam
FROM
    Match
LEF JOIN
    Teams ON Teams.TeamID=Match.TeamID
ORDER BY
    MatchID

Then just deal with having two records..   Just for short typing reasons,
I'm doing a recordset loop, but using GetRows, puting it into an array,
closeing the recordset is a better way to go.

<%
Do While Not objRS.EOF
    sMatchID = objRS("MatchID")
    sTeam1 =  objRS("TeamName")
    If objRS("HomeTeam") Then sHomeTeam = objRS("TeamName")

    objRS.MoveNext
    If Not objRS.EOF Then
        sTeam2 = objRS("TeamName")
        If objRS("HomeTeam") Then sHomeTeam = objRS("TeamName")
    End If

    Response.Write("Match: " & sMatchID & " " & sTeam1 & " vs. " & sTeam2 &
" (" & sHomeTeam & " are the Home Team)")

    objRS.MoveNext
Loop
%>