By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,117 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,117 IT Pros & Developers. It's quick & easy.

a query that finds criteria and deletes all but the last record found

P: 14
MS ACCESS 2003
Is it possible to make a query that searches a table for criteria and then updates the table deleting all of the matched in the query except for the last one (say if the records have autonumbered primary key, the one with the highest key, say).

Thanks
Mar 17 '07 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,628
MS ACCESS 2003
Is it possible to make a query that searches a table for criteria and then updates the table deleting all of the matched in the query except for the last one (say if the records have autonumbered primary key, the one with the highest key, say).

Thanks
The following code searches a Table named tblTest for all CPTs in the Rank Field. It then DELETES all the Records except the one with the HIGHEST AutoNumber Value as indicated by the [MyKey] Field. I hope this is what you were looking for.
Expand|Select|Wrap|Line Numbers
  1. Dim intCriteriaCount As Integer, MyDB As Database, MyRS As Recordset
  2. Dim intCounter As Integer
  3.  
  4. intCriteriaCount = DCount("*", "tblTest", "[Rank]='CPT'")
  5.  
  6. 'Are there Records meeting the Criteria?
  7. If intCriteriaCount > 0 Then
  8.   Set MyDB = CurrentDb()
  9.   'Order by your Primary Key Ascending
  10.   Set MyRS = MyDB.OpenRecordset("SELECT * FROM  tblTest WHERE [Rank]='CPT' ORDER BY [MyKey]")
  11.   MyRS.MoveLast: MyRS.MoveFirst
  12.     For intCounter = 1 To intCriteriaCount - 1      'All but the Last Record
  13.       MyRS.Delete
  14.       MyRS.MoveNext
  15.     Next
  16. Else
  17.   Exit Sub
  18. End If
  19.  
  20. MyRS.Close
NOTE: You must modify this code to meet your specific needs.
Mar 17 '07 #2

P: 14
Well i adapted the code as follows:
Private Sub Command74_Click()
Dim intCriteriaCount As Integer, MyDB As Database, MyRS As Recordset
Dim intCounter As Integer

intCriteriaCount = DCount("*", "FindDuplicatesWeeklyPoints")

'Are there Records meeting the Criteria?
If intCriteriaCount > 0 Then
Set MyDB = CurrentDb()
'Order by your Primary Key Ascending
Set MyRS = MyDB.OpenRecordset("SELECT * FROM FindDuplicatesWeeklyPoints ORDER BY [Weekly Index]")
MyRS.MoveLast: MyRS.MoveFirst
For intCounter = 1 To intCriteriaCount - 1 'All but the Last Record
MyRS.Delete
MyRS.MoveNext
Next
Else
Exit Sub
End If

MyRS.Close
End Sub

But it deletes all but the last record, but what i need is:

all but the last record for each ChildsName.
Mar 17 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.