按日期标准删除相同字段的记录re：操作方法？ [英] Delete records of same fields by date criteria re: How-to?
问题描述
大家好，
我收到了一个问题。希望有人能提供帮助。我在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
推荐答案
试试这个...
Try this ...
感谢Mary，但我仍然得到无效的SQL语句;预期''DELETE''，''INSERT''，''PROCEDURE''，''SELECT''或''UPDATE''。"
这是什么意思？任何的想法？ ：-\
Thanks Mary, but I''m still getting the "Invalid SQL statement; expected ''DELETE'', ''INSERT'', ''PROCEDURE'', ''SELECT'', or ''UPDATE''."
What does that mean? Any idea? :-\
感谢Mary，但我仍然得到无效的SQL语句;预期''DELETE''，''INSERT''，''PROCEDURE''，''SELECT''或''UPDATE''。"
这是什么意思？任何的想法？ ： -
Thanks Mary, but I''m still getting the "Invalid SQL statement; expected ''DELETE'', ''INSERT'', ''PROCEDURE'', ''SELECT'', or ''UPDATE''."
What does that mean? Any idea? :-
发生错误的代码行是什么？
你有一个链接到数据库的后端吗？
What line of code is the error occuring on?
Do you have a linked backend to the database?
这篇关于按日期标准删除相同字段的记录re：操作方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
