Solved

Can Not Delete from specified tables

Posted on 2007-04-02
4
540 Views
Last Modified: 2012-06-21
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.

DELETE *
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
0
Comment
Question by:avgplusguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 9

Accepted Solution

by:
TheSloath earned 500 total points
ID: 18840444
Make sure there are indexes on the fields you join by in the first Query.
0
 

Author Comment

by:avgplusguy
ID: 18843755
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.
0
 

Author Comment

by:avgplusguy
ID: 18844117
SOLUTION
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
DELETE *
FROM TempTran where TempTran.AccessNO in  (select AccessNO from Canc_q);
0
 

Author Comment

by:avgplusguy
ID: 18844138
The Sloath
Thank you for making think about indexes. It was not the solution, but it got me thinking so you get the points...
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question