Is this query possible?

[Guest]

I have a database with a table for children (ChildIDnumber, name, birthday,
etc) and a table called SignUps (SignUpNumber, ChildIDNumber, Session,
Class, etc.) They have a one to many relationship: one child can sign up for
many classes. I would like to query my SignUps table and get a list of
children who signed up last session (Spring05) but have not signed up for
this session (Fall05). I can't figure out how to do this. Help!

 

[Brendan Reynolds]

I have a sneaking suspicion there might be a way to achieve this by
combining inner and outer joins between the two tables, which might be more
efficient than my suggestion, but I can't quite get a grip on that idea. The
following works, though, and unless you have huge numbers of records,
whether it is the most efficient method may be a moot point ...

SELECT Children.*
FROM Children
WHERE (((Children.ChildID) In (SELECT ChildID FROM SignUps WHERE Session =
'Spring 05')) AND ((Children.ChildID) Not In (SELECT ChildID FROM SignUps
WHERE Session = 'Fall 05')));

 

[Guest]

When you link the tables, link two copies of the SignUps table and make one
of the links a regular join, the other the type of outer join where the
SignUps table record is not required.

On your query, you'll have your kids names, of course, but also include
Session fields from both SignUps tables and the SignUpNumber from the table
with the outer join.

On the normal join Session criteria, put the Session value where they
attended. On the Outer join session criteria, put the Session value where
they did not attend. On the outer join SignUpNumber criteria, put "Is Null".

To prevent kids from showing up multiple times (if they took more than one
class in the session), you go to Properties and check Unique Values = True.

I BET this works. (Not entirely sure, but I think it is close.) There is a
Wizard for building queries to look for values that aren't present in a table
(one of the standard query-building Wizards) and it does something rather
similar.

- Phil Freihofner

 

[Guest]

Thanks Phil and Brendan. That's exactly what I was looking for. I was
struggling with both these concepts and couldn't figure out how to do it
either way! I'll try both and see what works best. I really appreciate your
help!

~Marcy

 

[Guest]

Phil,

It worked beautifully! Thanks again.

Marcy