Delete Qry

[kholm]

I am creating a delete query that should delete those items which match the first and last name of another table. It looks like this: Field: Last Name, Table: Chi-town, Delete: Where, Criteria: [Delete].[last name]. Same for first name.

When I go to run the query it says to specify the table containing the records you want to delete. I thought I was doing that? Help

Kristin

 

[Alexandre]

In the QBE grid, switch to SQL view, copy and paste the SQL statement here so we can see what it looks like

 

[kholm]

SQL View

Here is the info in SQL:
DELETE [CHI-TOWN].lastname, [CHI-TOWN].firstname, [CHI-TOWN].address, [CHI-TOWN].city, [CHI-TOWN].state, [CHI-TOWN].zip, [CHI-TOWN].school, [CHI-TOWN].grade
FROM [Delete] INNER JOIN [CHI-TOWN] ON (Delete.[First Name] = [CHI-TOWN].firstname) AND (Delete.[Last Name] = [CHI-TOWN].lastname)
WHERE ((([CHI-TOWN].lastname)=[Delete].[Last Name]) AND (([CHI-TOWN].firstname)=[Delete].[First Name]));

 

[Alexandre]

There is a contradiction between DELETE [CHI-TOWN].lastname etc which are fields from the CHI-TOWN Table
and FROM Delete which refers to the Delete table.
If you actually want to delete fields from records in the CHI-TOWN table (not the Delete table) for which matching records exist in the Delete table:

DELETE [CHI-TOWN].lastname,
             [CHI-TOWN].firstname,
             [CHI-TOWN].address,
             [CHI-TOWN].city, [CHI-TOWN].state,
             [CHI-TOWN].zip, [CHI-TOWN].school,
             [CHI-TOWN].grade 
FROM [CHI-TOWN]
INNER JOIN [CHI-TOWN]
   ON (Delete.[First Name] = [CHI-TOWN].firstname)
   AND (Delete.[Last Name] = [CHI-TOWN].lastname) 
WHERE ((([CHI-TOWN].lastname)=[Delete].[Last Name])
   AND (([CHI-TOWN].firstname)=[Delete].[First Name]));

 

[kholm]

Syntax

When I made the change, it said Syntax Error and pointed to the Delete just after Inner Join.

 

[Alexandre]

Delete is a reserved keyword in Access and you should not use such words to name your DB objects. For the moment, put it between brackets, but for the long term it is strongly advisable to change the name of your table. Among other thing to avoid when naming objects are spaces (like in Last Name).
Finally, I must say I had a very superficial look at your query the first time, and I now see another pb:
your where clause is useless and redundant with the Inner join which suffices to select records where matches exist on the lastname and firstname fields. So:

DELETE [CHI-TOWN].lastname,
             [CHI-TOWN].firstname,
             [CHI-TOWN].address,
             [CHI-TOWN].city, [CHI-TOWN].state,
             [CHI-TOWN].zip, [CHI-TOWN].school,
             [CHI-TOWN].grade 
FROM [CHI-TOWN]
INNER JOIN [Delete]
   ON ([Delete].[First Name] = [CHI-TOWN].firstname)
   AND ([Delete].[Last Name] = [CHI-TOWN].lastname);

should do it.

 

[Pat Hartman]

Since rows are deleted rather than columns, try this:

DELETE [CHI-TOWN].*
FROM [CHI-TOWN]
INNER JOIN [Delete]
   ON ([Delete].[First Name] = [CHI-TOWN].firstname)
   AND ([Delete].[Last Name] = [CHI-TOWN].lastname);

 

[kholm]

Stumped

I looked at the two resolutioins and tried to incorporate them and have come back with either syntax errors or just did not work. HELP
Kristin

 

[Pat Hartman]

How can we find your syntax errors unless you post your SQL?

PS, don't start another thread on the same topic. It is much better to stick to the same thread until your problem is resolved.

 

[kholm]

The SQL

I was afraid the post would get lost and would receive no reply. Now I know that it will not. But here is the SQL and I am still receiving the same response,

DELETE [CHI-TOWN].lastname, [CHI-TOWN].firstname, [CHI-TOWN].address, [CHI-TOWN].city, [CHI-TOWN].state, [CHI-TOWN].zip, [CHI-TOWN].school, [CHI-TOWN].grade
FROM [Take out] INNER JOIN [CHI-TOWN] ON ([Take out].[First Name] = [CHI-TOWN].firstname) AND ([Take out].[Last Name] = [CHI-TOWN].lastname)
WHERE ((([CHI-TOWN].lastname)=[Take out].[Last Name]) AND (([CHI-TOWN].firstname)=[Take Out].[First Name]));

 

[Pat Hartman]

DELETE [CHI-TOWN].*
FROM [CHI-TOWN] INNER JOIN [Take out] ON ([Take out].[First Name] = [CHI-TOWN].firstname) AND ([Take out].[Last Name] = [CHI-TOWN].lastname);

1. You don't need to specify a field list since indifidual fields are not what is being deleted, rows are being deleted. Just use the asterisk.
2. The first table mentioned in the From clause must be the one that you are deleting from.
3. The Where clause is superfluous since it uses the same columns that the join uses.