DELETE QUERY PROBLEM

[sdg8481]

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

 

[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

 

[sdg8481]

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

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

 

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

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

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

 

[sdg8481]

works perfectly thankyou

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)

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

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