Help with Delete Query

[platypus71]

I am having problems with a query. When I run my query in View mode, it reports exactly what I want to delete. When I run it in delete mode, it says it can't delete and recommends I don't have access to do that, but I do.

Here is my SQL of the query. Am I trying to do something Access isn't capable of or am I missing something?

DELETE [tblWaitlistRequests].*, [tblWaitlistRequests].[WaitKey]
FROM [tblWaitlistRequests], [qryWaitlistAttended]
WHERE ((([tblWaitlistRequests].[WaitKey])=[qryWaitlistAttended]![WaitKey]));

 

[jebry]

Hi!

I may be that it can't because those records are being used by your query. Maybe you can do it as a sub-query:

DELETE [tblWaitlistRequests FROM [tblWaitlistRequests]
WHERE [tblWaitlistRequests].[WaitKey] In (Put your SQL from your query here);

Note, the SQL can return WaitKey only.


hth

Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[platypus71]

Here is my original query that didn't work that I then built a query to show the results and compare to.
Sorry, I realize I posted the wrong code in the original.

DELETE [tblWaitlistRequests].*, [tblWaitlistRequests].Status, tblRoster.Status
FROM ([tblWaitlistRequests] INNER JOIN tblRoster ON [tblWaitlistRequests].[Emp#] = tblRoster.[Employee#]) INNER JOIN tblReconReport ON (tblReconReport.EventID = tblRoster.EventID) AND ([tblWaitlistRequests].CourseID = tblReconReport.CourseID)
WHERE ((([tblWaitlistRequests].Status)="Wait") AND ((tblRoster.Status)="attended"));

 

[jebry]

Hi!

Try this:

DELETE [tblWaitlistRequests].* FROM ([tblWaitlistRequests] INNER JOIN tblRoster ON [tblWaitlistRequests].[Emp#] = tblRoster.[Employee#]) INNER JOIN tblReconReport ON (tblReconReport.EventID = tblRoster.EventID) AND ([tblWaitlistRequests].CourseID = tblReconReport.CourseID)
WHERE ((([tblWaitlistRequests].Status)="Wait") AND ((tblRoster.Status)="attended"));

Since you only want to delete records from tblWaitlistRequests correct? Do you have any permanent relationships defined in the relationship window? If you can't get it work as a delete query you can alternatively do it code using a record set.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[platypus71]

Could not delete from specified tables."

Same error as before.

 

[jebry]

Hi!

You need to do this as a subquery:

DELETE [tblWaitlistRequests].* FROM ([tblWaitlistRequests]
Where tblWaitlistRequests.[Emp#] IN (Select [tblWaitlistRequests].[Emp#] FROM ([tblWaitlistRequests] INNER JOIN tblRoster ON [tblWaitlistRequests].[Emp#] = tblRoster.[Employee#]) INNER JOIN tblReconReport ON (tblReconReport.EventID = tblRoster.EventID) AND ([tblWaitlistRequests].CourseID = tblReconReport.CourseID)
WHERE [tblWaitlistRequests].Status)="Wait" AND tblRoster.Status)="attended");

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[platypus71]

This gives me a "Syntax Error in JOIN operation

 

[platypus71]

It doesn't seem to like the first WHERE, if it is really hiliting the problem.

 

[jebry]

Hi!

Missed one parentheses. Remove the opening ( before the table name.

If that doesn't work then we can remove the join conditions to the Where clause.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[platypus71]

I had already found that parenthesis and added the close parenthesis. With those parens, it gives me the JOIN error above.

Without, it gives me:
Syntax error (missing operator) in query expression '<insert code starting at> ([tblWaitlistRequests].[Emp#] IN (Select [tblWaitlistRequests].[Emp#] FROM......'

 

[jebry]

Hi!

Let's try again!

DELETE [tblWaitlistRequests].* FROM [tblWaitlistRequests]
Where tblWaitlistRequests.[Emp#] IN (Select [tblWaitlistRequests].[Emp#] FROM [tblWaitlistRequests], tblRoster, tblReconReport WHERE [tblWaitlistRequests].[Emp#] = tblRoster.[Employee#] AND tblReconReport.EventID = tblRoster.EventID AND [tblWaitlistRequests].CourseID = tblReconReport.CourseID [tblWaitlistRequests].Status = "Wait" AND tblRoster.Status = "attended");

let me know if that works. I got rid of all extra parentheses and the joins. I added the join conditions to the Where clause.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[platypus71]

This time, it says there is a syntax error and hilights everything in the parenthesis.

Not sure Access-SQL likes the imbedded SELECT statement.

 

[jebry]

Hi!

I missed the AND before [tblWaitlistRequests].Status = "Wait"

sorry!



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[platypus71]

Much closer.

Ok, now here is the problem I have.
According to the original query (which I verified the results), there should only currently be 4 records in my test DB to delete.

The new query we (you) created shows 9 records. When I check the 9 records, indeed 5 of them are incorrect.

What I have found:
5 records show up from the same Emp# but are different CourseIDs. Only one of these records should have shown up.

The same thing happened on a different Emp# but it was only two courseIDs in which only one was valid.

In researching these two people, what has happened is:
If [tblWaitlistRequests].Status = "Wait" on one CourseID, then it wants to delete ALL records for that Emp# in [tbleWaitlistRequests].

Any ideas?

 

[platypus71]

Nevermind, I figured it out.

I just changed the beginning to look at the UNIQUE field of [Wait Key]