Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can Not Delete from specified tables

Posted on 2007-04-02
4
Medium Priority
?
542 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 1500 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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

715 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