Delete Query

  • Thread starter Thread starter Tina
  • Start date Start date
T

Tina

I have two tables. I am trying to delete records from one
table (table 1) that exist in the other (table 2). I am
using design mode (I don't know SQL). I've opened a
delete query and selected the two tables that I want to
use in my query. I've selected the asterisk from table 1
to be "Deleted From" I've included the field from table 1
(the field is called "number")that needs to be searched
for a match in table 2 and in the criteria I've typed in
=Table 2.number. I'm very new with Access and would
appreciate any advice. Please be gentle as I am a novice.

Tina
 
You can try the following SQL.

DELETE From Delete_Table
where Key_Field = (select Key_Field from Second_Table);

This will delete record in Delete_Table that Key_Field is
matched by the Second_Table.
 
Based on your input this is what I have typed in SQL.
However I keep getting asked for a parameter value. Thanks
for your help


DELETE *
FROM Linkbase
WHERE (Linkbase.NUMBER) = (LinkedDeletions18-1.Number);
 
You need to use square brackets as you have special characters in names.
Try:

DELETE *
FROM Linkbase
WHERE (Linkbase.[NUMBER]) = ([LinkedDeletions18-1].[Number]);

I am not sure whether "Number" is a reserved word or not so I alse enclosed
it in square brackets.
 
Thanks! In addition to the corrections you had me make, I
must be doing something else wrong. Whenever I try to go
to datasheet view (so I can see what I'll be deleting) I
still keep getting a box pop up asking me to input a
parameter value. I've left the value field blank and hit
okay but then there are no items in datasheet view.
Thanks for all your help

Tina
-----Original Message-----
You need to use square brackets as you have special characters in names.
Try:

DELETE *
FROM Linkbase
WHERE (Linkbase.[NUMBER]) = ([LinkedDeletions18-1]. [Number]);

I am not sure whether "Number" is a reserved word or not so I alse enclosed
it in square brackets.

--
HTH
Van T. Dinh
MVP (Access)


Based on your input this is what I have typed in SQL.
However I keep getting asked for a parameter value. Thanks
for your help


DELETE *
FROM Linkbase
WHERE (Linkbase.NUMBER) = (LinkedDeletions18-1.Number);
Click to expand...


.
Click to expand...
 
Someone seems to be missing a table to join to.

Back to the query you built in the grid. UNCHECK the field in the second table
so it does not show in the query. That will probably fix your query. If that
doesn't work. I suggest you switch to the SQL view in your non-functioning
query and paste the SQL statement to the newsgroup. That will give enough
information (normally) to suggest the changes you need to make to your SQL.

Thanks! In addition to the corrections you had me make, I
must be doing something else wrong. Whenever I try to go
to datasheet view (so I can see what I'll be deleting) I
still keep getting a box pop up asking me to input a
parameter value. I've left the value field blank and hit
okay but then there are no items in datasheet view.
Thanks for all your help

Tina
-----Original Message-----
You need to use square brackets as you have special characters in names.
Try:

DELETE *
FROM Linkbase
WHERE (Linkbase.[NUMBER]) = ([LinkedDeletions18-1]. [Number]);

I am not sure whether "Number" is a reserved word or not so I alse enclosed
it in square brackets.

--
HTH
Van T. Dinh
MVP (Access)


Based on your input this is what I have typed in SQL.
However I keep getting asked for a parameter value. Thanks
for your help


DELETE *
FROM Linkbase
WHERE (Linkbase.NUMBER) = (LinkedDeletions18-1.Number);
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks, My Sql Statement looks like this

DELETE Linkbase.*, Linkbase.NUMBER
FROM Linkbase, [Linked Deletions18-1]
WHERE (((Linkbase.NUMBER)=[Linked Deletions18-1].
[Number]));

I finally got the records I needto delete in Datasheet
view, however when I hit the Delete! button I get a
message "Could not delete from specified table". Any
ideas?

-----Original Message-----
Someone seems to be missing a table to join to.

Back to the query you built in the grid. UNCHECK the field in the second table
so it does not show in the query. That will probably fix your query. If that
doesn't work. I suggest you switch to the SQL view in your non-functioning
query and paste the SQL statement to the newsgroup. That will give enough
information (normally) to suggest the changes you need to make to your SQL.

[email protected] said:
Thanks! In addition to the corrections you had me make, I
must be doing something else wrong. Whenever I try to go
to datasheet view (so I can see what I'll be deleting) I
still keep getting a box pop up asking me to input a
parameter value. I've left the value field blank and hit
okay but then there are no items in datasheet view.
Thanks for all your help

Tina
-----Original Message-----
You need to use square brackets as you have special characters in names.
Try:

DELETE *
FROM Linkbase
WHERE (Linkbase.[NUMBER]) = ([LinkedDeletions18-1]. [Number]);

I am not sure whether "Number" is a reserved word or
Click to expand...
not
so I alse enclosed
it in square brackets.

--
HTH
Van T. Dinh
MVP (Access)


Based on your input this is what I have typed in SQL.
However I keep getting asked for a parameter value. Thanks
for your help


DELETE *
FROM Linkbase
WHERE (Linkbase.NUMBER) = (LinkedDeletions18- 1.Number);





.
Click to expand...
Click to expand...
.
Click to expand...
 
Try the following variations in Access. They ALL SHOULD work, but at least one
of them should work.

DELETE Linkbase.*
FROM Linkbase INNER JOIN [Linked Deletions18-1]
ON Linkbase.NUMBER=[Linked Deletions18-1].[Number]

DELETE DISTINCTROW Linkbase.*
FROM Linkbase INNER JOIN [Linked Deletions18-1]
ON Linkbase.NUMBER=[Linked Deletions18-1].[Number]

DELETE Linkbase.*
FROM Linkbase
WHERE LinkBase.NUMBER IN
(SELECT [Linked Deletions18-1].[Number]
FROM [Linked Deletions18-1].[Number])

DELETE DISTINCTROW Linkbase.*
FROM Linkbase
WHERE LinkBase.NUMBER IN
(SELECT [Linked Deletions18-1].[Number]
FROM [Linked Deletions18-1].[Number])


Thanks, My Sql Statement looks like this

DELETE Linkbase.*, Linkbase.NUMBER
FROM Linkbase, [Linked Deletions18-1]
WHERE (((Linkbase.NUMBER)=[Linked Deletions18-1].
[Number]));

I finally got the records I needto delete in Datasheet
view, however when I hit the Delete! button I get a
message "Could not delete from specified table". Any
ideas?
-----Original Message-----
Someone seems to be missing a table to join to.

Back to the query you built in the grid. UNCHECK the field in the second table
so it does not show in the query. That will probably fix your query. If that
doesn't work. I suggest you switch to the SQL view in your non-functioning
query and paste the SQL statement to the newsgroup. That will give enough
information (normally) to suggest the changes you need to make to your SQL.

[email protected] said:
Thanks! In addition to the corrections you had me make, I
must be doing something else wrong. Whenever I try to go
to datasheet view (so I can see what I'll be deleting) I
still keep getting a box pop up asking me to input a
parameter value. I've left the value field blank and hit
okay but then there are no items in datasheet view.
Thanks for all your help

Tina
-----Original Message-----
You need to use square brackets as you have special
characters in names.
Try:

DELETE *
FROM Linkbase
WHERE (Linkbase.[NUMBER]) = ([LinkedDeletions18-1].
[Number]);

I am not sure whether "Number" is a reserved word or not
so I alse enclosed
it in square brackets.

--
HTH
Van T. Dinh
MVP (Access)


message
Based on your input this is what I have typed in SQL.
However I keep getting asked for a parameter value.
Thanks
for your help


DELETE *
FROM Linkbase
WHERE (Linkbase.NUMBER) = (LinkedDeletions18- 1.Number);





.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top