Delete query w/child table

[Trevor]

I have a master(parent) table [VMSU-IL] and a child table [bill select]
Is it posable to use a delete query on the parent table to delete by date the linking field between the 2 tables is IDNumber?
would I just make a delete query the same way I would for a single table and just add the child table to the list of tables in the delete query? or how would I go about a delete query by date to also delete the associated records on the child table?

[orange]

I have a master(parent) table [VMSU-IL] and a child table [bill select]
Is it posable to use a delete query on the parent table to delete by date the linking field between the 2 tables is IDNumber?
would I just make a delete query the same way I would for a single table and just add the child table to the list of tables in the delete query? or how would I go about a delete query by date to also delete the associated records on the child table?

Can you show us the SQL that you have tried so far?

[Trevor]

Field: Date Date
Table: VMSU-IL Bill select
Where
Criteria: <Date()-740
Note this is done in design mode for access query, so the table: [Table name] [Table name] the space denotes the next colume of info

[asingh]

This will perform a full inner join on the two tables..and delete data from both the tables (master and child).

DELETE Table1.*, Table2.*
FROM Table1 INNER JOIN Table2 ON Table1.a = Table2.a;'

thanks and regards,

asingh

[Trevor]

ok but I'm a little confused of where to put both lines, becaue my field = Date, Table = Table 1 , Delte = where, criteria = <Date()-740

[asingh]

The criteria....is driven of which table....??

[Trevor]

the parent table is [VMSU-IL], witch contains the date, the child table I want to also delete usin the Date -740 is [VMSU-Bill select]

[asingh]

DELETE [VMSU-IL].*, [VMSU-Bill select].*
FROM [VMSU-IL] INNER JOIN [VMSU-Bill select] ON [VMSU-IL].[your field] = [VMSU-Bill select].[your field] WHERE [VMSU-Bill select].[Date]= "-740";

[Trevor]

Thanks, I'li have to try that when i get my subform and main form workin correctly. if you want you can check out my post to stop mouse scroll

[Trevor]

I Changed My tables Refelected in the 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";

I recieve an Error when I atempt to run
"Microsoft jet engine does not recongnise VMSU-ILT-Main".* as a vaid field or expresion , but even in the help file it says you can you an * for all tables.
I even tried [VMSU-ILT-Main].Date and I get error message that closes out when i click ok "specify which table to delete from"
you can't use inner join in an access delete query, I found out by searchin'

[orange]

I Changed My tables Refelected in the 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";

I recieve an Error when I atempt to run
"Microsoft jet engine does not recongnise VMSU-ILT-Main".* as a vaid field or expresion , but even in the help file it says you can you an * for all tables.
I even tried [VMSU-ILT-Main].Date and I get error message that closes out when i click ok "specify which table to delete from"
you can't use inner join in an access delete query, I found out by searchin'
I'm not sure why Access is having trouble with [VMSU-ILT-Main], but your Date expression is incorrect.

The syntax would be
.....
WHERE [VMSU-ILT-Main].[Date] < Date()-740;

which means that you would delete records where the
[VMSU-ILT-Main].[Date] is less than today's Date - 740 days.

[Trevor]

Yes I am aware of this but to delete records where the date is equal to 740 days or grater then the date of the record it deletes with <Date()-740 (my previous post was an attempt at using asingh's post)
I have tried adding a relationship between vmsu-ilt-main and vmsu-ILT-sub where the IdNumber in both tables are the same and deleteing by Date in VMSU-ILT-main , select inforce intagrety on the relationship and I get an error 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

[orange]

Yes I am aware of this but to delete records where the date is equal to 740 days or grater then the date of the record it deletes with <Date()-740 (my previous post was an attempt at using asingh's post)
I have tried adding a relationship between vmsu-ilt-main and vmsu-ILT-sub where the IdNumber in both tables are the same and deleteing by Date in VMSU-ILT-main , select inforce intagrety on the relationship and I get an error 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
Trevor,
I'm confused by your table names. In the first message you said master(parent) table [VMSU-IL] and a child table [bill select]
but now you're using
tried adding a relationship between vmsu-ilt-main and vmsu-ILT-sub where the IdNumber in both tables are the same.

????

[Trevor]

I changed the table names, to VMSU-ILT-main, and vmsu-ILT-Sub, sorry for the confusion, the only way I have found so far to achieve the same end result is add the date field to the vmsu-ILT-sub and delete by date, but Im still interested in how to delete from the child table from records on the master table