#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    2
    Rep Power
    0

    MS SQL equivalent of MS Access lookup fields


    MS SQL equivalent of MS Access lookup fields


    I would be most grateful if someone could help me. I wish to upgrade my Access db to MS SQL but I am unable to replicate the lookup fields I use in Access in SQL.

    In Access I have a Results table with a Column for HomeTeam and Away Team. These two columns use a basic lookup to select the team name as listed in the Team table.

    i.e. SELECT [Team].[Team] FROM Team ORDER BY [Team].[Team];

    I am sure this must really straight forward to accomplish in SQL but I am really struggling with it.

    Any help would be much appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    in sql server, "lookup" tables are just tables

    (they are just tables in access too)

    they just usually happen to have only two fields, a code and a description
    Code:
    select A.name as awayteamname
         , R.awayscore
         , H.name as hometeamname
         , R.homescore     
      from results R 
    inner
      join teams A
        on R.awayteamid = A.teamid
    inner
      join teams H
        on R.hometeamid = H.teamid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    2
    Rep Power
    0
    Thanks r937.

    Unfortunately I don't think I explained myself very well. What I am actually hoping to do is populate a listbox with this data - like I have in access - but in a column within an SQL table.

    Do you know if this is possible?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    well, sorta, if i understand you, you would create the teams table as a table in sql server, but i don't think sql server comes with listboxes -- it is a database engine, full stop

    whereas access is a combination of front end and database engine

    listboxes are front end thingies

    you *could* set up access as your front end and use sql server as the database engine

    but i'm speculating wildly here, and you'll ahve to be more specific to get me to go on...

IMN logo majestic logo threadwatch logo seochat tools logo