Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fixture list (2003)

    I have a table of football teams and want to create a 'fixture list'. That is, each team plays each other but they cannot play themselves?!
    I've created a query and added tblTeams twice, linked Team to Team in the table area, given the Team field(s) the aliases HomeTeam and AwayTeam. I've then tried the criteria for HomeTeam of '<>tblTeams_1!AwayTeam'. This doesn't work. Am I missing something? Andy.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fixture list (2003)

    The alias is local to the query - in other words, it doesn't change the field name in the table. '<>tblTeams_1!Team' should work instead (although I haven't tested this before posting!)
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Fixture list (2003)

    Andy,

    After creating your table that list teams, create a query that has the same table twice as you did. Now join the tables together on team and set it to select all records from table 1 and only where records in table 2 are equal (the join should look like an arrow pointing from one table to the other, left to right).

    Now, go to SQL view and change the join to read as follows:

    SELECT Table1.TEAM AS hOME, Table1_1.TEAM AS AWAY
    FROM Table1 LEFT JOIN Table1 AS Table1_1 ON Table1.TEAM <> Table1_1.TEAM;

    Basically you want the join to be <> instead of = on Table1.team <> Table1_1.team. Note that you will not be able to view this query in graphic mode after making the join change. I tried this on a table with 4 teams and got the following results.

    hOME AWAY
    TEAM1 TEAM2
    TEAM1 TEAM3
    TEAM1 TEAM4
    TEAM1 TEAM5
    TEAM2 TEAM1
    TEAM2 TEAM3
    TEAM2 TEAM4
    TEAM2 TEAM5
    TEAM3 TEAM1
    TEAM3 TEAM2
    TEAM3 TEAM4
    TEAM3 TEAM5
    TEAM4 TEAM1
    TEAM4 TEAM2
    TEAM4 TEAM3
    TEAM4 TEAM5
    TEAM5 TEAM1
    TEAM5 TEAM2
    TEAM5 TEAM3
    TEAM5 TEAM4
    Regards,

    Gary
    (It's been a while!)

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fixture list (2003)

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •