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
8  25075 
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
"John Baker" <Ba******@Veriz on.net> wrote in message
news:ls******** *************** *********@4ax.c om... 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
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.
--
HTH
Van T. Dinh
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message
news:Zb******** ************@co mcast.com... 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
See the Microsoft Knowledge Base article: http://support.microsoft.com/?id=207761
--
HTH
Van T. Dinh
"John Baker" <Ba******@Veriz on.net> wrote in message
news:ls******** *************** *********@4ax.c om... 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
Thats exactly what happened!!!
"Van T. Dinh" <Va***********@ discussions.mic rosoft.com> wrote: 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.
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
"Van T. Dinh" <Va***********@ discussions.mic rosoft.com> wrote: See the Microsoft Knowledge Base article:
http://support.microsoft.com/?id=207761
Please post the SQL String of your Query.
--
Van T. Dinh
"John Baker" <Ba******@Veriz on.net> wrote in message
news:2b******** *************** *********@4ax.c om...
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
"Van T. Dinh" <Va***********@ discussions.mic rosoft.com> wrote:
See the Microsoft Knowledge Base article:
http://support.microsoft.com/?id=207761
John Baker wrote: 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] 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.
--
Pretentious? 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
Trevor Best <nospam@localho st> wrote: John Baker wrote:
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] 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: aiKeith |
last post by:
I have a really stupid problem I'm hoping to get help with.
The problem occurs if I delete rows from a dynamically built datatable.
ie:
CreateDataTable(); // creates the structure of the table
myTable.Rows.Delete();
now if I put that datatable back in a dataset and bind that ds to a grid, I
get the error "there is no row at position 216"
|
by: lottaviano |
last post by:
I am using Access 2002 and have two tables (Main and Actions) linked
(in Relationships) on one field "CAL_ID" (primary key in Main Table).
Main Table is a list of equipment. Actions Table lists actions
performed on these pieces of equipment (calibration, cleaning, etc.) so
there are many records per every one record in the Main Table.
I have a form based on Main Table, containing a subform for Actions
Table. This is used for data...
|
by: Michael Ramey |
last post by:
Hi,
I'm dynamically creating a table of "delete" imagebuttons, that correspond
to files on the webserver. I want to respond to clicks of these buttons, so
I know to know what file to delete. Here is the code I'm using
Dim imgShow As New ImageButton
imgShow.CommandName = "Delete"
imgShow.CommandArgument = arrayOfFiles(i)
AddHandler imgShow.Command, AddressOf Image_Command
|
by: papaja |
last post by:
Hi,
I posted this on asp.net forums already, but nobody answered it. Here
is direct link: http://forums.asp.net/1124640/ShowPost.aspx.
Here is the question:
I'm using this code in delete command of datagrid:
****************
Dataset1.DSataTable1.Rows(e.Item.ItemIndex).Delete()
adp.Update(DataSet1)
|
by: cyber0ne |
last post by:
I'm having a bit of trouble adding records to a local .DB file (using a
Paradox connection string). I checked the file permissions, and for
testing purposes set Everyone to Full access. But it seems that I can
add one and only one record to the table, any other attempts to insert
a record generate the error:
"Operation Must Use An Updatable Query"
I can access the table via other means, delete that one record, try
again, same...
| | |
by: Mike D |
last post by:
OS: Windows XP Professional
Microsoft Access 2003
I am trying to update a table in my DB from a tempory table. I need to ensure that if records in the main table match records in the temp table (on the keys) they are replaced/updated by the new data in the temp table.
the vba code i am running is below:
Dim mySQL As String
|
by: Daniel Pitts |
last post by:
I have std::vector<Base *bases;
I'd like to do something like:
std::for_each(bases.begin(), bases.end(), operator delete);
Is it possible without writing an adapter? Is there a better way? Is
there an existing adapter?
Thanks,
Daniel.
|
by: Cirene |
last post by:
Can you assist me with this database problem?
I have 4 tables in my db....
Table1 (key: Table1Id)
Table2 (key: Table2Id)
Table3 (key: Table3Id)
Table 4 has these fields:
key: Table4Id
Table1Id (ties it to Table 1)
|
by: jbt007 |
last post by:
All,
Access 2003 - WinXP
I thought this would be a no brainer, but it seems to be a perplexing problem. I have a simple table I use for importing several text reports, use VBA to run through the report to move data into tables. The temp table stays in access (tblTxtRpt), until the next report is imported, I then use SQL to DROP TABLE and CREATE TABLE to recreate it. This is because the table has an autonumber field that numbers the text...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
