accessで重複データの削除について

accessというよりSQLについてなのかと思いますが、教えていただけないでしょうか。


重複するIDがあり、1件を除いて他のデータを削除してデータを抽出したいです。

抽出条件としては、[年月日]カラムがあるため、日付が一番直近のものを残したいです。
　※こちらも重複しております。

また、[フィルタ]カラムが「2」のレコードのみを対象としたいと思っています。

accessの場合、重複クエリを用いるのかと思い、クエリウィザードから作成しましたが
一意とはなりませんでした。


SELECT テーブル１.[共通ID], テーブル１.[連番], テーブル１.[郵便番号], テーブル１.[住所], テーブル１.[担当者名], テーブル１.[年月日], テーブル１.[フィルタ]
FROM テーブル１
WHERE (((テーブル１.[共通ID]) In (SELECT [共通ID] FROM [テーブル１] As Tmp GROUP BY [共通ID] HAVING Count(*)>1 )) AND ((テーブル１.[フィルタ])=2))
ORDER BY テーブル１.[共通ID], テーブル１.[年月日] DESC;


distinctも使ってみましたがうまくできませんでした。
何が原因でしょうか。
申し訳ありませんがご教示いただきたくお願いいたします。

No.1 回答者： hatena1989 回答日時： 2014/06/23 13:46

重複クエリは、重複するレコードを抽出するもので、

今回は重複するレコードのうち最新以外は削除するということですので、
全然違いますね。

下記でどうでしょうか。

SELECT
テーブル１.[共通ID],
テーブル１.[連番],
テーブル１.[郵便番号],
テーブル１.[住所],
テーブル１.[担当者名],
テーブル１.[年月日],
テーブル１.[フィルタ]
FROM テーブル１
WHERE
テーブル１.[年月日] In
(SELECT TOP 1 年月日 FROM [テーブル１] As Tmp
WHERE Tmp.共通ID = テーブル１.共通ID And Tmp.[フィルタ]=2
OEDER BY 年月日 DESC)
ORDER BY テーブル１.[共通ID], テーブル１.[年月日] DESC;


または、

SELECT
テーブル１.[共通ID],
テーブル１.[連番],
テーブル１.[郵便番号],
テーブル１.[住所],
テーブル１.[担当者名],
テーブル１.[年月日],
テーブル１.[フィルタ]
FROM テーブル１ INNER JOIN
(SELECT 共通ID, Max(年月日) As Max年月日 FROM テーブル１
WHERE [フィルタ]=2
GROUP BY [共通ID]
) As Tmp
ON テーブル１.共通ID=Tmp.共通ID AND テーブル１.年月日=Tmp.Max年月日
ORDER BY テーブル１.[共通ID], テーブル１.[年月日] DESC;