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

[John Baker]

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

[Darryl Kerkeslager]

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

[Van T. Dinh]

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

[Van T. Dinh]

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

[John Baker]

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.

[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
"Van T. Dinh" <Va***********@discussions.microsoft.com> wrote:

See the Microsoft Knowledge Base article:

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

[Van T. Dinh]

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

[Trevor Best]

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?

[John Baker]

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.