質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

Q&A

2回答

3197閲覧

accessのクエリでdmaxで抽出した複数のレコードうち一つだけを抽出したい

ma2_ra

総合スコア16

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

0グッド

0クリップ

投稿2018/03/20 04:48

編集2022/01/12 10:55

前提・実現したいこと

下記のテーブルから、1会員につき、場所:bbで、日時が最新のレコード
というクエリ結果を得たいと考えています。
そこで、下記のように抽出条件を設定しました。

日時 DMax("日時","T1","会員番号='" & [会員番号] & "' And 場所='" & [場所] & "'")
場所 "bb"

ところがまれに
同会員で同場所、同日時のレコードが存在するようで、
「発生している問題・エラーメッセージ」のように、
二つのレコードが抽出されてしまいます。
名称はどうでもよく、どちらかのレコードが抽出できればよいのですが、
dlookupを組み合わせたらよいのでしょうか。

dmaxの記述もネット上を調べまくってなんとかこしらえたので、
どうネストしたらよいのかもわかりません。
正しい記述が知りたいです。

テーブル名:T1
名称|日時|場所|会員番号
AA|2017/12/31 23:22|aa|10552
BB|2017/12/21 17:40|bb|10552
CC|2017/12/22 11:16|bb|10552
DD|2017/11/16 8:21|bb|10451
AA|2017/11/16 8:21|bb|10451

クエリ―名:Q1
名称|日時|場所|会員番号
CC|2017/12/22 11:16|bb|10552
DD|2017/11/16 8:21|bb|10451

発生している問題・エラーメッセージ

名称|日時|場所|会員番号
CC|2017/12/22 11:16|bb|10552
DD|2017/11/16 8:21|bb|10451
AA|2017/11/16 8:21|bb|10451

該当のソースコード

補足情報(FW/ツールのバージョンなど)

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hatena19

2018/03/20 05:51

T1 テーブルに主キーはないのですか。
ma2_ra

2018/03/20 05:57

あります。元々のデータにはなかったのでインポート時にオートナンバーで作成しました
guest

回答2

0

オードナンバー型の主キーフィールドの名前を ID とすると、

IDフィールドの抽出条件に、下記の式でどうでしょう。

DLookup("ID","T1","日時 = #" & DMax("日時","T1","会員番号='" & [会員番号] & "' And 場所='" & [場所] & "'") & "#")

※コメントで、想定通りにならないとのことなので、提示のサンプルデータでの結果画像を貼っておきます。
![クエリ結果]
名称「CC」が最新の日付ですので、これが正しいと思います。

あるいは、サブクエリを使って、

SQL

1(SELECT TOP 1 ID FROM T1 AS T2 WHERE 場所='bb' AND T1.会員番号=T2.会員番号 ORDER BY 日時 DESC, ID)

追記

※レコードの特定とのコメントがあったので、下記の案はだめですね。

表示するフィールドが 名称, 日時, 場所, 会員番号 の4フィールドだけでいいのなら、
下記のSQLが高速かな。(D系関数や相関サブクエリは重いので)

SQL

1SELECT 2 First(T1.名称) AS 名称, T1.日時, T1.場所, T1.会員番号 3FROM 4 T1 INNER JOIN 5 (SELECT Max(日時) AS 最新日時, 会員番号, 場所 6 FROM T1 7 WHERE T1.場所="bb" 8 GROUP BY 会員番号, 場所 9 ) AS a 10 ON T1.会員番号 = a.会員番号 AND T1.日時 = a.最新日時 AND T1.場所 = a.場所 11GROUP BY 12 T1.日時, T1.場所, T1.会員番号;

上記が理解できないなら、下記の2つのクエリと同じ意味になります。

クエリ―名:a

SQL

1SELECT Max(日時) AS 最新日時, 会員番号, 場所 2FROM T1 3WHERE T1.場所="bb" 4GROUP BY 会員番号, 場所;

SQL

1SELECT First(T1.名称) AS 名称の先頭, T1.日時, T1.場所, T1.会員番号 2FROM 3 T1 INNER JOIN a 4 ON T1.会員番号 = a.会員番号 AND T1.日時 = a.最新日時 AND T1.場所 = a.場所 5GROUP BY T1.日時, T1.場所, T1.会員番号;

上記の2つをデザインビューで見れば、何をしているか理解しやすいでしょう。

投稿2018/03/20 06:19

編集2018/03/22 17:04
hatena19

総合スコア33620

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ma2_ra

2018/03/20 09:46

ありがとうございます。 IDフィールドにDLookupの記述をしてみましたが、実際には元のテーブルで2万弱のレコードがあり、4500ほどに絞られるはずですが、結果130程度になってしまいます。 SQLの記述では、パラメータの入力を求められてしまいます。
hatena19

2018/03/20 10:25

