Filter on a QUERY

B

Bob Richardson

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;
 
J

John Vinson

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];
Click to expand...

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;
Click to expand...

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]
 
B

Bob Richardson

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 said:
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];
Click to expand...

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;
Click to expand...

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]
Click to expand...
 
J

John Vinson

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.
Click to expand...

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]
 
Top