Delete Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jologs - 07 Nov 2005 11:25 GMT

Hi:

Am trying to execute a delete query but it prompts to enter a parameter, the
where clause. SQL is as follows:

DELETE tblTBBeg.AccountTitle, *
FROM tblTBBeg
WHERE (((tblTBBeg.AccountTitle)=[tblTBBeg Without Matching
tblCOA].[AccountTitle]));

"tblTBBeg Without Matching tblCOA" is a result of Unmatched Query Wizard.
what seems to be wrong. I checked the where clause "AccountTitle" and it's
correct. Many thanks.

Signature

Allan - http://allanmagtibay.hostrocket.com

Reply to this Message

Ofer - 07 Nov 2005 12:06 GMT

If the [tblTBBeg Without Matching tblCOA] is a name of a query, you need to
define it the "From" in the query

DELETE tblTBBeg.*
FROM tblTBBeg , [tblTBBeg Without Matching tblCOA]
WHERE tblTBBeg.AccountTitle=[tblTBBeg Without Matching tblCOA].[AccountTitle]

or
DELETE tblTBBeg.*
FROM tblTBBeg INNER JOIN [tblTBBeg Without Matching tblCOA] On
tblTBBeg.AccountTitle=[tblTBBeg Without Matching tblCOA].[AccountTitle]

Signature

The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

> Hi:
>
[quoted text clipped - 9 lines]
> what seems to be wrong. I checked the where clause "AccountTitle" and it's
> correct. Many thanks.

Reply to this Message

Pat Hartman(MVP) - 07 Nov 2005 15:30 GMT

Ofer, your first SQL statement will not work because it creates a Cartesian
Product which is not updatable.

> If the [tblTBBeg Without Matching tblCOA] is a name of a query, you need
> to
[quoted text clipped - 25 lines]
>> it's
>> correct. Many thanks.

Reply to this Message

Jeff Boyce - 07 Nov 2005 13:49 GMT

Your SQL statement seems to imply that your WHERE clause is looking for a
string (i.e., "Title"). But you aren't using string delimiters in your
WHERE clause...

Try something like:

WHERE (((tblTBBeg.AccountTitle)=" & [tblTBBeg Without Matching
tblCOA].[AccountTitle])) & Chr(34) & ";"

Signature

Regards

Jeff Boyce
<Office/Access MVP>

> Hi:
>
[quoted text clipped - 9 lines]
> what seems to be wrong. I checked the where clause "AccountTitle" and it's
> correct. Many thanks.

Reply to this Message

Pat Hartman(MVP) - 07 Nov 2005 15:31 GMT

Delimiters are only required when working with literal values.

> Your SQL statement seems to imply that your WHERE clause is looking for a
> string (i.e., "Title"). But you aren't using string delimiters in your
[quoted text clipped - 20 lines]
>> it's
>> correct. Many thanks.

Reply to this Message

Pat Hartman(MVP) - 07 Nov 2005 15:28 GMT

You haven't defined the second table in the From clause. Also, instead of
using a Where clause, you need a proper join.

DELETE tblTBBeg.AccountTitle, *
FROM tblTBBeg Inner Join [tblTBBeg Without Matching
tblCOA] ON tblTBBeg.AccountTitle = [tblTBBeg Without Matching
tblCOA].[AccountTitle];

> Hi:
>
[quoted text clipped - 10 lines]
> what seems to be wrong. I checked the where clause "AccountTitle" and it's
> correct. Many thanks.

Reply to this Message

Jologs - 08 Nov 2005 01:40 GMT

Hi Pat:

Your SQL seems to be in the right tract, however it still prompts to error
"Specify the table containing the records you want to delete." I tried
modifying the first line to "> DELETE tblTBBeg.*" but still can't carried out
"Could not delete from the specified tables". I checked the suggested
information of Help and nothing contradicts the information (restricted or
read only). Many thanks.

Signature

Allan - http://allanmagtibay.hostrocket.com

> You haven't defined the second table in the From clause. Also, instead of
> using a Where clause, you need a proper join.
[quoted text clipped - 18 lines]
> > what seems to be wrong. I checked the where clause "AccountTitle" and it's
> > correct. Many thanks.

Reply to this Message

Pat Hartman(MVP) - 09 Nov 2005 04:30 GMT

This is better:
DELETE tblTBBeg.*
FROM tblTBBeg Inner Join [tblTBBeg Without Matching
tblCOA] ON tblTBBeg.AccountTitle = [tblTBBeg Without Matching
tblCOA].[AccountTitle];

If the delete still won't work, the problem is probably that one or both the
tables do not have a primary key defined. Without primary keys, Access
cannot determine the cardinality of the relationship. If the relationship
is 1-m, you need to decide if you want to select the cascade delete option.
If you do, you'll need to define a proper relationship and select the
enforce RI option before you will be able to choose Cascade delete.

> Hi Pat:
>
[quoted text clipped - 32 lines]
>> > it's
>> > correct. Many thanks.

Reply to this Message