Access Inner Join Delete query

[Trevor2007]

Hi,
I have 2 tables [VMSU-ILT-Main] and [VMSU-ILT-Sub]
The VMSU-ILt-Main Table contains info about a person on the main form, the VMSU-ILT-Sub Table contains info about the subform on the main form. they both have [IDNumber] as ehe linked fied, and I am trying to delete records on both tables by Date, the date is only stored in vMSU-ILT-main table. I am getting error when I try to run my query:

DELETE [VMSU-ILT-Main].*,[VMSU-ILT-Sub].*
FROM [VMSU-ILT-Main] INNER JOIN [VMSU-ILT-Sub] ON [VMSU-ILT-Main].[IDNumber] = [VMSU-ILT-Sub].[IDNumber] WHERE [VMSU-ILT-Main].[Date]= "-740";

The error I get states "cannot delete from specified tables"
I have also tried changing my first(delete) line to Delete [VMSU-ILT-Main].*, [VMSU-ILT-Sub] *
and I get error "jet engine does not recognise VMSU-ILT-Main .*"
I have read though the threads and haven't found a solution that works, I have even tried deleting the child table records first but still I get the error message "cannot delete from specified tables."
aparently w/ an * you cannot specify criteria as with SQL .
thank for helping

[Stewart Ross]

Hi Trevor. Problem is that Delete is intended to delete records in a single table, so you can't use the join query to do so.

If you set the relationship between the two underlying tables to Cascade Delete you will be able to delete associated records on the many side of the relationship when you delete records on the one side. You can then delete the main table records and the associated records using the single DELETE statement listed below:

DELETE [VMSU-ILT-Main].*
FROM [VMSU-ILT-Main] WHERE [VMSU-ILT-Main].[Date]= "-740";
 

I am assuming that your where condition on the Date field is correct, even though you are referring to a string that does not look like a date to me.

Regards

Stewart

ps Cascade Delete is not set by default because it can be quite dangerous - delete one record and lose a whole lot more in one go!

[Trevor2007]

I tried to set up my cascade delete, but to click cascede delete I have to click inforce integrety ,
as you know from my previous post that the only linking field is IDNumber,
but when I try to create my link, I get an error message saying that the main table does not contain records from the sub table , example there may be records related to the employees in the related table but no records in the for the employee in the primary table.
I could just add a date field to the vmsu-ILT-sub table and delete by date , that would give me the same end result I would like to try to avoid having to do that.
Thanks Trevor 2007

[Stewart Ross]

I tried to set up my cascade delete, but to click cascede delete I have to click inforce integrety ,
as you know from my previous post that the only linking field is IDNumber,
but when I try to create my link, I get an error message saying that the main table does not contain records from the sub table...

Hi Trevor. The error means that there are records in your sub-table that don't have matching IDs in the main table. Because the 1-to-many relationship between the tables is already broken Access cannot set up the 1-M relationship. Your current link between the main form and subform will hide these unmatched records (since the link will only show the ones that do exist in both tables). It is possible to find all records in the subtable which aren't matched in the main table with a left-join query:

 
Select [VMSU-ILT-Sub].*
FROM [VMSU-ILT-Sub] LEFT JOIN [VMSU-ILT-Main] ON [VMSU-ILT-Main].[IDNumber] = [VMSU-ILT-Sub].[IDNumber] WHERE ([VMSU-ILT-Main].[IDNumber]) Is Null;
 

At least you would be able to find the unmatched records then decide what to do with them, and whether or not to leave the relationship between the tables an informal one.

-Stewart

[Trevor2007]

So once the table link is broken its broken and can't be fixed?

it's looking like the easiest way to to do this is just to add the date to the sub table and just delete by date rather then haveing 2 + quries to do the same job?
I have no clue why access must be so difficult w/ inner join delete quries.
Thanks, Trevor 2007
what are your thaughts?

[Stewart Ross]

So once the table link is broken its broken and can't be fixed?
it's looking like the easiest way to to do this is just to add the date to the sub table and just delete by date rather then haveing 2 + quries to do the same job?
I have no clue why access must be so difficult w/ inner join delete quries.
Thanks, Trevor 2007
what are your thaughts?

