Delete Queries from an Unmatched query

L

Lisa

I have a table called Calls table and a table called calls table completed.
I have a unmatched query to find the records in Call Table that do not have a
record in the calls completed table. Those that do not have a record in the
calls complete table i want to be able to delete as the calls get old. Is
there a way to do a delete query off an unmatched query?

Thanks for your help in advance.

Lisa
 
L

Lisa

and I did try this here is my sql

DELETE [Calls Table].*, [Calls Table].ID, [Calls Table].CONTACT_DT, [Calls
Table Completed].ID
FROM [Calls Table] LEFT JOIN [Calls Table Completed] ON [Calls Table].ID =
[Calls Table Completed].ID
WHERE ((([Calls Table].CONTACT_DT)=#12/1/2007#) AND (([Calls Table
Completed].ID) Is Null));

and it is giving me an error "Could Not Delete from Specified Tables"
 
J

John W. Vinson

I have a table called Calls table and a table called calls table completed.
I have a unmatched query to find the records in Call Table that do not have a
record in the calls completed table. Those that do not have a record in the
calls complete table i want to be able to delete as the calls get old. Is
there a way to do a delete query off an unmatched query?
Click to expand...

The DELETE clause of the query should reference only the table from which you
wish to delete: try

DELETE [Calls Table].*
FROM [Calls Table] LEFT JOIN [Calls Table Completed] ON [Calls Table].ID =
[Calls Table Completed].ID
WHERE ((([Calls Table].CONTACT_DT)=#12/1/2007#) AND (([Calls Table
Completed].ID) Is Null));

This will only work if the joining field - ID - is the Primary Key of Calls
Table, or otherwise has a unique index. If there might be multiple records
with the same ID you'll need a subquery instead:

DELETE [Calls Table].*
FROM [Calls Table]
WHERE ((([Calls Table].CONTACT_DT)=#12/1/2007#)
AND NOT EXISTS(SELECT [Calls Table Completed].ID FROM [Calls Table Completed]
WHERE [Calls Table Completed].ID = [Calls Table].ID);

John W. Vinson [MVP]
 
L

Lisa

John,

thank you for your response. The ID is the primary key on the calls table
and when i replaced the sql with the one below it is still giving me the same
error.

Any other advice?

Thanks.

Lisa

John W. Vinson said:
I have a table called Calls table and a table called calls table completed.
I have a unmatched query to find the records in Call Table that do not have a
record in the calls completed table. Those that do not have a record in the
calls complete table i want to be able to delete as the calls get old. Is
there a way to do a delete query off an unmatched query?
Click to expand...

The DELETE clause of the query should reference only the table from which you
wish to delete: try

DELETE [Calls Table].*
FROM [Calls Table] LEFT JOIN [Calls Table Completed] ON [Calls Table].ID =
[Calls Table Completed].ID
WHERE ((([Calls Table].CONTACT_DT)=#12/1/2007#) AND (([Calls Table
Completed].ID) Is Null));

This will only work if the joining field - ID - is the Primary Key of Calls
Table, or otherwise has a unique index. If there might be multiple records
with the same ID you'll need a subquery instead:

DELETE [Calls Table].*
FROM [Calls Table]
WHERE ((([Calls Table].CONTACT_DT)=#12/1/2007#)
AND NOT EXISTS(SELECT [Calls Table Completed].ID FROM [Calls Table Completed]
WHERE [Calls Table Completed].ID = [Calls Table].ID);

John W. Vinson [MVP]
Click to expand...
 
J

John Spencer

Try the following AFTER you make a backup of your data

DELETE [Calls Table].*
FROM [Calls Table]
WHERE [Calls Table] IN
(SELECT [Calls Table].ID
FROM [Calls Table] LEFT JOIN [Calls Table Completed]
ON [Calls Table].ID = [Calls Table Completed].ID
WHERE [Calls Table].CONTACT_DT=#12/1/2007#
AND [Calls Table Completed].ID Is Null)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

thank you for your response. The ID is the primary key on the calls table
and when i replaced the sql with the one below it is still giving me the same
error.

Any other advice?

Thanks.

Lisa

John W. Vinson said:
I have a table called Calls table and a table called calls table completed.
I have a unmatched query to find the records in Call Table that do not have a
record in the calls completed table. Those that do not have a record in the
calls complete table i want to be able to delete as the calls get old. Is
there a way to do a delete query off an unmatched query?
Click to expand...
The DELETE clause of the query should reference only the table from which you
wish to delete: try

DELETE [Calls Table].*
FROM [Calls Table] LEFT JOIN [Calls Table Completed] ON [Calls Table].ID =
[Calls Table Completed].ID
WHERE ((([Calls Table].CONTACT_DT)=#12/1/2007#) AND (([Calls Table
Completed].ID) Is Null));

