 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / February 2005"Specify Table containing the Records you want to Delete" Problem
Hi: Access 2000 W98! I have a table with numerous records in it, and am attempting to delete certain records that have been selected from it. These are selected based on the ID number in a different table. While I am using the tools in Access for query setup, its easier to show it on here using the SQL for the query, which is as follows( the table is [Investment Action Units]): DELETE [Investment Action Units].date, [Investment Action Units].[Action name], [Investment Action Units].Units, [Investment Action Units].Value, [Investment Action Units].Remarks FROM [Investment Action Units] INNER JOIN Parms ON [Investment Action Units].[Investment Name ID] = Parms.[Invest ID]; When I try and run the query, I get a message "Specify Table containing the Records you want to Delete". Since I already have specified the table I don't know what else I should do. I certainly want to select ONLY those records where there is a match in Parms, and not erase the whole table. Advice is appreciated. Regards John Baker DELETE * FROM [Investment Action Units] WHERE [Investment Action Units].[Investment Name ID] = Parms.[Invest ID] Never tried to delete using a join; I assume that that is where the problem is. Darryl Kerekslager > Hi: > [quoted text clipped - 21 lines] > > John Baker With the posted SQL, it is likely that JET will treat [Parms].[InvestID] as a Parameter and ask the user for the Parameter value since the Table [Parms] is not in the FROM clause.  SignatureHTH Van T. Dinh > DELETE * FROM [Investment Action Units] WHERE [Investment Action > Units].[Investment Name ID] = Parms.[Invest ID] [quoted text clipped - 3 lines] > > Darryl Kerekslager Thats exactly what happened!!! >With the posted SQL, it is likely that JET will treat [Parms].[InvestID] as >a Parameter and ask the user for the Parameter value since the Table [Parms] >is not in the FROM clause. See the Microsoft Knowledge Base article: http://support.microsoft.com/?id=207761 SignatureHTH Van T. Dinh > Hi: > [quoted text clipped - 21 lines] > > John Baker Unfortunatley none of these solves the problem I am having. It still does not know what table I was to delete from. Is there any good article about delete queries out there? Best John >See the Microsoft Knowledge Base article: > >http://support.microsoft.com/?id=207761 Please post the SQL String of your Query. SignatureVan T. Dinh > Unfortunatley none of these solves the problem I am having. It still does not know what > table I was to delete from. [quoted text clipped - 8 lines] > > > >http://support.microsoft.com/?id=207761 > Hi: > [quoted text clipped - 21 lines] > > John Baker DELETE * FROM [Investment Action Units] INNER JOIN Parms ON [Investment Action Units].[Investment Name ID] = Parms.[Invest ID]; Delete syntax is funny in Access in that you specify columns whereas the delete command works on a row but in general you use "delete * from" to delete the record, I think you confused it with all the column names in the there. If your intention was to delete the contents of those columns and not delete the rows themselves (i.e. set them to null) then you should use an update query. SignaturePretentious? Moi? Ahhhh Thanks very much. Yes, I have found the delete to be very odd, and not totally consistent with the other query commands. Thanks John >> Hi: >> [quoted text clipped - 34 lines] >delete the rows themselves (i.e. set them to null) then you should use >an update query. |
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.