按日期标准删除相同字段的记录re：操作方法？

问题描述

大家好，


我收到了一个问题。希望有人能提供帮助。我在XP上使用Access 97.


我有一个包含3个字段的表：EmployeeID，ModuleID和TrainedDate。


我创建了查询（qryFindDuplicatesTrainingFortblRECORDS）以查找具有不同TrainedDate的此类表（tblRECORDS）中的重复字段（EmployeeID和ModuleID）。它的SQL是：


SELECT DISTINCTROW tblRECORDS.EmployeeID，tblRECORDS.ModuleID，tblRECORDS.TrainedDate

FROM tblRECORDS

WHERE（（ （tblRECORDS.EmployeeID）In（SELECT [EmployeeID] FROM [tblRECORDS] As Tmp GROUP BY [EmployeeID]，[ModuleID] HAVING Count（*）> 1和[ModuleID] = [tblRECORDS]。[ModuleID]）））

ORDER BY tblRECORDS.EmployeeID，tblRECORDS.ModuleID;


运行查询后，我想要删除所有具有旧版TrainDate的记录只保留结果中的最新记录。我现在每天都手动完成。

:-(


我从某处复制了以下代码并试过但是Access说无效的SQL语句;预计''DELETE''，''INSERT''，''PROCEDURE''，''SELECT''或''UPDATE''。"：


Dim db As DAO .Database

Dim qdf作为QueryDef

Dim strSQL As String

Dim dteDate As Date


dteDate = DMax（" [TrainedDate]"，" qryFindDuplicatesTrainingFortblRECORDS"）

strSQL =" Delete * FROM qryFindDuplicatesTrainingFortblRECORDS"& _

" WHERE（qryFindDuplicatesTrainingFortblRECORDS.TrainedDat e）<＃"& dteDate&"＃"


设置db = CurrentDb

使用db

设置qdf = .CreateQueryDef（" tmpQuery"，strSQL）

DoCmd.OpenQuery" tmpQuery"

.QueryDefs.Delete" tmpQuery"

结束Wi th $>
db.Close

qdf.Close


有没有什么方法可以让我的日常例程自动删除旧版本记录本身？


您的意见非常感谢。非常感谢你！


ClumsyGalLA

Hello guys,

I''ve gotten a question. Hope someone can help. I''m using Access 97 on XP.

I have a table of 3 fields: EmployeeID, ModuleID and TrainedDate.

I created a query (qryFindDuplicatesTrainingFortblRECORDS) to find duplicated fields (EmployeeID and ModuleID) in such table (tblRECORDS) with different TrainedDate. Its SQL is:

SELECT DISTINCTROW tblRECORDS.EmployeeID, tblRECORDS.ModuleID, tblRECORDS.TrainedDate
FROM tblRECORDS
WHERE (((tblRECORDS.EmployeeID) In (SELECT [EmployeeID] FROM [tblRECORDS] As Tmp GROUP BY [EmployeeID],[ModuleID] HAVING Count(*)>1 And [ModuleID] = [tblRECORDS].[ModuleID])))
ORDER BY tblRECORDS.EmployeeID, tblRECORDS.ModuleID;

After running the Query, what I want to do is to delete all records that have the older TrainDate and only keep the newest record in the result. I am doing it manually now everyday.
:-(

I copied the following code from somewhere and tried but Access said "Invalid SQL statement; expected ''DELETE'', ''INSERT'', ''PROCEDURE'', ''SELECT'', or ''UPDATE''.":

Dim db As DAO.Database
Dim qdf As QueryDef
Dim strSQL As String
Dim dteDate As Date

dteDate = DMax("[TrainedDate]", "qryFindDuplicatesTrainingFortblRECORDS")

strSQL = "Delete * FROM qryFindDuplicatesTrainingFortblRECORDS " & _
"WHERE (qryFindDuplicatesTrainingFortblRECORDS.TrainedDat e)< #" & dteDate & "#"

Set db = CurrentDb
With db
Set qdf = .CreateQueryDef("tmpQuery", strSQL)
DoCmd.OpenQuery "tmpQuery"
.QueryDefs.Delete "tmpQuery"
End With
db.Close
qdf.Close

Is there any way a query can automate my daily routine to delete the older records by itself?

Your input is highly appreciated. Thank you very much!

ClumsyGalLA

推荐答案

展开 | 选择 | Wrap | 行号

发生错误的代码行是什么？


你有一个链接到数据库的后端吗？

What line of code is the error occuring on?

Do you have a linked backend to the database?

这篇关于按日期标准删除相同字段的记录re：操作方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！