avgplusguy
asked on
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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);
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);
ASKER
The Sloath
Thank you for making think about indexes. It was not the solution, but it got me thinking so you get the points...
Thank you for making think about indexes. It was not the solution, but it got me thinking so you get the points...
ASKER
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.