Welcome to SPN

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

Sign Up Now!

Filter on a QUERY

Discussion in 'Information Technology' started by Bob Richardson, Nov 5, 2005.

Tags:
  1. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    I want my drop down list to include only the classes from the selected
    event. Here's my query, but it's including every class from every event, not
    just the classes in the selected event.
    SELECT Classes.ClassCode FROM Classes WHERE Classes.Event=Event ORDER BY
    [ClassCode];

    I also tried to link the tables, (because there is a one to many
    relationship from Events to Classes) but this didn't work either. :(

    SELECT Classes.ClassCode FROM Events INNER JOIN Classes ON
    Events.Event=Classes.Event ORDER BY Classes.ClassCode;
     
  2. Loading...

    Similar Threads Forum Date
    General Filter Test Hard Talk May 20, 2008
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 4 Nov 2005 17:09:06 -0800, "Bob Richardson" <bobr at whidbey
    dot com> wrote:

    >I want my drop down list to include only the classes from the selected
    >event. Here's my query, but it's including every class from every event, not
    >just the classes in the selected event.
    >SELECT Classes.ClassCode FROM Classes WHERE Classes.Event=Event ORDER BY
    >[ClassCode];


    Well, that's not working because Classes.Event=Event is comparing the
    Event field from Classes to itself - there's no other table, so Event
    by itself simply refers to the Event field in this table. This
    obviously is always true, since the value must be equal to itself.

    >I also tried to link the tables, (because there is a one to many
    >relationship from Events to Classes) but this didn't work either. :(
    >
    >SELECT Classes.ClassCode FROM Events INNER JOIN Classes ON
    >Events.Event=Classes.Event ORDER BY Classes.ClassCode;
    >


    What constitutes the *SELECTED* event? Is this on a Form? If so, you
    can use the Form control containing the Event as a criterion:

    SELECT Classes.ClassCode FROM Classes
    WHERE Classes.Event = Forms!YourForm!Event
    ORDER BY ClassCode;

    You'll need to Requery the combo in the AfterUpdate event of the
    control whereby the user selects or defines the Event.

    John W. Vinson[MVP]
     
  4. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    You need the full reference to the Ontrol that has the Event selected. In
    your SQL, Event will be the same as Class.Event so it is always True.

    It sounds like you have a set of Cascaded ComboBoxes. If this is the case,
    see The Access Web article:

    http://www.mvps.org/access/forms/frm0028.htm

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Bob Richardson" <bobr at whidbey dot com> wrote in message
    news:r7WdnUy6P4Szm_HenZ2dnUVZ_tydnZ2d@whidbeytel.com...
    >I want my drop down list to include only the classes from the selected
    >event. Here's my query, but it's including every class from every event,
    >not just the classes in the selected event.
    > SELECT Classes.ClassCode FROM Classes WHERE Classes.Event=Event ORDER BY
    > [ClassCode];
    >
    > I also tried to link the tables, (because there is a one to many
    > relationship from Events to Classes) but this didn't work either. :(
    >
    > SELECT Classes.ClassCode FROM Events INNER JOIN Classes ON
    > Events.Event=Classes.Event ORDER BY Classes.ClassCode;
    >
     
  5. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    Your SELECT worked perfectly. There is a form (key = event) and a subform
    (key=event/class)

    The idea is to have the appropriate class subform appear when the user
    clicks the desired class on the drop-down list. How would that onclick event
    look. I'm most concerned because the sub-form record has two fields in its
    key...event and class.


    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:bpbom1t8iktovsnhc6conmfceke3u39urv@4ax.com...
    > On Fri, 4 Nov 2005 17:09:06 -0800, "Bob Richardson" <bobr at whidbey
    > dot com> wrote:
    >
    >>I want my drop down list to include only the classes from the selected
    >>event. Here's my query, but it's including every class from every event,
    >>not
    >>just the classes in the selected event.
    >>SELECT Classes.ClassCode FROM Classes WHERE Classes.Event=Event ORDER BY
    >>[ClassCode];

    >
    > Well, that's not working because Classes.Event=Event is comparing the
    > Event field from Classes to itself - there's no other table, so Event
    > by itself simply refers to the Event field in this table. This
    > obviously is always true, since the value must be equal to itself.
    >
    >>I also tried to link the tables, (because there is a one to many
    >>relationship from Events to Classes) but this didn't work either. :(
    >>
    >>SELECT Classes.ClassCode FROM Events INNER JOIN Classes ON
    >>Events.Event=Classes.Event ORDER BY Classes.ClassCode;
    >>

    >
    > What constitutes the *SELECTED* event? Is this on a Form? If so, you
    > can use the Form control containing the Event as a criterion:
    >
    > SELECT Classes.ClassCode FROM Classes
    > WHERE Classes.Event = Forms!YourForm!Event
    > ORDER BY ClassCode;
    >
    > You'll need to Requery the combo in the AfterUpdate event of the
    > control whereby the user selects or defines the Event.
    >
    > John W. Vinson[MVP]
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 4 Nov 2005 20:58:36 -0800, "Bob Richardson" <bobr at whidbey
    dot com> wrote:

    >Your SELECT worked perfectly. There is a form (key = event) and a subform
    >(key=event/class)
    >
    >The idea is to have the appropriate class subform appear when the user
    >clicks the desired class on the drop-down list. How would that onclick event
    >look. I'm most concerned because the sub-form record has two fields in its
    >key...event and class.


    One way (if I understand you correctly) would be to have the subform's
    Master Link Fields property be the two controls on the mainform
    containing Event and Class, separated by semicolons; and the Child
    Link Field be

    Event;Class

    This will automatically display only the records pertaining to that
    combination.

    John W. Vinson[MVP]
     

Share This Page