This will only work if the joining field - ID - is the Primary Key of Calls
Table, or otherwise has a unique index. If there might be multiple records
with the same ID you'll need a subquery instead:

DELETE [Calls Table].*
FROM [Calls Table]
WHERE ((([Calls Table].CONTACT_DT)=#12/1/2007#)
AND NOT EXISTS(SELECT [Calls Table Completed].ID FROM [Calls Table Completed]
WHERE [Calls Table Completed].ID = [Calls Table].ID);

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
L

Lisa

John,

When i tried that sql statement, I get a dialog box asking for input for
Calls Table.. I click OK.. and then it runs for about 15 minutes and then it
says it is deleting 0 records do I want to continue. So I don't think is
working.

Thank you for any other information you can provide.

Lisa

John Spencer said:
Try the following AFTER you make a backup of your data

DELETE [Calls Table].*
FROM [Calls Table]
WHERE [Calls Table] IN
(SELECT [Calls Table].ID
FROM [Calls Table] LEFT JOIN [Calls Table Completed]
ON [Calls Table].ID = [Calls Table Completed].ID
WHERE [Calls Table].CONTACT_DT=#12/1/2007#
AND [Calls Table Completed].ID Is Null)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

thank you for your response. The ID is the primary key on the calls table
and when i replaced the sql with the one below it is still giving me the same
error.

Any other advice?

Thanks.

Lisa

John W. Vinson said:
I have a table called Calls table and a table called calls table completed.
I have a unmatched query to find the records in Call Table that do not have a
record in the calls completed table. Those that do not have a record in the
calls complete table i want to be able to delete as the calls get old. Is
there a way to do a delete query off an unmatched query?
The DELETE clause of the query should reference only the table from which you
wish to delete: try

DELETE [Calls Table].*
FROM [Calls Table] LEFT JOIN [Calls Table Completed] ON [Calls Table].ID =
[Calls Table Completed].ID
WHERE ((([Calls Table].CONTACT_DT)=#12/1/2007#) AND (([Calls Table
Completed].ID) Is Null));

This will only work if the joining field - ID - is the Primary Key of Calls
Table, or otherwise has a unique index. If there might be multiple records
with the same ID you'll need a subquery instead:

DELETE [Calls Table].*
FROM [Calls Table]
WHERE ((([Calls Table].CONTACT_DT)=#12/1/2007#)
AND NOT EXISTS(SELECT [Calls Table Completed].ID FROM [Calls Table Completed]
WHERE [Calls Table Completed].ID = [Calls Table].ID);

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Missed one bit. WHERE [Calls Table].ID IN (...

Note the addition of .ID after [Calls Table]

DELETE [Calls Table].*
FROM [Calls Table]
WHERE [Calls Table].ID IN
(SELECT [Calls Table].ID
FROM [Calls Table] LEFT JOIN [Calls Table Completed]
ON [Calls Table].ID = [Calls Table Completed].ID
WHERE [Calls Table].CONTACT_DT=#12/1/2007#
AND [Calls Table Completed].ID Is Null)

Oh, by the way. BACK UP YOUR DATA before doing this. Just in case it
doesn't work the way you want it to.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

When i tried that sql statement, I get a dialog box asking for input for
Calls Table.. I click OK.. and then it runs for about 15 minutes and then it
says it is deleting 0 records do I want to continue. So I don't think is
working.

Thank you for any other information you can provide.

Lisa
Click to expand...
S N I P
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to create a report that will list unmatched records by Ticket Primary Key 0
Append Crosstab Query to Existing Table 0
Delete Query not Working 5
Making a Delete Qiuery from an unmatched Query 6
Help! Unmatched Data Query 1
composite key unmatched query 1
Joins in unmatched query 2
Unmatched query question 3

Top