Help solve this query...

[Guest]

I need to find all the students who have not registered for a Fall 2005 couse
that were registered in either Fall 2004 or Spring 2005.

So far I have made a make table for all the students registered for Fall 04
and/or Spring 05. I've also made a make table for all the students
registered for Fall 2005.

I brought them into a new query and did an outer join from Fall04/Spring05
to Fall 05 (Arrow points to the Fall 05).

I can't figure out what to do from here. Any suggestions to get the list to
only the students who were registered for the prior 2 semesters but have not
registered for the upcoming Fall 05 one?

 

[Ken Snell [MVP]]

Sounds as if you've set it up correctly as a query, the only part missing is
to set a criterion for the primary key field in the Fall 05 table:

Fall05PrimaryKey Is Null

 

[Rick B]

I'd build a union query and save it that pulls all students with Fall04 or
Spring05 registries. (No need to make a table out of this since a query can
be called in a query just like a table could.)

I'd then build an UNMATCHED query that compares that Union query (no need to
make a new table) to the Fall05 table.

This unmatched query would show everyone with a record in the Union query
that does not have a record in the Fall05 table.

 

[Guest]

Thanks Ken and Rick!
Rick, I'm a new user and not quite sure I understand all that's in your
post. Ken, I tried what you suggested and nothing got returned.
I can't figure out what I'm doing wrong!
Thanks for the effort though!
Karen

 

[Ken Snell [MVP]]

You'll need to post the SQL statement of the query that you tried to use
(the one with the outer join). Note that I used a generic reference when I
posted Fall05PrimaryKey as a field name. (You can get the SQL statement by
switching the query to SQL view and copying the statement that is there.)

Also tell us what is the structure of the different tables that you're
using.

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Here is the SQL

SELECT zzz_tempPreviousTermsF05.StudentID
FROM zzz_TempPreviousTermsF04S05 LEFT JOIN zzz_tempPreviousTermsF05 ON
zzz_TempPreviousTermsF04S05.StudentID = zzz_tempPreviousTermsF05.StudentID
WHERE (((zzz_tempPreviousTermsF05.StudentID)="IsNull"));

Thanks again for you continued help! You are great!

 

[Ken Snell [MVP]]

Change it to this:

SELECT zzz_tempPreviousTermsF05.StudentID
FROM zzz_TempPreviousTermsF04S05 LEFT JOIN zzz_tempPreviousTermsF05 ON
zzz_TempPreviousTermsF04S05.StudentID = zzz_tempPreviousTermsF05.StudentID
WHERE zzz_tempPreviousTermsF05.StudentID Is Null;

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

When I go to run it; all that comes back are a bunch of blank cells. It's
better than the nothing I was getting before, but still no data.

 

[Ken Snell [MVP]]

You want the people from Fall04 and Spring05 who aren't registered for
Fall05, right? If yes, you're asking the query to show you the wrong fields.
You're asking the query to show the results from the Fall05 table; you want
the other table's fields:

SELECT zzz_tempPreviousTermsF04S05.StudentID
FROM zzz_TempPreviousTermsF04S05 LEFT JOIN zzz_tempPreviousTermsF05 ON
zzz_TempPreviousTermsF04S05.StudentID = zzz_tempPreviousTermsF05.StudentID
WHERE zzz_tempPreviousTermsF05.StudentID Is Null;