访问VBA以从访问表中删除已过滤的记录 [英] Access VBA to Delete fitered records from access table

问题描述

大家好，

我想删除过滤记录，其中保留记录号1并删除其余记录。我能够达到这样的程度，即我得到正确的过滤记录，记录保持为1。例如，在应用过滤器后，如果返回4条记录而不是
i只想删除2到4条记录。 />

I want to delete filtered records where record no 1 is retained and rest are deleted. I am able to reach to the point where i am getting correct filtered records with record to keep comes in no 1. For e.g after applying filter if 4 records are returned than i want to delete only records 2 to 4.

以下是代码。

Below is the code.

Sub RemoveDups()
Dim strProductLine        As String
Dim strInsured_Value      As String
Dim strSql                As String
Dim strSql2               As String
Dim rs                    As DAO.Recordset
Dim rs2                   As DAO.Recordset
Dim rstFiltered           As DAO.Recordset





strSql = "SELECT distinct CrossSellImportData.ProductLine FROM CrossSellImportData;"
strSql2 = "SELECT distinct CrossSellImportData.Insured_Value FROM CrossSellImportData;"


Set rs = CurrentDb.OpenRecordset(strSql)
Set rs2 = CurrentDb.OpenRecordset(strSql2)


rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
    strProductLine = (rs!ProductLine)
    DoCmd.OpenTable "CrossSellImportData"
    DoCmd.ApplyFilter , "[ProductLine]=" & Chr(34) & strProductLine & Chr(34)
   
    rs2.MoveLast
    rs2.MoveFirst

    Do While Not rs2.EOF
        strInsured_Value = (rs2!Insured_Value)
        DoCmd.OpenTable "CrossSellImportData"
        DoCmd.ApplyFilter , "[Insured_Value]=" & Chr(34) & strInsured_Value & Chr(34)
        DoCmd.SetOrderBy "GrossPremium_Value desc"
        
        'code to delete all filtered records except top 1 record.
        rs2.MoveNext
    Loop

Loop
End Sub

谢谢，

Zav

推荐答案

您应该可以将此作为设置操作而无需任何过程代码。 我不完全确定你的例子确切地确定要保留哪些行的基础是什么，但也许我可以通过几个
的简单例子说明原则：



以下DELETE语句将删除具有相同名字和姓氏的每个联系人子集中的所有条形码：



DELETE *

来自联系人AS C1

WHERE Con​​tactID<>

     （SELECT MAX（ContactID）

      FROM Contacts AS C2

      WHERE C2.LastName = C1.LastName

      AND C2.FirstName = C1.FirstName）;



case保留的每个子集的行被任意定义为具有最高ContactID值的行，这是表的自动编号主键。



以下语句同样保留一行每个具有相同名字和姓氏的联系人子集：



DELETE *
$
来自联系人AS C1

WHERE Con​​tactID<>

     （SELECT MAX（ContactID）

      FROM Contacts AS C2

      WHERE C2.LastName = C1.LastName

      AND C2.FirstName = C1.FirstName

      AND C2 .DoB =

            （SELECT MAX（DoB）

  ;             FROM Contacts AS  C3

               WHERE C3.LastName = C1.LastName

               AND C3.FirstName = C1.FirstName））;
$


但是，在这种情况下，要保留的每个子集的行是最新（MAX）出生日期值。 如果有两个或多个联系人不仅具有相同的名称，而且具有相同的出生日期，那么要保留的行将被任意确定为
，具有最高的ContactID值。



我怀疑上述两个例子中的后者与你的目标更为相似。

You should be able to do this as a set operation without the need for any procedural code.  I'm not quite sure from your example exactly what is the basis for determining which rows to retain, but perhaps I can illustrate the principles with a couple of simple examples:

The following DELETE statement will delete all bar one of each subset of contacts with the same first and last names:

DELETE *
FROM Contacts AS C1
WHERE ContactID <>
     (SELECT MAX(ContactID)
      FROM Contacts AS C2
      WHERE C2.LastName = C1.LastName
      AND C2.FirstName = C1.FirstName);

In this case the row per subset to be retained is arbitrarily defined as that with the highest ContactID value, this being the table's autonumber primary key.

The following statement similarly retains one row per subset of contacts with the same first and last names:

DELETE *
FROM Contacts AS C1
WHERE ContactID <>
     (SELECT MAX(ContactID)
      FROM Contacts AS C2
      WHERE C2.LastName = C1.LastName
      AND C2.FirstName = C1.FirstName
      AND C2.DoB =
             (SELECT MAX(DoB)
              FROM Contacts AS  C3
              WHERE C3.LastName = C1.LastName
              AND C3.FirstName = C1.FirstName));

In this case, however, the row per subset to be retained is that with the latest (MAX) date of birth value.  If there are two or more contacts with not only the same names, but also the same date of birth, then the row to be retained is determined arbitrarily as that with the highest ContactID value.

I suspect the latter of the above two examples is more closely analogous to what you are aiming at.

这篇关于访问VBA以从访问表中删除已过滤的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！