Can Not Delete from specified tables

Access 2003 saving as Access 200 database

Select TempTran.*
FROM TempTran02_canc LEFT JOIN TempTran ON (TempTran02_canc.Ptid = TempTran.Ptid) AND (TempTran02_canc.Datedata = TempTran.Datedata) AND (TempTran02_canc.Timeeff = TempTran.Timeeff);
197 cases

Change to delete and it says can not delete from specified tables.

FROM TempTran
Where (tempTran.Ptid in (select Ptid from TempTran02_canc)) and (TempTran.Datedata in (select Datedata from TempTran02_canc)) AND (TempTran.Timeeff in (select Timeeff from TempTran 02_canc));
238 responses

Reverse the order and it asks for TempTran02_canc.Ptid
I am almost there, but very tired. Looking for overnight help
Who is Participating?
TheSloathConnect With a Mentor Commented:
Make sure there are indexes on the fields you join by in the first Query.
avgplusguyAuthor Commented:
TempTran is the the temporay table which I have now indexed.
TempTran02_canc is a query that selects the people that have canceled.
None of the above is unique or could be used as a key field.
AccessNO is the only key field in the table.
I am tempted to output AccessNO to a table and then delete from that table, but I am trying to do this in only a couple of steps.
avgplusguyAuthor Commented:
query one
SELECT TempTran.AccessNO  INTO Canc_q
FROM TempTran
WHERE (((TempTran.Datedata) In (select Datedata from TempTran02_canc)) AND ((TempTran.Timeeff) In (select Timeeff from TempTran02_canc)) AND ((TempTran.Ptid) In (select Ptid from TempTran02_canc)));

query two
FROM TempTran where TempTran.AccessNO in  (select AccessNO from Canc_q);
avgplusguyAuthor Commented:
The Sloath
Thank you for making think about indexes. It was not the solution, but it got me thinking so you get the points...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.