Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Lookup SQL WHERE

Discussion in 'Information Technology' started by Bob Richardson, Oct 27, 2005.

Tags:
  1. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    I'm trying to design a Student table that will use a lookup to a Class
    table. Classes are provided ONLY for people in certain grades, e.g. Class
    101 is only given to grade 9, class 221 is only given to grade 10, etc.

    Class Table: Grade, ClassCode
    9 101
    9 103
    9 110
    10 101
    10 201
    10 221
    etc.

    Each Student can choose 2 classes, so I want a lookup table in two different
    fields that will only show, in a drop down list, those classes that are
    available to the grade that the student is in.

    Student Table: StudentID, Grade, Class1, Class2

    I tried the following, with no success:

    This is the Row Source of the LookUp tab for Student.Class1

    SELECT Class.ClassCode FROM Class WHERE Grade=Class.Grade ORDER BY
    ClassCode;
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    You are pulling the value Grade from the form and you want Class.Grade to be
    equal to that.

    SELECT Class.ClassCode FROM Class WHERE Class.Grade = Forms!FormName!Grade
    ORDER BY ClassCode;

    If may be that what you have will work if you reverse the equal statement in
    the WHERE clause, is not, then specify the full path to the Grade value on
    the form as indicated above.

    --
    Wayne Morgan
    MS Access MVP


    "Bob Richardson" <bobr at whidbey dot com> wrote in message
    news:p_6dnWOfQYXxKMLeRVn-rw@whidbeytel.com...
    > I'm trying to design a Student table that will use a lookup to a Class
    > table. Classes are provided ONLY for people in certain grades, e.g. Class
    > 101 is only given to grade 9, class 221 is only given to grade 10, etc.
    >
    > Class Table: Grade, ClassCode
    > 9 101
    > 9 103
    > 9 110
    > 10 101
    > 10 201
    > 10 221
    > etc.
    >
    > Each Student can choose 2 classes, so I want a lookup table in two
    > different fields that will only show, in a drop down list, those classes
    > that are available to the grade that the student is in.
    >
    > Student Table: StudentID, Grade, Class1, Class2
    >
    > I tried the following, with no success:
    >
    > This is the Row Source of the LookUp tab for Student.Class1
    >
    > SELECT Class.ClassCode FROM Class WHERE Grade=Class.Grade ORDER BY
    > ClassCode;
    >
     
  4. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    Thank you. I see where that will work when using a form. I was hoping to
    establish this lookup in the design of the Student table. Then, if entering
    data with the Datasheet view, there would be an appropriately filtered
    drop-down list in the Class column, depending on the value in the Grade
    column.


    "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
    news:OMIUfcl2FHA.1876@TK2MSFTNGP10.phx.gbl...
    > You are pulling the value Grade from the form and you want Class.Grade to
    > be equal to that.
    >
    > SELECT Class.ClassCode FROM Class WHERE Class.Grade = Forms!FormName!Grade
    > ORDER BY ClassCode;
    >
    > If may be that what you have will work if you reverse the equal statement
    > in the WHERE clause, is not, then specify the full path to the Grade value
    > on the form as indicated above.
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "Bob Richardson" <bobr at whidbey dot com> wrote in message
    > news:p_6dnWOfQYXxKMLeRVn-rw@whidbeytel.com...
    >> I'm trying to design a Student table that will use a lookup to a Class
    >> table. Classes are provided ONLY for people in certain grades, e.g. Class
    >> 101 is only given to grade 9, class 221 is only given to grade 10, etc.
    >>
    >> Class Table: Grade, ClassCode
    >> 9 101
    >> 9 103
    >> 9 110
    >> 10 101
    >> 10 201
    >> 10 221
    >> etc.
    >>
    >> Each Student can choose 2 classes, so I want a lookup table in two
    >> different fields that will only show, in a drop down list, those classes
    >> that are available to the grade that the student is in.
    >>
    >> Student Table: StudentID, Grade, Class1, Class2
    >>
    >> I tried the following, with no success:
    >>
    >> This is the Row Source of the LookUp tab for Student.Class1
    >>
    >> SELECT Class.ClassCode FROM Class WHERE Grade=Class.Grade ORDER BY
    >> ClassCode;
    >>

    >
    >
     
  5. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Sorry, you cannot have a filtered drop-down list depending on an "earlier"
    value in the DatasheetView of the Table. You need a Form, more
    specifically, Form / Control Events to do this.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Bob Richardson" <bobr at whidbey dot com> wrote in message
    news:wJydnSZE0-ePScLeRVn-gw@whidbeytel.com...
    > Thank you. I see where that will work when using a form. I was hoping to
    > establish this lookup in the design of the Student table. Then, if
    > entering data with the Datasheet view, there would be an appropriately
    > filtered drop-down list in the Class column, depending on the value in the
    > Grade column.
    >
    >
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 26 Oct 2005 12:08:23 -0700, "Bob Richardson" <bobr at whidbey
    dot com> wrote:

    >I was hoping to establish this lookup in the design of the Student table.


    You can't. Table datasheets have VERY limited functionality. This is
    among the many things you can't do, and among the many reasons you
    should not routinely use table datasheets.

    See http://www.mvps.org/access/lookupfields.htm for a critique of the
    Lookup Field misfeature.

    John W. Vinson[MVP]
     
  7. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    I read about the Evils. ISTM that Lookup tables can either put in the actual
    data or they can insert a key to the data. For example, to simplify entering
    city names, and increase the odds of the city being spelled correctly :) a
    lookup table can include lots of city names, especially the hard to
    spell/type ones.

    Most of the "evils" occur when you insert a key, not the actual data.

    What evils abound if I include the data, rather than a key, in the DB?


    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:7i00m1lo8mo1bcid5k8bet0oshl3ll1tq3@4ax.com...
    > On Wed, 26 Oct 2005 12:08:23 -0700, "Bob Richardson" <bobr at whidbey
    > dot com> wrote:
    >
    >>I was hoping to establish this lookup in the design of the Student table.

    >
    > You can't. Table datasheets have VERY limited functionality. This is
    > among the many things you can't do, and among the many reasons you
    > should not routinely use table datasheets.
    >
    > See http://www.mvps.org/access/lookupfields.htm for a critique of the
    > Lookup Field misfeature.
    >
    > John W. Vinson[MVP]
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 27 Oct 2005 10:14:49 -0700, "Bob Richardson" <bobr at whidbey
    dot com> wrote:

    >What evils abound if I include the data, rather than a key, in the DB?
    >


    Not too much. A bit of a waste of space - "Poughkeepsie" has 12 bytes,
    a numeric ID only 4. For US States (or Canadian provinces) the
    two-letter postal abbreviations are an excellent choice.

    John W. Vinson[MVP]
     

Share This Page