Access Query Problem

[JaredM]

I'm making a delete query that doesn't work. I included a picture attached to this post so you can look at it. (It gives me an error, the help suggests it may be read only, but I don't have it set to read only)

Here's what the query is inteneded to do.

I have an OrgChart that lists employees, subsidiaries, and positions they hold.

IE - Me IBM Design

If a person has at least one position at a subsidiary he is specified as a officer, director, or both by the person who enters the data. However, if they delete the record on the orgchart they will still exist on the Employee title table with a officer, director, or both specification. I want this query to seek out and get all the records on the Employee Title table that indicate people who don't hold any positions at the subsidiary. This query does return all those who fit the criteria, but it won't delete them.

[vbgladiator]

Click on the SQL button in the toolbar and then post the SQL statement.

[DerFarm]

I suspect that even when you designate the table that the record
live in (double click the * on the table you want deleted from)
you are going to get another error:

Must be updateable query.


I run into this all the time. The quickest way I've found, is to
update the key field of the record you wish to delete to NULL.

Then delete from the table where this field is null.

[KWell]

Dear JaredM,

You must select what you want to delete. In your case it is the table EmployeTitle.
Here is the correct SQL string for your delete query.

DELETE EmployeTitle.*, OrgChart.ID
FROM EmployeTitle LEFT JOIN OrgChart ON (EmployeTitle.[Officer ID] = OrgChart.[Subsidiary ID]) AND (EmployeTitle.[Subsidiary ID] = OrgChart.[Officer ID])
WHERE (((OrgChart.ID) Is Null));



KWell

[JaredM]

Thanks, but it still give me an error. It works in the View, but it doesn't allow me to run it. It says couldn't delete from specified tables.



If you want the old SQL:

DELETE EmployeeTitle.[Officer ID], EmployeeTitle.[Subsidiary ID], EmployeeTitle.Title, OrgChart.ID
FROM OrgChart RIGHT JOIN EmployeeTitle ON (OrgChart.[Officer ID] = EmployeeTitle.[Officer ID]) AND (OrgChart.[Subsidiary ID] = EmployeeTitle.[Subsidiary ID])
WHERE (((OrgChart.ID) Is Null));

[JaredM]

Still having problems with it. Anyone want to help out?

[InvisibleDuncan]

Try taking the OrgChart.ID out of the Delete part of the statement:

DELETE EmployeeTitle.[Officer ID], EmployeeTitle.[Subsidiary ID], EmployeeTitle.Title
FROM OrgChart RIGHT JOIN EmployeeTitle ON (OrgChart.[Officer ID] = EmployeeTitle.[Officer ID]) AND (OrgChart.[Subsidiary ID] = EmployeeTitle.[Subsidiary ID])
WHERE (((OrgChart.ID) Is Null))

[InvisibleDuncan]

As I said in my PM, I think I've sorted it. The problem is that my previous one gives you a non-updateable recordset. If you do it as a select query and try to delete the results, it doesn't let you. So, you have to do it so that you're only selecting from one table:

DELETE EmployeeTitle.[Officer ID], EmployeeTitle.[Subsidiary ID], EmployeeTitle.Title 
FROM EmployeeTitle 
Where (EmployeeTitle.[Officer ID] & EmployeeTitle.[Subsidiary ID]) Not In (Select OrgChart.[Officer ID] & OrgChart.[Subsidiary ID] From OrgChart)

This works; I've tested it.

[JaredM]

OMG Duncan, you are the man. Thanks a lot!