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