Problems with Delete Query

[SSharma]

Hi,

I am totally frustrated with this and hope someone can help! I have a database with 3 tables -- Invoice, Inspector and Payments. The 3 tables are joined on a field called Invoice Number. The Invoice table is on the "one" side and the other two tables are on the "many" side of the relationship. I have also checked the "Enforce Referential Integrity" box and the "Cascade Delete Related Records" box.

I then created a Delete query with all three tables and set a parameter on the Invoice Number field of the Invoice table. What I want this query to do is to get the invoice number from the user and delete all records with this Invoice number from all 3 tables.

But, this does not happen. When I run the query, it gets the invoice number but then I get the message "could not delete from specified tables". No records are deleted from any table.

What am I doing wrong?!! I am fairly new to Access...I would much appreciate any help.

Seetha

 

[Rich]

You only need to delete the record in the invoice table, the others will be deleted automatically

 

[SSharma]

THANK YOU!

Hi Rich and Pat,

Thank you so much for responding to my post. I did that -- took out the "extra" two tables -- and everything works beautifully! I can't believe it was so simple and I just couldn't see it for so long...

Thank you once again.

Seetha

 

[ST4RCUTTER]

I have the same problem that SSharma is having but with only one linked table. Understanding what Pat has said about deleting records on the one side, how can I construct this to delete the records on the one side and cascade to the many side when I need to specify criteria that is found on the many side of the relationship? For example, it is the many side that tells me what year the date is. I want to delete all completed records from 2008.

(One Side)____________________(Many Side)
tbl_Tasks..................................tbl_Dates
[P]TaskID..................................TaskID
Taskname..................................[P]DateID
Priority......................................DateType
CreatedBy..................................Date

Items in red signify fields that I need to specify criteria on to select the correct records on the one side. The criteria is "DateType=4" and "Date"<#1/1/2009#". The "4" tells me it is a completed date.

 

[ST4RCUTTER]

st4cutter,
Are you saying that if any of the "child" records of a tbl_Tasks record satisfies the criteria, you want to delete the parent and siblings? This is a very unusual requirement and would seem to indicate a flawed table design. I would do with DAO or by making a temp table of the FKs of the records that satisfy the criteria from tbl_dates and then (after adding a PK to the temp table) running a delete query that joins tbl_tasks to the temp table.

Pat,
You are essentially correct, but this doesn't seem like a flaw in design so much as a backwards way of deleting records. For example, if a database is tracking orders and has a table representing customers, tbl_customers, and a table to track the orders, tbl_orders, you would expect a one-to-many relationship with the tbl_cutomers being on the "one" side. This is normalized. This represents what I have done with tbl_tasks and tbl_dates.

Now in the example I just described, say that you wanted to delete all orders that occured in a previous year. If you delete the parent record from tbl_customers all orders would be deleted from tbl_orders. This is not what you would want. You only want orders that meet the criteria of being from the previous year. That in essence is what I'm trying to do.

 

[ST4RCUTTER]

If you are deleting orders, you have no need for the customer table in the query so by that logic, if you are deleting dates, you have no need for the task table in the query.

LOL! In most cases that would be true, but in this case the dates determine if the parent record should be deleted. Perhaps this does come down to a design issue. I will have to see what that better design would be.

 

[ST4RCUTTER]

Pat, You are tenacious! I will answer your question in this manner. The dates in the child records are never the same. They may be the same date value, but they represent different types of dates. For example, in tbl_Dates, there is a Primary key, TaskID, datetype, and the date value fields

tbl_Dates
*DateID
TaskID
DateType
DateValue

Now lets look at a typical row of data (record)

DateID | TaskID | DateType | DateValue
---------------------------------------
4 | 2 | 2 | 1/12/2009

Now in this record the DateID is unique to identify each record of the table.
The TaskID relates the date record to the Task in tbl_Tasks.
The DateType tells me what kind of date it is (Start Date [1], End Date [2], Due Date [3]).
The DateValue is self-explanatory.

Now, I want to be able to delete all tasks from tbl_Tasks that have an End Date[2] which is before 1/1/2009.

 

[ST4RCUTTER]

Now you're talkin! I had attempted to do this using the query builder but clearly my structure was wrong because I received the error that I needed to select the table to delete from. I'll try this again using SQL directly and see what happens. Thanks Pat!