Sorry if I have left you confused, Trevor. The relationship is broken at present because of the unmatched records; find these and resolve them back to matched records, or delete them if they are no longer required, and you certainly can fix the relationship.

You can use the left-join query suggested in the last response (or any other suitable method) to find the unmatched records on the many-side of the table relationship. These may be orphaned records left by deletion of the one-side records at some stage, leaving the many-side records with no match. Access itself would have prevented such a situation from arising in the first place if referential integrity had already been set. It really is best for the integrity of your data to find the unmatched records and deal with them. You will then be able to fix the link between the two tables properly - and you can then make full use of relational cascade and update properties that you can't when the 1-many is broken.

Sorry again for confusing you.

-Stewart

[Trevor2007]

Sorry if I have left you confused, Trevor. The relationship is broken at present because of the unmatched records; find these and resolve them back to matched records, or delete them if they are no longer required, and you certainly can fix the relationship.

You can use the left-join query suggested in the last response (or any other suitable method) to find the unmatched records on the many-side of the table relationship. These may be orphaned records left by deletion of the one-side records at some stage, leaving the many-side records with no match. Access itself would have prevented such a situation from arising in the first place if referential integrity had already been set. It really is best for the integrity of your data to find the unmatched records and deal with them. You will then be able to fix the link between the two tables properly - and you can then make full use of relational cascade and update properties that you can't when the 1-many is broken.

Sorry again for confusing you.

-Stewart

I finaly got the relationship between the 2 table to work, but I am now getting the error " specify the table to delete from"
and my query in SQLview looks like:

DELETE [VMSU-ILT-Main].Date
FROM [VMSU-ILT-Main] INNER JOIN [VMSU-ILT-Sub] ON ([VMSU-ILT-Main].IDNumber = [VMSU-ILT-Sub].IDNumber) AND ([VMSU-ILT-Main].IDNumber = [VMSU-ILT-Sub].IDNumber)
WHERE ((([VMSU-ILT-Main].Date)<Date()-740));

Do you know why access has to be a pain in the @$$ when it comes to delete innerjoin queries?
Thanks for your continuing help

[Stewart Ross]

I finaly got the relationship between the 2 table to work, but I am now getting the error " specify the table to delete from"
and my query in SQLview looks like:

DELETE [VMSU-ILT-Main].Date
FROM [VMSU-ILT-Main] INNER JOIN [VMSU-ILT-Sub] ON ([VMSU-ILT-Main].IDNumber = [VMSU-ILT-Sub].IDNumber) AND ([VMSU-ILT-Main].IDNumber = [VMSU-ILT-Sub].IDNumber)
WHERE ((([VMSU-ILT-Main].Date)<Date()-740));

Do you know why access has to be a pain in the @$$ when it comes to delete innerjoin queries?
Thanks for your continuing help

Hi Trevor. Delete queries in Access are single-table only, so whatever way you try to join tables in a DELETE query it will result in similar issues. If you have indeed set Cascade Delete between the main and sub tables, you won't need the inner join at all. The Cascade Delete will remove the sub-table entries matching the main table entries when the query is run. Adjusted SQL for the single-table delete is below:

DELETE [VMSU-ILT-Main].Date
FROM [VMSU-ILT-Main] WHERE ((([VMSU-ILT-Main].Date)<Date()-740));

I'm glad you are making progress with this, and recognise the frustration that such an apparently simple thing to do turns out not to be.

Regards

Stewart

[NeoPa]

Access (Jet) SQL is limited in this area (as compared with other SQL engines - MSSQL; Oracle; etc). I often use a work-around which is to update the record(s) first using an update query (less picky) then use the updated data to determine which records to delete.
A typical example is to search the dataset for records to delete and change their Name or Description fields to something like "DeleteMe". The next step is a simpler one, to delete all records with "DeleteMe" as the Name or Description.
It's a bit of a kludge, but is a workable method nevertheless.