Deleting Outer Join Records

Ok, I have a database that contains a table with current construction
projects. It will soon contain a table with the updated constructions
projects. I have a Left Outer Join that returns the records that are in the
Original_Table but not in the Updated_Table. Using the following concept:

SELECT *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

Is there a way to get Access to delete these records? I changed it into a
delete query and I keep getting the following message:

"Specify the table containing the records that you want to delete."

I am assuming that this message appears because it adds the
[Updated_Table].ID field to the query. Does anyone have any suggestions?

Thanks in advance,
Suzie

Hi,


Add DISTINCTROW from the delete query:


DELETE DISTINCTROW tableNameToDeleteFrom.* FROM table1 SOME JOIN table2
.....


Note also that you have to specify FROM WHICH table you delete, since the
JOIN implies many tables, and you probably wisely wish to delete from just
one of them.



Hoping it may help,
Vanderghast, Access MVP


"Suzie Raboin" wrote in message
...
Ok, I have a database that contains a table with current construction
projects. It will soon contain a table with the updated constructions
projects. I have a Left Outer Join that returns the records that are in
the
Original_Table but not in the Updated_Table. Using the following concept:

SELECT *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

Is there a way to get Access to delete these records? I changed it into a
delete query and I keep getting the following message:

"Specify the table containing the records that you want to delete."

I am assuming that this message appears because it adds the
[Updated_Table].ID field to the query. Does anyone have any suggestions?

Thanks in advance,
Suzie

The DISTINCTROW doesn't help me much. How do I specify which table the
records should be deleted from? When I change the query to a delete query my
SQL looks like the following (this was created by Access when I changed the
type of query):

DELETE DISTINCTROW *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

This is when I get the error message.

Any other help is appreciated!

"Michel Walsh" wrote:

Hi,


Add DISTINCTROW from the delete query:


DELETE DISTINCTROW tableNameToDeleteFrom.* FROM table1 SOME JOIN table2
.....


Note also that you have to specify FROM WHICH table you delete, since the
JOIN implies many tables, and you probably wisely wish to delete from just
one of them.



Hoping it may help,
Vanderghast, Access MVP


"Suzie Raboin" wrote in message
...
Ok, I have a database that contains a table with current construction
projects. It will soon contain a table with the updated constructions
projects. I have a Left Outer Join that returns the records that are in
the
Original_Table but not in the Updated_Table. Using the following concept:

SELECT *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

Is there a way to get Access to delete these records? I changed it into a
delete query and I keep getting the following message:

"Specify the table containing the records that you want to delete."

I am assuming that this message appears because it adds the
[Updated_Table].ID field to the query. Does anyone have any suggestions?

Thanks in advance,
Suzie

Hi,


I would suspect you delete from the table Original_Table:


DELETE DISTINCTROW Original_Table.* FROM Original_Table LEFT JOIN ...


Vanderghast, Access MVP

"Suzie Raboin" wrote in message
...
The DISTINCTROW doesn't help me much. How do I specify which table the
records should be deleted from? When I change the query to a delete query
my
SQL looks like the following (this was created by Access when I changed
the
type of query):

DELETE DISTINCTROW *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

This is when I get the error message.

Any other help is appreciated!

"Michel Walsh" wrote:

Well, I've taken care of the problem. I'm still not really sure what was
wrong with it. I just started over and now it works fine. Thanks for your
help!

"Suzie Raboin" wrote:

The DISTINCTROW doesn't help me much. How do I specify which table the
records should be deleted from? When I change the query to a delete query my
SQL looks like the following (this was created by Access when I changed the
type of query):

DELETE DISTINCTROW *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

This is when I get the error message.

Any other help is appreciated!

"Michel Walsh" wrote:

Hi,


Add DISTINCTROW from the delete query:


DELETE DISTINCTROW tableNameToDeleteFrom.* FROM table1 SOME JOIN table2
.....


Note also that you have to specify FROM WHICH table you delete, since the
JOIN implies many tables, and you probably wisely wish to delete from just
one of them.



Hoping it may help,
Vanderghast, Access MVP


"Suzie Raboin" wrote in message
...
Ok, I have a database that contains a table with current construction
projects. It will soon contain a table with the updated constructions
projects. I have a Left Outer Join that returns the records that are in
the
Original_Table but not in the Updated_Table. Using the following concept:

SELECT *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

Is there a way to get Access to delete these records? I changed it into a
delete query and I keep getting the following message:

"Specify the table containing the records that you want to delete."

I am assuming that this message appears because it adds the
[Updated_Table].ID field to the query. Does anyone have any suggestions?

Thanks in advance,
Suzie