 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2006I have a "delete query" query!
I have a one to many relationship and I want to delete all records in the "many" table EXCEPT those records who have the most recent date. Having written this it has occurred to me to have a "NOT" criteria. How would I write it? I know it is a simple thing but need to be sure! If your table and field names don't have spaces in them or otherwise require square brackets [] then you should be able to write a query that looks like the following. DELETE DistinctRow M.* FROM ManyTable as M WHERE M.PrimaryKey NOT IN ( SELECT Primary Key FROM ManyTable Inner JOIN (SELECT ForeignKey, Max(DateField) as LatestDate FROM ManyTable as Temp GROUP BY ForeignKey) AS q1 ON ManyTable.ForeignKey = q1.ForeignKey AND ManyTable.DateField = q1.LatestDate ) >I have a one to many relationship and I want to delete all records in the > "many" table EXCEPT those records who have the most recent date. Having > written this it has occurred to me to have a "NOT" criteria. > > How would I write it? I know it is a simple thing but need to be sure! Thanks for the reply but I wasn't expecting anything that complicated. I have a switchboard form for which I have unbound text boxes for date criteria. What I had envisioned is being able to put in a date and click a button so it deletes all the records from the "many" table apart from that date. That is why I thought a "NOT" criteria would suffice. > If your table and field names don't have spaces in them or otherwise require > square brackets [] then you should be able to write a query that looks like [quoted text clipped - 17 lines] > > > > How would I write it? I know it is a simple thing but need to be sure! First, let me add a warning. BACKUP your data before doing the delete. You cannot UNDO the delete action. You could use simple criteria but this would delete all the records in the many table that had dates that were not equal to what you input. My understanding was that the latest date (Maximum Date) would vary depending on the One table records. Example many table (Dates displayed in m/d/y format): PK FK DateValue 1 --- 2 --- 1/1/06 2 --- 2 --- 1/3/06 3 --- 5 --- 1/1/06 4 --- 5 --- 2/1/06 So you would want to delete rows with PK of 1 and 3. If you simply deleted all rows with a date of <> #1/3/06, then you would only keep row 2. > Thanks for the reply but I wasn't expecting anything that complicated. I > have [quoted text clipped - 29 lines] >> > >> > How would I write it? I know it is a simple thing but need to be sure! What would the criteria be if I wanted to delete all records except for those with the two most recent weeks. I don't think I would have to use an unbound text box to specifiy a date. There must be a way using the max function. thanks > First, let me add a warning. BACKUP your data before doing the delete. You > cannot UNDO the delete action. [quoted text clipped - 47 lines] > >> > > >> > How would I write it? I know it is a simple thing but need to be sure! Field: DateField Criteria: < DateAdd("d",-14,Date()) That should select all records where the date is more than 14 days ago. Is that what you want? > What would the criteria be if I wanted to delete all records except for > those [quoted text clipped - 65 lines] >> >> > How would I write it? I know it is a simple thing but need to be >> >> > sure! Ive sorted it out. Thanks > Field: DateField > Criteria: < DateAdd("d",-14,Date()) [quoted text clipped - 71 lines] > >> >> > How would I write it? I know it is a simple thing but need to be > >> >> > sure! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.