質問のサンプルデータで実験すると、想定どおりの結果がえられます。 実際に記述したSQLと、想定と異なる結果になるサンプルデータを提示できますか。
ma2_ra

2018/03/22 02:22

ごめんなさい。 実際の記述は、できませんが、 提示のものをままコピペし、実際のデータのフィールド名にしました。 ただ、日時は日付形式ではインポートできなかったためテキスト形式になっており、♯を'に変更しました。
hatena19

2018/03/22 04:36

では、質問で提示のサンプルデータで、私の回答の条件を試してみてください。 それでうまくいくなら、あとは、ma2_raさんの方で違いを探してもらうしかないですね。 うまくいかないなら、サンプルでのSQLを提示してください。
ma2_ra

2018/03/22 09:08

ありがとうございます。 サンプルのデータで試してみましたが、求めている結果になりませんでした。 追記のSQL記述でも、「指定されたフィールド'場所'がSQLステートメントのFROM句にある複数のテーブルを参照しました」と出、うまくいきませんでした。 私の知識で理解できませんし、さらにわからない方に渡し、その方が利用するので、ちょっと無理ポイです。
hatena19

2018/03/22 12:10

追記のSQL記述に間違いがありましたので、修正しておきます。修正後のSQLで確認ください。 それと、DLookupの条件の結果画像も張っておきました。この結果で間違いないはずです。
ma2_ra

2018/03/22 13:36

失礼しました。 ご指摘の通り、求めたい結果の例示が間違っておりました、修正しました。 いずれの方法でもサンプルではうまくできました。 本番データでトライしてみます。
hatena19

2018/03/22 16:45

あと、追記のSQLは提示の4フィールドのみ出力の場合です。それ以外のフィールドも必用(レコードの特定)なら、前の2つの方法で。
ma2_ra

2018/03/23 06:11

ありがとうございます。本番データでは今ひとつ。。。 本当は、場所は二つに絞り込みたく、orでつなぎましたがうまくいきませんでした。 SQLはサッパリ?なので、修正が発生したら手がでないので、あきらめます。 私には荷が重く、クエリを繰り返して何とこぎつけます。 ありがとうございました。
hatena19

2018/03/23 07:13

4フィールドのみ出力でいいのか、それ以外のフィールドも必用なのか、どちらでしょう。まずは、それを、はっきりさせてください。それによって方法はことなりますので。 クエリをつないでやるとしても、何をしたいのかが明確でないと行き詰まりますよ。 4フィールドでいいのなら、追記の方に2つのクエリに分割したものも提示してあるので、それをデザインビューでみて、意味を考えてみてください。
guest

0

どちらかを選択する条件がないので、集約するしかないですね。

なので、「結果をさらに、日時、場所、会員番号でグルーピングして、各項目は最大を表示。」
または、「オートナンバーを追加して、その最大をを選択」

投稿2018/03/20 05:34

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ma2_ra

2018/03/20 10:29

ありがとうございます。 前述は「集計」機能のことですよね? Q1で、日時/最大、場所/グループ化(bb抽出)、会員番号/グループ化、名称/最大でもいいのでしょうか? 後述の方は他の方にも提案いただいたのですが、どうもうまくいかないようです。
sazi

2018/03/20 15:21

そうですね。ユニークにしたいものをグループ化してください。
ma2_ra

2018/03/22 03:36

ありがとうございます。 試したのですが、同クエリ内で日時/最大と名称/最大があると、 それぞれの最大が抽出されてしまい、 元のレコードとは異なるレコードができてしまうようです。
sazi

2018/03/22 04:19

一旦集約した後をさらに集約です。 但し、質問にある「名称はどうでもよく、どちらかのレコードが抽出できればよい」が前提ですが。
ma2_ra

2018/03/22 07:18

ありがとうございます。 やはり、2段階の集約ですかね。。。 名称はどちらでもよいというか、たまたま同一なんです。 想定ではありえないはずが、なぜか2万のレコード中に1件(2レコード)だけ重複が存在しました。 このケースであれば、1回だけの集約で 日時/最大、場所/グループ化(bb抽出)、会員番号/グループ化、名称/グループ化 で可能ですよね。
sazi

2018/03/22 07:43

>このケースであれば、1回だけの集約で 日時/最大、場所/グループ化(bb抽出)、会員番号/グループ化、名称/グループ化 で可能ですよね。 1回だけだと、その指定では、求めたい2件だけにはならないですけど。
ma2_ra

2018/03/22 08:24

求めたいのは重複する2件ではないです、、、。 名称、日時(直近)、場所(=bb)、会員番号のユニークデータです。
sazi

2018/03/22 08:26

その条件では、質問にある「クエリ―名:Q1」の求めたい結果にならないということです。
ma2_ra

2018/03/22 13:24

失礼しました。ご指摘のとおりのようです。 クエリを増やすのが現実的なようです。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問