Hey,
Basically my goal is to have records in my customer table deleted from a date in my Service Allocations table. They are related by the Customer ID that is contained in both tables.

Customer Table:
Customer ID
field 1
field 2
field 3
field 4
so on

Service Allocations:
S-Allocation ID
Dte
Customer ID
Mechanic ID
Automobile ID
Service ID

So where the date is more than 5 years old from the current date (I'm using <=DateAdd("yyyy",-5,Date()) to do this), I want this query to delete the related customer records. How can I go about doing this as I keep getting the "Could not delete from specified tables" error. The actual code works in datasheet view, but somehow when I run the delete query it doesn't work.

The SQL for my query is:
DELETE [Service Allocations].Dte, Customer.*
FROM Customer INNER JOIN [Service Allocations] ON Customer.[Customer ID] = [Service Allocations].[Customer ID]
WHERE ((([Service Allocations].Dte)<=DateAdd("yyyy",-5,Date())));


Thanks for reading!
---------
Hamid

It's too risky to delete Customer records where a Service Allocation exists that is five years old or older. Suppose you have a Customer with two Service Allocations, one older than five years and one two weeks old? By your logic, the Customer record would get deleted because a Service Allocation older than five years exists for that Customer.

A better approach would be to delete Customer records for which no Service Allocations exist that are newer than five years. See the following SQL for an example:

DELETE C1.*
FROM Customer AS C1
WHERE NOT EXISTS
(SELECT S1.*
FROM [Service Allocations] AS S1
WHERE S1.[Customer ID] = C1.[Customer ID]
AND S1.Dte > DateAdd('yyyy',-5,Date())
)
;

Thanks so much ByteMyzer!
It worked perfectly, and is exactly what I needed.
---
Hamid