Append Query problem

[Jeff]

I am attempting to create an Append query involving three tables but
am having trouble. This is what I'm trying to do.

Table A has a field called "Email", as does Table B. I start the query
as a SELECT query and in the grid I add the email field from Table A
and set the criteria to match the email field in Table B (these two
tables are not already directly related by the way). This part works
correctly. It only selects the email field in Table A that matches a
corresponding email in Table B.

Table A is already related to Table C by a field I'll call
"StudentID" (the field is called this in both tables). The "StudentID"
in table A is the primary key, and in Table C it is a foreign key.
It's a one-to-many relationship (inner join).

So here is what I'm ultimately trying to do. Select the record where
the email address from Table A and Table B are equal (I already
mentioned this part above) AND there is a "Student ID" that is in
Table A where there is NOT a corresponding "StudentID" in Table C;
then append a new record into Table C with this "StudentID" (which
would be put into Table C's "StudentID" field).

Does this make sense? In other words only select the primary key from
Table A where:

1--Table A has a corresponding value from another field in table C.
2--This key field from Table A ALSO does NOT have a corresponding
value in Table C (in it's foreign key).

When these two criteria are met,a new record is created in Table C
with it's foreign key set as Table A's corresponding primary key.

I can only seem to get the first criteria part to work, and then after
that I'm pretty much lost. Any suggestions are much appreciated.
Thanks!

 

[KARL DEWEY]

The "StudentID" in table A is the primary key, and in Table C it is a
foreign key.
It's a one-to-many relationship (inner join).
Make this a Left Join in your query - table A left of Table C.
Add table A StudentID and table C StudentID to the design view grid. As
criteria for table C StudentID enter 'Null' without quotes.

If this does not help then post your query as you have it now.

 

[Jeff]

Thanks Karl, I tried this before and when I turn the inner joint to a
left join I get the error message about having an "ambiguous join". So
basically if I do JUST my criteria 1, or JUST do the left join, I
don't get this error message. However if I do BOTH it gives the
message about having an ambiguous join.

Wait...I just figured it out. Instead of setting the criteria of Table
A to show records where the values of the email fields are equal to
those in Table C, I actually created an INNER JOIN between the two
email fields (not sure why this would make a difference but it did).
Then as you suggested I changed the inner join between Table A and
Table C to a LEFT JOIN, and set the criteria to Table C's "StudentID"
to be NULL.

Thanks for you help Karl! Hope this solution is of use to someone
else.
--Jeff