Professional Web Applications Themes

MS access SQL query help! - Macromedia Dreamweaver

I am creating a database for sports league. I have a table of Leagues, Teams, and Games. I trying to get a listing of the games but I am getting too much data returned. My problem is I am unable to create a query that only returns 1 listing of a game. My tables are as such: ****tblTeams PrimaryKey- TeamID TeamName ForeignKey- LeagueID **** tblGames PrimaryKey- GameID TeamName ForeignKey- LeagueID HomeID --- This is the linked to tblTeams.TeamID These relationships are the root of my problem. VisitorID --- This is the linked to tblTeams.TeamID ****tblLeagues PrimaryKey- LeaugueID LeagueName I would ...

  1. #1

    Default MS access SQL query help!

    I am creating a database for sports league.

    I have a table of Leagues, Teams, and Games. I trying to get a listing of the games but I am getting too much data returned. My problem is I am unable to create a query that only returns 1 listing of a game.

    My tables are as such:

    ****tblTeams
    PrimaryKey- TeamID
    TeamName
    ForeignKey- LeagueID

    **** tblGames
    PrimaryKey- GameID
    TeamName
    ForeignKey- LeagueID
    HomeID --- This is the linked to tblTeams.TeamID These relationships are the root of my problem.
    VisitorID --- This is the linked to tblTeams.TeamID

    ****tblLeagues
    PrimaryKey- LeaugueID
    LeagueName


    I would like to see each game listed once instead of twice and would like to have the team names replaced for the VisitorID and HomeID. I could just enter in the team name but I would like to keep the linked tables.

    SELECT DISTINCT Games.GameID, Games.Location, Games.StartDate, Games.StartTime, Games.HomeID, Games.VisitorID, Games.HomeTotal, Games.VisitorTotal, Games.LeagueID, Teams.TeamName
    FROM Teams LEFT JOIN Games ON Teams.LeagueID=Games.LeagueID;

    Any help would be greatly appriciated.


    You can see a picture of the database relationships here at [url]www.wou-wou.com/tablerelationships.htm[/url]

    Thanks,

    Ralph
    [email]ralph_sharetthotmail.com[/email]



    Ralph Sharett Guest

  2. #2

    Default Re: MS access SQL query help!

    I would guess (only a guess though) that you need to link the GameID in the Grade table with the GameID in the games table...
    Elliott Guest

Similar Threads

  1. Access Query
    By dave in forum Coldfusion Database Access
    Replies: 5
    Last Post: February 21st, 08:20 PM
  2. MS Access query in MX 7
    By Eric Hung in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 19th, 03:31 AM
  3. Access query help
    By Brandon Taylor in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 12th, 09:52 PM
  4. Getting 0 recordset in MS Access Query
    By jt in forum ASP Database
    Replies: 4
    Last Post: May 24th, 04:27 AM
  5. Access Query in ASP
    By Ambassador in forum ASP Database
    Replies: 2
    Last Post: March 2nd, 06:08 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139