Multiple relations from one table to another

[0ddball]

Ok, I'm noew to this and I seem to have hit a wall.

I have two tables:

Matches
-matchID (autoNumber - primary Key)
-winnerID (number)
-loserID (number)
-datePlayed (Date/Time)
-victoryType (Text Lookup)
--(lkup)major
--(lkup)minor
--(lkup)technical

Teams
-teamID (Autonumber - primary key)
-name (Text)
-tag (Text)


Now I admit that I don't know what I'm doing here. The winnerID and the looserID are supposed to relate to the Teams table - but I don't know how to implement this.

I draw lines in the relationships window and when I look at it again its all changed.

Please treat me like I don't know anything about databases outside using ClarisWorks in highschool And that was a long time ago. Its amazing what management thing we should be able to do when we have a couple of letters after our name.

--> Oddball

 

[Transcend]

Were wer you dragging the lines? Did you save the relationship structure? You should connect the TeamID field with the winner id field, then create another connection from the TeamID field to the WinnerID field.

You do this by just clicking on teamid and dragging the mouse over to the other fields. Does this make sense?

Transcend

 

[0ddball]

I did that - I've worked out how the relationship structure window works, and I've successfuly set up relationships with other tables.

The problem I'm having stems directly from me linking the same table twice.

 

[Transcend]

Have you made sure that TeamID, WinnerID and LoserID are all the same data type?

Are you saying that its not letting you join the fields that you want to at all? Are you getting any errors?

Transcend

 

[Transcend]

I just created two tables similar to yours and i was able to create a link like you describe. Access just comes up with a message that "a relationship already exists, do you want to edit the relationship, click no to create a new relationship"

Sorry if I'm not being much help but I'm not exactly sure what isn't working for you

Transcend

 

[0ddball]

I've tried clicking 'No' and I end up even more confused. I have a long way to go, and its nice to see there are places out there that help the people who help people

Access creates a table called "Teams_1"

I can't find any mention of this table anywhere other than on the Relationships Window

 

[Transcend]

That's because it just creates a second table 'visually' so that the links don't get all confused. And so that when you write your queries it will use Teams_1 for one relationship, and Teams for the other. Teh both still refer to the same table. There is only one teams table in your database.

The bottom of this link explains it a little ..

http://dbforums.com/arch/112/2003/6/818951

Hope this helps!

Transcend

 

[0ddball]

I don't believe this I still can't get this down.

Could you give me the SQL statemant I need to select the matchID, datePlayed, Teams.name, Teams_1.name and victoryType.

I have a feeling I just need to see it once...

 

[lespaul]

SELECT MATCHID, DATEPLAYED, TEAMS.NAME, TEAMS1.NAME, VICTORYTYPE
FROM MATCHES
INNER JOIN TEAM ON MATCHES.WINNERID = TEAMS.TEAMID
INNER JOIN TEAM1 ON MATCHES.LOSERID = TEAMS1.TEAMID

 

[0ddball]

So THATS how it works

A world opens infront of me

Thanks folks

 

[0ddball]

SELECT matchID, datePlayed, victoryType, Teams.Name, Teams_1.Name FROM Matches INNER JOIN Teams ON Matches.winnerID=Teams.teamID INNER JOIN Teams_1 ON Matches.looserID=Teams_1.teamID;


What am I missing here? Apparently:

"Syntax Error (missing operator) in query expression "Matches.winnerID=Teams.teamID INNER JOIN Teams_1 ON Matches.looserID=Teams_1.teamID"

I'm |--| this far away from using three letter acronyms to express my frustration.

 

[lespaul]

SELECT matches.MatchID, matches.WinnerID, teams.teamname, matches.LoserID, teams_1.teamname
FROM teams AS teams_1 INNER JOIN (teams INNER JOIN matches ON teams.teamid = matches.WinnerID) ON teams_1.teamid = matches.LoserID;

try this, you may need to modify a little but it works for me!





Leslie