By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

"Specify Table containing the Records you want to Delete" Problem

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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******@Verizon.net> wrote in message
news:ls********************************@4ax.com...
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

Nov 13 '05 #2

P: n/a
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*********@comcast.net> wrote in message
news:Zb********************@comcast.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

Nov 13 '05 #3

P: n/a
See the Microsoft Knowledge Base article:

http://support.microsoft.com/?id=207761

--
HTH
Van T. Dinh


"John Baker" <Ba******@Verizon.net> wrote in message
news:ls********************************@4ax.com...
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

Nov 13 '05 #4

P: n/a
Thats exactly what happened!!!
"Van T. Dinh" <Va***********@discussions.microsoft.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.


Nov 13 '05 #5

P: n/a

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.microsoft.com> wrote:
See the Microsoft Knowledge Base article:

http://support.microsoft.com/?id=207761


Nov 13 '05 #6

P: n/a
Please post the SQL String of your Query.

--
Van T. Dinh


"John Baker" <Ba******@Verizon.net> wrote in message
news:2b********************************@4ax.com...

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.microsoft.com> wrote:
See the Microsoft Knowledge Base article:

http://support.microsoft.com/?id=207761

Nov 13 '05 #7

P: n/a
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?
Nov 13 '05 #8

P: n/a
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@localhost> 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.


Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.