Delete Query Error Message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to delete records from a table where the records in the duplicate
table are missing.

delete *
from table1 Left Outer Join table1replica
where (table1.empnum = table2.empnum) is null

I'm not actually using this code, I'm using the GUI, but I cant cut and
paste the picture. I keep getting an error message that says "could not
delete from specified tables" This delete query worked for other tables, and
I don't understand why it's not working for this one

Please Help!
 
It's hard to say what the problem may be without seeing the actual Access
SQL. Try switching from the design view (GUI) to SQL view, and then copy the
exact text and post it. Hopefully someone will be able to spot the problem.

-Ted Allen
 
Here's the code:

DELETE [Performance Review Import].*, [Performance Reviews].[Employee Number]
FROM [Performance Reviews] LEFT JOIN [Performance Review Import] ON
[Performance Reviews].[Employee Number] = [Performance Review
Import].[Employee Number]
WHERE ((([Performance Reviews].[Employee Number]) Is Null));
 
The problem may be that you have the field [Performance Reviews].[Employee
Number] in the DELETE clause (in addition to the *). In the GUI, look at the
[Performance Reviews].[Employee Number] field and see if it says "FROM" under
it. If so, change it to WHERE.

In addition, it seems that you either have the join backwards, or have
selected to delete from the wrong table (Since you are left joining on
Performance Reviews, you shouldn't encounter any nulls in that table as
specified in the WHERE clause unless they are currently null in the table to
start with). Post back and let me know which table you want to delete the
records from (based on missing records in the other table), and I should be
able to correct the code for you (which you could then just paste into the
SQL view).

HTH, Ted Allen

Msslove97 said:
Here's the code:

DELETE [Performance Review Import].*, [Performance Reviews].[Employee Number]
FROM [Performance Reviews] LEFT JOIN [Performance Review Import] ON
[Performance Reviews].[Employee Number] = [Performance Review
Import].[Employee Number]
WHERE ((([Performance Reviews].[Employee Number]) Is Null));


Ted Allen said:
It's hard to say what the problem may be without seeing the actual Access
SQL. Try switching from the design view (GUI) to SQL view, and then copy the
exact text and post it. Hopefully someone will be able to spot the problem.

-Ted Allen
Click to expand...
Click to expand...
 
I checked the GUI and the [Performance Reviews].[Employee Number] field says
"FROM"
I would like to delete records from the performace reviews table where the
records in the performace reviews import table are missing. Or where
[Performance Reviews].[Employee Number] <> [Performance Reviews
Import].[Employee Number]

Thanks
Ted Allen said:
The problem may be that you have the field [Performance Reviews].[Employee
Number] in the DELETE clause (in addition to the *). In the GUI, look at the
[Performance Reviews].[Employee Number] field and see if it says "FROM" under
it. If so, change it to WHERE.

In addition, it seems that you either have the join backwards, or have
selected to delete from the wrong table (Since you are left joining on
Performance Reviews, you shouldn't encounter any nulls in that table as
specified in the WHERE clause unless they are currently null in the table to
start with). Post back and let me know which table you want to delete the
records from (based on missing records in the other table), and I should be
able to correct the code for you (which you could then just paste into the
SQL view).

HTH, Ted Allen

Msslove97 said:
Here's the code:

DELETE [Performance Review Import].*, [Performance Reviews].[Employee Number]
FROM [Performance Reviews] LEFT JOIN [Performance Review Import] ON
[Performance Reviews].[Employee Number] = [Performance Review
Import].[Employee Number]
WHERE ((([Performance Reviews].[Employee Number]) Is Null));


Ted Allen said:
It's hard to say what the problem may be without seeing the actual Access
SQL. Try switching from the design view (GUI) to SQL view, and then copy the
exact text and post it. Hopefully someone will be able to spot the problem.

-Ted Allen

:

I am trying to delete records from a table where the records in the duplicate
table are missing.

delete *
from table1 Left Outer Join table1replica
where (table1.empnum = table2.empnum) is null

I'm not actually using this code, I'm using the GUI, but I cant cut and
paste the picture. I keep getting an error message that says "could not
delete from specified tables" This delete query worked for other tables, and
I don't understand why it's not working for this one

Please Help!
Click to expand...
Click to expand...
Click to expand...
 
Sorry for the delayed response, I was off on Fri.

I think the following will do what you are trying to do:

DELETE [Performance Reviews].* FROM [Performance Reviews] LEFT JOIN
[Performance Review Import] ON [Performance Reviews].[Employee Number] =
[Performance Review Import].[Employee Number] WHERE [Performance Review
Import].[Employee Number] Is Null;

This query will delete all records from the Performance Reviews table that
do not have a matching Employee Number in the Performance Review Import table.

You can post this sql text in the sql view of a new query. As always, it is
a good idea to back up your database before running a delete query just in
case something goes wrong. Post back if it doesn't work.

HTH, Ted Allen


Msslove97 said:
I checked the GUI and the [Performance Reviews].[Employee Number] field says
"FROM"
I would like to delete records from the performace reviews table where the
records in the performace reviews import table are missing. Or where
[Performance Reviews].[Employee Number] <> [Performance Reviews
Import].[Employee Number]

Thanks
Ted Allen said:
The problem may be that you have the field [Performance Reviews].[Employee
Number] in the DELETE clause (in addition to the *). In the GUI, look at the
[Performance Reviews].[Employee Number] field and see if it says "FROM" under
it. If so, change it to WHERE.

In addition, it seems that you either have the join backwards, or have
selected to delete from the wrong table (Since you are left joining on
Performance Reviews, you shouldn't encounter any nulls in that table as
specified in the WHERE clause unless they are currently null in the table to
start with). Post back and let me know which table you want to delete the
records from (based on missing records in the other table), and I should be
able to correct the code for you (which you could then just paste into the
SQL view).

HTH, Ted Allen

Msslove97 said:
Here's the code:

DELETE [Performance Review Import].*, [Performance Reviews].[Employee Number]
FROM [Performance Reviews] LEFT JOIN [Performance Review Import] ON
[Performance Reviews].[Employee Number] = [Performance Review
Import].[Employee Number]
WHERE ((([Performance Reviews].[Employee Number]) Is Null));


:

It's hard to say what the problem may be without seeing the actual Access
SQL. Try switching from the design view (GUI) to SQL view, and then copy the
exact text and post it. Hopefully someone will be able to spot the problem.

-Ted Allen

:

I am trying to delete records from a table where the records in the duplicate
table are missing.

delete *
from table1 Left Outer Join table1replica
where (table1.empnum = table2.empnum) is null

I'm not actually using this code, I'm using the GUI, but I cant cut and
paste the picture. I keep getting an error message that says "could not
delete from specified tables" This delete query worked for other tables, and
I don't understand why it's not working for this one

Please Help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top