Use single SQL statement instead of 2 saved queries?

[Eric]

Hi:

I am using two multi-select listboxes. I use one saved query that refers to
another saved query, but I am just curious if there is a way to turn this
into a single SQL statement? I mostly only use the query design grid...

Query 1:

SELECT tblEvent.Event_ID, tblEvent.Event, Query2.Student_ID
FROM tblEvent LEFT JOIN Query2 ON tblEvent.Event_ID = Query2.Event_ID
WHERE (((Query2.Student_ID) Is Null));

Query 2:

SELECT tblStudentEvent.Student_ID, tblStudentEvent.Event_ID
FROM tblStudentEvent
WHERE
(((tblStudentEvent.Student_ID)=[forms]![frmStudentEvent]![txtStudent_ID]));

Thanks!
Eric

 

[John Spencer]

Your use of a parameter ([forms]![frmStudentEvent]![txtStudent_ID])in
query 2 precludes doing this all in one query.

You could build the query string on the fly using VBA. YOu would
substitute the value of [forms]![frmStudentEvent]![txtStudent_ID] in the
query string. Another alternative would be to use a function to get the
value and return it.

If you replaced [forms]![frmStudentEvent]![txtStudent_ID] with 211 (for
example) the query would look like.

SELECT tblEvent.Event_ID, tblEvent.Event, Query2.Student_ID
FROM tblEvent LEFT JOIN
(SELECT tblStudentEvent.Student_ID, tblStudentEvent.Event_ID
FROM tblStudentEvent
WHERE Student_ID=211) as Query2
ON tblEvent.Event_ID = Query2.Event_ID
WHERE (((Query2.Student_ID) Is Null));

You could accomplish the same thing using a correlated sub query in the
where clause, but it would probably be very slow

SELECT tblEvent.Event_ID, tblEvent.Event, Query2.Student_ID
FROM tblEvent
WHERE NOT Exists
(SELECT *
FROM tblStudentEvent
WHERE Student_ID = [forms]![frmStudentEvent]![txtStudent_ID] and
tblStudentEvent.Event_ID = tblEvent.Event_id)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Eric]

Thank you very much. That is helpful!

Eric

 

[John Spencer]

After re-evalutating this I think you can do the following. Note that I have
removed the square brackets around the reference to the form - which is
possible as long as your control name and form name don't require brackets.

SELECT tblEvent.Event_ID, tblEvent.Event, Query2.Student_ID
FROM tblEvent LEFT JOIN
(SELECT tblStudentEvent.Student_ID, tblStudentEvent.Event_ID
FROM tblStudentEvent
WHERE Student_ID=forms!frmStudentEvent!txtStudent_ID) as Query2
ON tblEvent.Event_ID = Query2.Event_ID
WHERE (((Query2.Student_ID) Is Null));


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County