Delete query based on criteria in a linked table

[fishy]

I am trying to delete all records from tbl_XXdata where the tbl_MSEmployees
shows a leaving date against it. I have the following code but keep getting
the error:

'Specify the table containg the records you wish to delete'

DELETE [tbl_Street_Names].[StreetNameID], tbl_MSemployees.[Date Left]
FROM tbl_XXDATA INNER JOIN tbl_MSemployees ON tbl_XXDATA.[Employee number] =
tbl_MSemployees.[Employee number]
WHERE ((([tbl_Street_Names].[StreetNameID]) In (SELECT EmployeeNumber
FROM [tbl_MSEmployees])) AND ((tbl_MSemployees.[Date Left]) Is Not Null));

Any help would be appreciated as I have been trying a number of ways but as
soon as I link the tables the query fails.

The date left field does not exist in the tbl_xxData

 

[Klatuu]

Try this. (be sure to make a copy of tbl_XXXDATA before you test it

DELETE [tbl_XXXDATA].*
FROM [tbl_XXXDATA] INNER JOIN tbl_MSemployees ON tbl_XXDATA.[Employee
number] = tbl_MSemployees.[Employee number]
WHERE [tbl_MSEmployees].[Date Left] IS NOT NULL;

 

[vanderghast]

When you delete, you delete the whole record, not just some of its fields,
so you need one, and only one, tableName.*.

Note that deleting directly over a join generally requires propriatary
syntax, and with Jet, you have to add DISTINCTROW, so, give a try to:

DELETE DISTINCTROW [tbl_Street_Names].*
FROM tbl_XXDATA INNER JOIN tbl_MSemployees
ON tbl_XXDATA.[Employee number] = tbl_MSemployees.[Employee number]
WHERE ((([tbl_Street_Names].[StreetNameID]) IN (SELECT EmployeeNumber

FROM [tbl_MSEmployees]))
AND ((tbl_MSemployees.[Date Left]) Is Not Null));



Note that it sounds strange that a StreetNameID could be an EmployeeNumber
though, as your IN condition seems to imply



Vanderghast, Access MVP

 

[Klatuu]

I don't understand the need for DISTINCTROW.
I did a test before I sent the code and my test worked correctly.
--
Dave Hargis, Microsoft Access MVP

When you delete, you delete the whole record, not just some of its fields,
so you need one, and only one, tableName.*.

Note that deleting directly over a join generally requires propriatary
syntax, and with Jet, you have to add DISTINCTROW, so, give a try to:

DELETE DISTINCTROW [tbl_Street_Names].*
FROM tbl_XXDATA INNER JOIN tbl_MSemployees
ON tbl_XXDATA.[Employee number] = tbl_MSemployees.[Employee number]
WHERE ((([tbl_Street_Names].[StreetNameID]) IN (SELECT EmployeeNumber

FROM [tbl_MSEmployees]))
AND ((tbl_MSemployees.[Date Left]) Is Not Null));



Note that it sounds strange that a StreetNameID could be an EmployeeNumber
though, as your IN condition seems to imply



Vanderghast, Access MVP

I am trying to delete all records from tbl_XXdata where the tbl_MSEmployees
shows a leaving date against it. I have the following code but keep
getting
the error:

'Specify the table containg the records you wish to delete'

DELETE [tbl_Street_Names].[StreetNameID], tbl_MSemployees.[Date Left]
FROM tbl_XXDATA INNER JOIN tbl_MSemployees ON tbl_XXDATA.[Employee number]
=
tbl_MSemployees.[Employee number]
WHERE ((([tbl_Street_Names].[StreetNameID]) In (SELECT EmployeeNumber
FROM [tbl_MSEmployees])) AND ((tbl_MSemployees.[Date Left]) Is Not
Null));

Any help would be appreciated as I have been trying a number of ways but
as
soon as I link the tables the query fails.

The date left field does not exist in the tbl_xxData

 

[vanderghast]

That should be tbl_XXDATA.*, not [tbl_Street_Names].*


Vanderghast, Access MVP

When you delete, you delete the whole record, not just some of its fields,
so you need one, and only one, tableName.*.

Note that deleting directly over a join generally requires propriatary
syntax, and with Jet, you have to add DISTINCTROW, so, give a try to:

DELETE DISTINCTROW [tbl_Street_Names].*
FROM tbl_XXDATA INNER JOIN tbl_MSemployees
ON tbl_XXDATA.[Employee number] = tbl_MSemployees.[Employee number]
WHERE ((([tbl_Street_Names].[StreetNameID]) IN (SELECT EmployeeNumber

FROM [tbl_MSEmployees]))
AND ((tbl_MSemployees.[Date Left]) Is Not Null));



Note that it sounds strange that a StreetNameID could be an
EmployeeNumber though, as your IN condition seems to imply



Vanderghast, Access MVP

I am trying to delete all records from tbl_XXdata where the
tbl_MSEmployees
shows a leaving date against it. I have the following code but keep
getting
the error:

'Specify the table containg the records you wish to delete'

DELETE [tbl_Street_Names].[StreetNameID], tbl_MSemployees.[Date Left]
FROM tbl_XXDATA INNER JOIN tbl_MSemployees ON tbl_XXDATA.[Employee
number] =
tbl_MSemployees.[Employee number]
WHERE ((([tbl_Street_Names].[StreetNameID]) In (SELECT EmployeeNumber
FROM [tbl_MSEmployees])) AND ((tbl_MSemployees.[Date Left]) Is Not
Null));

Any help would be appreciated as I have been trying a number of ways but
as
soon as I link the tables the query fails.

The date left field does not exist in the tbl_xxData

 

[vanderghast]

It may be nothing more, now, than an extra protection from previous versions
(see http://www.mvps.org/access/queries/qry0022.htm)


Vanderghast, Access MVP

I don't understand the need for DISTINCTROW.
I did a test before I sent the code and my test worked correctly.
--
Dave Hargis, Microsoft Access MVP

When you delete, you delete the whole record, not just some of its
fields,
so you need one, and only one, tableName.*.

Note that deleting directly over a join generally requires propriatary
syntax, and with Jet, you have to add DISTINCTROW, so, give a try to:

DELETE DISTINCTROW [tbl_Street_Names].*
FROM tbl_XXDATA INNER JOIN tbl_MSemployees
ON tbl_XXDATA.[Employee number] = tbl_MSemployees.[Employee number]
WHERE ((([tbl_Street_Names].[StreetNameID]) IN (SELECT EmployeeNumber

FROM [tbl_MSEmployees]))
AND ((tbl_MSemployees.[Date Left]) Is Not Null));



Note that it sounds strange that a StreetNameID could be an
EmployeeNumber
though, as your IN condition seems to imply



Vanderghast, Access MVP

I am trying to delete all records from tbl_XXdata where the
tbl_MSEmployees
shows a leaving date against it. I have the following code but keep
getting
the error:

'Specify the table containg the records you wish to delete'

DELETE [tbl_Street_Names].[StreetNameID], tbl_MSemployees.[Date Left]
FROM tbl_XXDATA INNER JOIN tbl_MSemployees ON tbl_XXDATA.[Employee
number]
=
tbl_MSemployees.[Employee number]
WHERE ((([tbl_Street_Names].[StreetNameID]) In (SELECT EmployeeNumber
FROM [tbl_MSEmployees])) AND ((tbl_MSemployees.[Date Left]) Is Not
Null));

Any help would be appreciated as I have been trying a number of ways
but
as
soon as I link the tables the query fails.

The date left field does not exist in the tbl_xxData