Delete Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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]
 
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) & ";"
 
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];
 
Ofer, your first SQL statement will not work because it creates a Cartesian
Product which is not updatable.

Ofer said:
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]

--
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


Jologs said:
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.
Click to expand...
Click to expand...
 
Delimiters are only required when working with literal values.

Jeff Boyce said:
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) & ";"

--
Regards

Jeff Boyce
<Office/Access MVP>

Jologs said:
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.
Click to expand...
Click to expand...
 
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.
--
Allan - http://allanmagtibay.hostrocket.com


Pat Hartman(MVP) said:
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];


Jologs said:
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.
Click to expand...
Click to expand...
 
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.


Jologs said:
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.
--
Allan - http://allanmagtibay.hostrocket.com


Pat Hartman(MVP) said:
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];


Jologs said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top