Solved

Can Not Delete from specified tables

Posted on 2007-04-02
4
536 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
  • 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now