Modified Delete Query

[jcbhydro]

I have a 'delete query' to remove 'leavers' from my main table [Mail List]. It works well. I have another table [GroupMembers] from which I require to delete relevant records of 'leavers' simultaneously.
Can anyone suggest why the following SQL code fails to work. It brings up a dialogue box which says 'specify the table containing records to delete.', but the 2 tables are listed in the code and in the design view of the query.

Quote DELETE [Mail List].[Member ID], [Mail List].Surname, [Mail List].[First Name], [Mail List].Phone, [Mail List].[E-Mail], [Mail List].Leaving, GroupMembers.[Group ID], GroupMembers.[Member ID], GroupMembers.[Group Name], GroupMembers.[Member Name]
FROM [Mail List] LEFT JOIN GroupMembers ON [Mail List].[Member ID] = GroupMembers.[Member ID]
WHERE ((([Mail List].Leaving)=True));Unquote

Regards,

jcbhydro

 

[jdraw]

Instead of DELETE, can you run a SELECT query to see which records are displayed.
What happens if you try INNER JOIN rather than LEFT.

It is safer to use a SELECT --which is read only -- and make sure the records being selected are the ones you want to delete.

 

[jcbhydro]

Thank you jdraw for your response to my problem.

I see the logic of using INNER JOIN. However, unfortunateley, that alone doesn't solve the problem. I stll get the message 'Specify table to be used'.

On the subject of SELECT v. DELETE, SELECT is not relevant in this case as I run an Append query prior to deletion which appends all the prospective deletions to a 'Leavers' Table where they can be inspected and retained for further administrative purposes.

Any further suggestions for the initial problem would be gratefully received.

Regards,

jcbhydro

 

[namliam]

You can only delete from one table at a time... you have a select statement that you simply replaced select by delete.

Try the below

DELETE GroupMembers.[Group ID], GroupMembers.[Member ID], GroupMembers.[Group Name], GroupMembers.[Member Name]
FROM [Mail List] LEFT JOIN GroupMembers ON [Mail List].[Member ID] = GroupMembers.[Member ID]
WHERE ((([Mail List].Leaving)=True));

If that fails for what ever reason....
try

Delete GroupMembers.[Member ID]
From GroupMembers
where [Member ID] in (select [Mail List].[Member ID] 
                      from [Mail List] 
                      WHERE [Mail List].Leaving=True)

Also I would like to point you to Naming conventions, please read and understand it.... If atleast for future use.

 

[jcbhydro]

Thank you for the comments.

I hadn't appreciated that a 'delete query' in Access is limited to a single table. I'm sure that wasn't the case with DBase 3 or Lotus Approach.

Your first suggested code didn't work for a separate 'Delete Query' with the 2nd Table GroupMembers. However, the second set of code does work.
Separate queries seem a bit clumsy for a simple deletion from related tables. Is there not some way to cascade queries? I assume it could be done with a macro.

On the subject of 'naming conventions', I have read the article and am aware of the recommended convention. Unfortunately, I inherited this database and was concerned to amend field and table names for fear of corrupting the whole thing.

Is it possible to rename fields and tables etc. retrospectively?

Regards,

jcbhydro

 

[namliam]

Separate queries seem a bit clumsy for a simple deletion from related tables. Is there not some way to cascade queries? I assume it could be done with a macro.

Deletes are a nightmare and unless constraints and all that are setup right and near perfect will be in most databases. Same as updates that can cause havoc sometimes.

Macro's, brrrr, they exist in access for no real appearent reason. The poor man's VBA, if you want to do things right, do them in VBA.

On the subject of 'naming conventions', I have read the article and am aware of the recommended convention. Unfortunately, I inherited this database and was concerned to amend field and table names for fear of corrupting the whole thing.

As long as you are aware
Renaming fields in an existing database can be a nightmare, best to leave things alone with inherited crap since you never exactly know what the downpoor from any change can be.