If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Delete query doesn't!!
My apologies, I meant to post this to the OP not the responder. I hope
she/he will see this and respond. "Chris2" wrote in message ... "John Spencer" wrote in message ... Pardon me for butting in. You probably have a relationship set between Client and Meals. Yes. In my first post I wrote: MasterID looks like the Primary Key of client, so I'm using it that way. If you have it set to maintain data integrity, you cannot delete the client until you have deleted ALL the related records in the Meals table. Access will delete the client and the related meals records IF you set Cascade Delete option to true. Yes. I also wrote: Open the relationships window and show both tables. Right-click on the relationship link. Check both boxes for cascading on DELETES and UPDATES. and: Because we've got cascading deletes going, the meals table got it's rows for Mr. Jay Smith deleted, as well. From an earlier reply to your post entitled "Delete Query Problem" Do you want to delete the records in the Meals table or in the Client table or in both? The OP wrote: I wish to delete all clients that have not been seen for two years or more. A "meal" table contains the last visit date. Does the meals table have multiple records for each MasterID? I am guessing that LastVisit is a date field that actually specifies the date the meals were received. Only the OP can say for sure. I used multiple records in meals, but that was an assumption on my part. Sincerely, Chris O. |
#12
|
|||
|
|||
Delete query doesn't!!
Good catch Chris!
I was so focused on "Cannot delete from specified table" error, I never paid attention to WHERE clause! Does this make more sense? DELETE DISTINCTROW client.* FROM client INNER JOIN [SELECT MasterID, Max(LastVisit) As LatestVisit FROM meals GROUP BY MasterID]. As m ON client.MasterID = m.MasterID WHERE ((m.LastestVisit)DateAdd("yyyy", -2, Date())); or I suppose you could add WHERE clause to virtual table. Plus, John brought up something I never thought about... Depending on relationships (and since you can only delete from one table in query), it is possible you cannot delete from client as long as there are records for those clients in meals... I have to get back to my "job work" and now regret tacking a what-I-thought was a brief post to a more-complicated situation than I have bandwidth to pursue. Apologies and please disregard all my posts to this thread. gary "Chris2" wrote: "Gary Walter" wrote: PMFBI A delete query involving more than one table is one of the few situations where *DISTINCTROW* means anything anymore in Access 200x. http://support.microsoft.com/default...b;en-us;207761 I might suggest trying on copy of data: DELETE DISTINCTROW client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE ((meals.LastVisit)DateAdd("yyyy", -2, Date())); snip The above query will attempt to delete clients that are still active. When I run the above query, it attempts to delete Dee Smith (MasterID 2) from client, even though there is a LastVisit in meals more recent that two years ago. Sincerely, Chris O. |
#13
|
|||
|
|||
Delete query doesn't!!
"Van T. Dinh" wrote in message ... OK. Try: DELETE DISTINCT client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE ((meals.LastVisit)DateAdd("yyyy", -2, Date())); -- HTH Van T. Dinh MVP (Access) That generates: "The Microsoft JET database engine does not recognize 'DISTINCT client.*' as a valid field name or expression." Sincerely, Chris O. |
#14
|
|||
|
|||
Delete query doesn't!!
"Gary Walter" wrote in message ... PMFBI A delete query involving more than one table is one of the few situations where *DISTINCTROW* means anything anymore in Access 200x. http://support.microsoft.com/default...b;en-us;207761 I might suggest trying on copy of data: DELETE DISTINCTROW client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE ((meals.LastVisit)DateAdd("yyyy", -2, Date())); It has been awhile since I visited this... so, if you don't mind, would you mind posting back with your testing. The reason I ask is because I vaguely remember needing also a LEFT JOIN which does not make sense to me right now. Apologies again for butting in. good luck, gary One other point is: Are you sure you want to "lose data." This can come back to bite you in some situations. A common alternative is to add a field to "client" like "fActive" (default -1) and instead of deleting, change "fActive" to 0. Gary Walter, The above query will attempt to delete clients that are still active. When I run the above query, it attempts to delete Dee Smith (MasterID 2) from client, even though there is a LastVisit in meals more recent that two years ago. Sincerely, Chris O. |
#15
|
|||
|
|||
Delete query doesn't!!
"John Spencer" wrote in message ... Pardon me for butting in. You probably have a relationship set between Client and Meals. Yes. In my first post I wrote: MasterID looks like the Primary Key of client, so I'm using it that way. If you have it set to maintain data integrity, you cannot delete the client until you have deleted ALL the related records in the Meals table. Access will delete the client and the related meals records IF you set Cascade Delete option to true. Yes. I also wrote: Open the relationships window and show both tables. Right-click on the relationship link. Check both boxes for cascading on DELETES and UPDATES. and: Because we've got cascading deletes going, the meals table got it's rows for Mr. Jay Smith deleted, as well. From an earlier reply to your post entitled "Delete Query Problem" Do you want to delete the records in the Meals table or in the Client table or in both? The OP wrote: I wish to delete all clients that have not been seen for two years or more. A "meal" table contains the last visit date. Does the meals table have multiple records for each MasterID? I am guessing that LastVisit is a date field that actually specifies the date the meals were received. Only the OP can say for sure. I used multiple records in meals, but that was an assumption on my part. Sincerely, Chris O. |
#16
|
|||
|
|||
Delete query doesn't!!
Nope. It doesn't like the word "DISTINCT".
"Van T. Dinh" wrote in message ... OK. Try: DELETE DISTINCT client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE ((meals.LastVisit)DateAdd("yyyy", -2, Date())); -- HTH Van T. Dinh MVP (Access) "Ron Le Blanc" wrote in message ... This generates a "Cannot delete from specified table" error. |
#17
|
|||
|
|||
Delete query doesn't!!
It looks like Access/JET doesn't like deleting the "One" Records in a Join.
Admiited, I mostly delete the "Many" Records first before deleting the "One" Records whether I enforce Cascade Delete or not. I think you may need a SubQuery in this case. Try something like: DELETE client.* FROM client WHERE Client.MaterID Not In ( SELECT DISTINCT Meals.MasterID FROM Meals WHERE (Meals.LastVisit DateAdd("yyyy", -2, Date()) ); The above should also delete Records that don't have related Records in Meals which is probably what you need. The only problem is this type of Query is fairly slow because of the Not In Clause. Try also: DELETE client.* FROM client LEFT JOIN ( SELECT Meals.MasterID FROM Meals WHERE (Meals.LastVisit DateAdd("yyyy", -2, Date()) ) AS SubQ ON client.MasterID = SubQ.MasterID WHERE SubQ.MasterID Is Null; If the second SQL works (which I am not sure), it should me much faster than the first SQL. -- HTH Van T. Dinh MVP (Access) "Ron Le Blanc" wrote in message .. . Nope. It doesn't like the word "DISTINCT". |
#18
|
|||
|
|||
Delete query doesn't!!
Chris2
I just applied similar syntax with "Distinctrow" and it appeared to delete all required non-unique rows. Thanks for your input everyone. Ben "Chris2" wrote: "Van T. Dinh" wrote in message ... OK. Try: DELETE DISTINCT client.* FROM client INNER JOIN meals ON client.MasterID = meals.MasterID WHERE ((meals.LastVisit)DateAdd("yyyy", -2, Date())); -- HTH Van T. Dinh MVP (Access) That generates: "The Microsoft JET database engine does not recognize 'DISTINCT client.*' as a valid field name or expression." Sincerely, Chris O. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Access 2k/2003: Touble with delete query using WHERE EXISTS | Darin | Running & Setting Up Queries | 17 | August 25th, 2005 06:58 PM |
Delete query won't run! | faramir | Running & Setting Up Queries | 7 | February 7th, 2005 03:26 AM |
Moving from xBase/Clipper | [email protected] | New Users | 1 | February 3rd, 2005 07:25 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |