Can't delete returned records.

[Bill]

SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog] ON [q#Asgnmnt].CallID =
[q#CallLog].CallID
WHERE ((([q#CallLog].CallID) Is Null));


This above query returns what I call 'floating assignments' from our
helpdesk data. They are effectively assignments that do not have a
corresponding call log and I want to delete them but the returned data is
read only and I can't.

Can anyone help please?

Ta.
Bill

 

[John Spencer]

Is [q#Asgnmnt] a table or a query?
If it is a table, you could try the following

DELETE DistinctRow [q#Asgnmnt].CallID
FROM [q#Asgnmnt]
WHERE [q#Asgnmnt].CallID IN
(SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog]
ON [q#Asgnmnt].CallID = [q#CallLog].CallID
WHERE [q#CallLog].CallID Is Null)

 

[Bill]

[q#Asgnmnt] is a query.

Is [q#Asgnmnt] a table or a query?
If it is a table, you could try the following

DELETE DistinctRow [q#Asgnmnt].CallID
FROM [q#Asgnmnt]
WHERE [q#Asgnmnt].CallID IN
(SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog]
ON [q#Asgnmnt].CallID = [q#CallLog].CallID
WHERE [q#CallLog].CallID Is Null)

SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog] ON [q#Asgnmnt].CallID =
[q#CallLog].CallID
WHERE ((([q#CallLog].CallID) Is Null));


This above query returns what I call 'floating assignments' from our
helpdesk data. They are effectively assignments that do not have a
corresponding call log and I want to delete them but the returned data is
read only and I can't.

Can anyone help please?

Ta.
Bill

 

[John Spencer]

You might still be able to use the query I proposed, but if [q#Asgnmnt] is
not updatable, then you won't be able to delete from it.

IS [q#Asgnmnt] based on one table or on multiple tables? Try posting the
SQL for [q#Asgnmnt]. If [q#Asgnmnt] is multiple tables, then WHICH table to
you want to delete records from? YOUR SQL statement might look like:

DELETE DistinctRow OneTable.CallID
FROM OneTable
WHERE OneTable.CallID IN
(SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog]
ON [q#Asgnmnt].CallID = [q#CallLog].CallID
WHERE [q#CallLog].CallID Is Null)

[q#Asgnmnt] is a query.

Is [q#Asgnmnt] a table or a query?
If it is a table, you could try the following

DELETE DistinctRow [q#Asgnmnt].CallID
FROM [q#Asgnmnt]
WHERE [q#Asgnmnt].CallID IN
(SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog]
ON [q#Asgnmnt].CallID = [q#CallLog].CallID
WHERE [q#CallLog].CallID Is Null)

SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog] ON [q#Asgnmnt].CallID =
[q#CallLog].CallID
WHERE ((([q#CallLog].CallID) Is Null));


This above query returns what I call 'floating assignments' from our
helpdesk data. They are effectively assignments that do not have a
corresponding call log and I want to delete them but the returned data is
read only and I can't.

Can anyone help please?

Ta.
Bill

 

[Bill]

John,

First - thanks for persevering with me - I will try some of the things that
you suggest.

FYI: Both the queries (prefixed q#) are each based on a single table. It
just has a few calculated fields (using the a function called ccdate) in it
for the dates because the dates are 'text' entries in the original table.

SQL as follows.

q#Asgnmnt:
SELECT ccDate([DateAssign]) AS [#DateAssign], ccDate([TimeAssign]) AS
[#TimeAssign], ccDate([DateAcknow]) AS [#DateAcknow], ccDate([TimeAcknow])
AS [#TimeAcknow], ccDate([DateResolv]) AS [#DateResolv],
ccDate([TimeResolv]) AS [#TimeResolv], dbo_Asgnmnt.*
FROM dbo_Asgnmnt
ORDER BY ccDate([DateAssign]);

q#CallLog
SELECT ccDate([RecvdDate]) AS [#RecvdDate], ccDate([RecvdTime]) AS
[#RecvdTime], ccDate([ClosedDate]) AS [#ClosedDate], ccDate([ClosedTime]) AS
[#ClosedTime], dbo_CallLog.*
FROM dbo_CallLog;


Regards.
Bill

You might still be able to use the query I proposed, but if [q#Asgnmnt]
is not updatable, then you won't be able to delete from it.

IS [q#Asgnmnt] based on one table or on multiple tables? Try posting the
SQL for [q#Asgnmnt]. If [q#Asgnmnt] is multiple tables, then WHICH table
to you want to delete records from? YOUR SQL statement might look like:

DELETE DistinctRow OneTable.CallID
FROM OneTable
WHERE OneTable.CallID IN
(SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog]
ON [q#Asgnmnt].CallID = [q#CallLog].CallID
WHERE [q#CallLog].CallID Is Null)

[q#Asgnmnt] is a query.

Is [q#Asgnmnt] a table or a query?
If it is a table, you could try the following

DELETE DistinctRow [q#Asgnmnt].CallID
FROM [q#Asgnmnt]
WHERE [q#Asgnmnt].CallID IN
(SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog]
ON [q#Asgnmnt].CallID = [q#CallLog].CallID
WHERE [q#CallLog].CallID Is Null)




SELECT [q#Asgnmnt].CallID
FROM [q#Asgnmnt] LEFT JOIN [q#CallLog] ON [q#Asgnmnt].CallID =
[q#CallLog].CallID
WHERE ((([q#CallLog].CallID) Is Null));


This above query returns what I call 'floating assignments' from our
helpdesk data. They are effectively assignments that do not have a
corresponding call log and I want to delete them but the returned data is
read only and I can't.

Can anyone help please?

Ta.
Bill