 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / September 2005Delete Query
I've created a delete query using a left join on two tables that identifies the data rows I want to delete. However, I get an error message that tells me too "specify the table containing the records I want to delete"? How do I correct this error?  Signaturethanks, mc MC, Can you please post back with some more setails, preferably the SQL view of the query you're trying with so far? Thanks. SignatureSteve Schapel, Microsoft Access MVP > I've created a delete query using a left join on two tables that identifies > the data rows I want to delete. However, I get an error message that tells > me too "specify the table containing the records I want to delete"? How do I > correct this error? Below is the sql statement. Thanks. DELETE ALMOST.Co, ALMOST.ST, ALMOST.[Opr #], ALMOST.[Well No], ALMOST.Operator, ALMOST.[Well Name], ALMOST.[Prod Date], ALMOST.[Current Gas Imb], ALMOST.[Prior Mo Gas IMb], ALMOST.Status, ALMOST.[Stmt Mailed], ALMOST.[DOM requests], ALMOST.PIPELINE, ALMOST.Purchaser, ALMOST.SYSTEM, ALMOST.GWI, ALMOST.[Last Request], ALMOST.Comments, ALMOST.[P&A/Sell Form], ALMOST.[P&A/Sell Form to Acctg], ALMOST.GBA, ALMOST.[MAKE-UP], ALMOST.[TERMS FOR GBA], ALMOST.County, ALMOST.[LEGAL DESCRIPTION], ALMOST.ACQ, ALMOST.[SOLD DATE], ALMOST.[SOLD TO], ALMOST.[FILE LOCATION], ALMOST.Reserves, ALMOST.ID FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No]; Signaturethanks, mc > MC, > [quoted text clipped - 5 lines] > > me too "specify the table containing the records I want to delete"? How do I > > correct this error? MC, A Delete Query needs to have the table specified. The SQL always looks like this... DELETE TableName.* FROM ... It is not clear what you are trying to achieve with the Left Join. This would appear to indicate that you have records in the 2_073105 table which do not have corresponding records in the Almost table. Is that right? And in that case, the data returned by the query will include these records, right? So how do the non-existent Almost records relate to what you are trying to achieve, if you catch my drift here? Are you really trying to delete all records from the Almost table where there *are* matching records in the 2_073105 table? If so, the SQL would be like this... DELETE ALMOST.* FROM ALMOST INNER JOIN 2_073105 ON ALMOST.[Well No]=[2_073105].QEP Otherwise, if I have misunderstood, please try to explain what you want to happen. SignatureSteve Schapel, Microsoft Access MVP > Below is the sql statement. Thanks. > [quoted text clipped - 7 lines] > DATE], ALMOST.[SOLD TO], ALMOST.[FILE LOCATION], ALMOST.Reserves, ALMOST.ID > FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No]; Thank you - your reponse was right on -- however, I am getting an additional error that says "Could not delete from specified tables. (Error 3086) You tried to delete data from one or more tables, but the deletion could not be completed." I've checked the permissions? Signaturethanks, mc > MC, > [quoted text clipped - 29 lines] > > DATE], ALMOST.[SOLD TO], ALMOST.[FILE LOCATION], ALMOST.Reserves, ALMOST.ID > > FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No]; Is there something running prior to this that is locking the table? You could try closing the table via code first... Just a thought, Susan > Thank you - your reponse was right on -- however, I am getting an > additional [quoted text clipped - 42 lines] >> > ALMOST.ID >> > FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No]; Thanks for the feedback, I have learned quite abit with the information intereacted on this posting. I have since approached the problem from a different angle and have had success. I would like to know if a website exists that has examples of delete queries using multiple tables and joins? Thanks again. Signature> Is there something running prior to this that is locking the table? You > could try closing the table via code first... [quoted text clipped - 49 lines] > >> > ALMOST.ID > >> > FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No]; I've have these bookmarked for ages - nice reference: Fundamental Microsoft Jet SQL for Access 2000 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/ac intsql.asp Intermediate Microsoft Jet SQL for Access 2000 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/ac intsql.asp Advanced Microsoft Jet SQL for Access 2000 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/ac intsql.asp Also, http://www.techonthenet.com/ has lots of excellent stuff, SQL, VBA syntax etc. Signaturehth, SusanV > Thanks for the feedback, I have learned quite abit with the information > intereacted on this posting. I have since approached the problem from a [quoted text clipped - 63 lines] >> >> > ALMOST.ID >> >> > FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No]; MC, I would be interested to hear what is involved with your solution. There is a topic in Access Help about "when can I update data in a query". There are a number of factors that determine whether a query is "updateable" or not. One of the considerations for a Delete Query to work is that the data set returned by the query must be updateable. SignatureSteve Schapel, Microsoft Access MVP > Thanks for the feedback, I have learned quite abit with the information > intereacted on this posting. I have since approached the problem from a > different angle and have had success. I would like to know if a website > exists that has examples of delete queries using multiple tables and joins? > Thanks again. I reversed my approach and on the 1000 record table, instead of going after the 20 records I wanted to delete and reappend, I created a new table for the 980 records I didn't want to delete and then appended the new records to the new table - deleted all records in the main 1000 record table and finally appended the new table records back to the orig/main table. I recognize its not the most efficient approach but for now it did the job. I plan on learning from the web sites posted. Signaturethanks, mc > MC, > [quoted text clipped - 10 lines] > > exists that has examples of delete queries using multiple tables and joins? > > Thanks again. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.