DELETE QUERY PROBLEM

G

Guest

Hi,

I know this has been covered many time buti just cant seem to get it working
for my query.

Basically i have a delete query that contains more than two table by an
INNER JOIN, hence one table just identifies those records to be deleted.

My SQL is as follows;

DELETE [T_Utilisation (Working)].[Unique ID2], [T_Utilisation
(Working)].WEEKDAY, [L - Weekday T].Include
FROM [T_Utilisation (Working)] INNER JOIN [L - Weekday T] ON [T_Utilisation
(Working)].WEEKDAY = [L - Weekday T].WEEKDAY
WHERE ((([L - Weekday T].Include)=0));

but when i try to run this i get an error stating "specify the table
containing the records you want to delete".

Basically, what i'm after is for where the 'Include' value in the 'L -
Weekday T' has a value of 0, for those records with a matching weekday in
the 'T_Utilisation (workings)' table to be deleted.

Hope this makes sense.

Thanks in advance
 
D

Douglas J. Steele

You could try

DELETE * FROM [T_Utilisation (Working)]
WHERE [WEEKDAY] IN (SELECT [WEEKDAY]
FROM [L - Weekday T] WHERE [Include])=0)

That having been said, you've got fields named Weekday and Include, both of
which are reserved words, and can lead to problems. If you cannot (or will
not) rename those fields, you'll need square brackets around them, as in the
example above.

For a good discussion of what names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
 
G

Guest

Hi,

Thanks for this, and i fully apprieciate the issue with my fields names that
bad planning on my part. However, when i try the SQL below i get the
following error message'

In operator without () in query expression '[WEEKDAY] IN (SELECT [WEEKDAY]
FROM [L - Weekday T Where [Include])=0'.

Am i missing something

Douglas J. Steele said:
You could try

DELETE * FROM [T_Utilisation (Working)]
WHERE [WEEKDAY] IN (SELECT [WEEKDAY]
FROM [L - Weekday T] WHERE [Include])=0)

That having been said, you've got fields named Weekday and Include, both of
which are reserved words, and can lead to problems. If you cannot (or will
not) rename those fields, you'll need square brackets around them, as in the
example above.

For a good discussion of what names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


sdg8481 said:
Hi,

I know this has been covered many time buti just cant seem to get it
working
for my query.

Basically i have a delete query that contains more than two table by an
INNER JOIN, hence one table just identifies those records to be deleted.

My SQL is as follows;

DELETE [T_Utilisation (Working)].[Unique ID2], [T_Utilisation
(Working)].WEEKDAY, [L - Weekday T].Include
FROM [T_Utilisation (Working)] INNER JOIN [L - Weekday T] ON
[T_Utilisation
(Working)].WEEKDAY = [L - Weekday T].WEEKDAY
WHERE ((([L - Weekday T].Include)=0));

but when i try to run this i get an error stating "specify the table
containing the records you want to delete".

Basically, what i'm after is for where the 'Include' value in the 'L -
Weekday T' has a value of 0, for those records with a matching weekday in
the 'T_Utilisation (workings)' table to be deleted.

Hope this makes sense.

Thanks in advance
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry, my typo. I forgot to remove the closing parenthesis after [Include]
(the perils of copy-and-paste!).

It should be:

DELETE * FROM [T_Utilisation (Working)]
WHERE [WEEKDAY] IN (SELECT [WEEKDAY]
FROM [L - Weekday T] WHERE [Include]=0)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


sdg8481 said:
Hi,

Thanks for this, and i fully apprieciate the issue with my fields names
that
bad planning on my part. However, when i try the SQL below i get the
following error message'

In operator without () in query expression '[WEEKDAY] IN (SELECT [WEEKDAY]
FROM [L - Weekday T Where [Include])=0'.

Am i missing something

Douglas J. Steele said:
You could try

DELETE * FROM [T_Utilisation (Working)]
WHERE [WEEKDAY] IN (SELECT [WEEKDAY]
FROM [L - Weekday T] WHERE [Include])=0)

That having been said, you've got fields named Weekday and Include, both
of
which are reserved words, and can lead to problems. If you cannot (or
will
not) rename those fields, you'll need square brackets around them, as in
the
example above.

For a good discussion of what names to avoid, see what Allen Browne has
at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


sdg8481 said:
Hi,

I know this has been covered many time buti just cant seem to get it
working
for my query.

Basically i have a delete query that contains more than two table by an
INNER JOIN, hence one table just identifies those records to be
deleted.

My SQL is as follows;

DELETE [T_Utilisation (Working)].[Unique ID2], [T_Utilisation
(Working)].WEEKDAY, [L - Weekday T].Include
FROM [T_Utilisation (Working)] INNER JOIN [L - Weekday T] ON
[T_Utilisation
(Working)].WEEKDAY = [L - Weekday T].WEEKDAY
WHERE ((([L - Weekday T].Include)=0));

but when i try to run this i get an error stating "specify the table
containing the records you want to delete".

Basically, what i'm after is for where the 'Include' value in the 'L -
Weekday T' has a value of 0, for those records with a matching weekday
in
the 'T_Utilisation (workings)' table to be deleted.

Hope this makes sense.

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

works perfectly thankyou

Douglas J. Steele said:
Sorry, my typo. I forgot to remove the closing parenthesis after [Include]
(the perils of copy-and-paste!).

It should be:

DELETE * FROM [T_Utilisation (Working)]
WHERE [WEEKDAY] IN (SELECT [WEEKDAY]
FROM [L - Weekday T] WHERE [Include]=0)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


sdg8481 said:
Hi,

Thanks for this, and i fully apprieciate the issue with my fields names
that
bad planning on my part. However, when i try the SQL below i get the
following error message'

In operator without () in query expression '[WEEKDAY] IN (SELECT [WEEKDAY]
FROM [L - Weekday T Where [Include])=0'.

Am i missing something

Douglas J. Steele said:
You could try

DELETE * FROM [T_Utilisation (Working)]
WHERE [WEEKDAY] IN (SELECT [WEEKDAY]
FROM [L - Weekday T] WHERE [Include])=0)

That having been said, you've got fields named Weekday and Include, both
of
which are reserved words, and can lead to problems. If you cannot (or
will
not) rename those fields, you'll need square brackets around them, as in
the
example above.

For a good discussion of what names to avoid, see what Allen Browne has
at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,

I know this has been covered many time buti just cant seem to get it
working
for my query.

Basically i have a delete query that contains more than two table by an
INNER JOIN, hence one table just identifies those records to be
deleted.

My SQL is as follows;

DELETE [T_Utilisation (Working)].[Unique ID2], [T_Utilisation
(Working)].WEEKDAY, [L - Weekday T].Include
FROM [T_Utilisation (Working)] INNER JOIN [L - Weekday T] ON
[T_Utilisation
(Working)].WEEKDAY = [L - Weekday T].WEEKDAY
WHERE ((([L - Weekday T].Include)=0));

but when i try to run this i get an error stating "specify the table
containing the records you want to delete".

Basically, what i'm after is for where the 'Include' value in the 'L -
Weekday T' has a value of 0, for those records with a matching weekday
in
the 'T_Utilisation (workings)' table to be deleted.

Hope this makes sense.

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Big brain problem! 2
insert a crosstab in to temp table query 2
delete query wth a condition 1
Delete Query Not Working 2
Help modifying a "working days" query/function 4
Delete query with joined tables 0
How to use a function in a query 1
Delete Query Help 2

Top