Accessでグループ化した中から最新の登録IDのみを抽出して表示するには？

質問

Access2003を使用しています。
集計クエリでグループ化して、グループのうちの最新のＩＤのデータ全てを抽出したいのですが、うまくできず大変困っています。
前任者が作成したものを修正していますが、どのような設定で作られているのか把握するだけでも大変なレベルです。
行いたいことは、貸出状況を製造番号別に表示させたいのです。
テーブルには履歴全部が残っています。
フォームで貸出中のみを表示させて、その製造番号のボタンをクリックするとその製造番号が持つデータすべてが見れるようになっています。
フィールドは約１０項目あります。
・「製造番号」フィールドをグループ化
・他のフィールドは「先頭」
ところが「先頭」「最大」というのはタイミングでターゲットになるデータがかわってしまうようで、正しく抽出してくれません。
「最小」「最大」でもうまくできません。
Accessに詳しい方ならこのような場合、どのような方法をとられますか？
仕組み自体が大幅に変わる方法でも構いませんので、どなたか良いアドバイスをご教示いただけないでしょうか？
以前にも同じ質問をさせていただいておりますが、解決に至らなくて他の方からのアドバイスもいただきたいと思い再び投稿させていただきます。

回答No.6

noname#60992

代替機の貸出ステータス及び貸し出し履歴を閲覧したいのか、もしくは故障機のステータス管理をしたいのかというのがまだわかりませんが、たぶん、故障機のステータス管理をということだと思いますので違っていたら補足をしてください。

ベストな方法ではありませんが、考えやすい方法として２段階クエリというのはありだと思います。　ただし、２段階クエリのＳＱＬ文を直接見れませんので、順番がどのようになるのかは私にもはっきりわかりません。　よって、最初のクエリをいったんテーブルに書き出すとうまくいく場合があります。（このあたりがベストな方法でない理由です。）　
最初のクエリで行うべきことは、表示させたいものを選び出し、製造番号順に並べ、それを受付日順に並べ仮のテーブルに書き出してください。(全機器を見たいならWHERE句は要りません）（降順、昇順というのは抽出されたレコードを並び替えるものです。　並び替えた後抽出するものではありません。）

SELECT * FROM T_REPAIRRECORD INTO T_ORDEDRECORD ORDER BY [故障製造番号],[受付日] DESC;
これを行えばあとは故障製造番号でグループ化をして、最初のレコードを見るようにしておけばよいわけです。（一番目のクエリをレコードソースとしてグループ化を行えばよいわけです。）
SELECT FIRST(貸出し製造番号),故障製造番号,FIRST(ユーザー名),FIRST(受付日),FIRST(貸出日),FIRST(故障機到着日),FIRST(修理完成日),FIRST(修理機発送日),FIRST(備考) FROM T_ODRDRECORD GROUP BY 故障製造番号;

まあ、インデックスを受付登録日時などにしておくと順番がうまいこと保持されることもありますが、ＤＢによって動作が違ったりする可能性もありますので、いろいろ試してみるのが良いと思います。　注意事項としては、テーブルを一日に何回も作り直したりするとレコード数によってはデータベースがどんどん大きくなりますので、時々最適化をする必要があります。

思いつくこととしては、このテーブルに返却日がないことかな。　あと、場合によっては完了フラグみたいなものを作っておけば、完了していないものを一度に選び出すなど処理は簡単になると思うのですが、現状をはっきり把握していない素人の意見として聞いておいてください。

回答No.5

noname#60992

>行いたいことは、貸出状況を製造番号別に表示させたいのです。

状況と質問がはっきりとわからないのですが、
状況
1.ひとつ以上の種類の製品を扱っていて
2.ひとつの製品種別あたり固有の製造番号を持つ複数の製品を保有していて、
3.ひとつの製品を複数回数貸し出す。

現在できていること
貸出中の製品をリストとして表示すること。

行いたいこと
1.リストより製品を選んだ際にその製品の貸出履歴を履歴の新しい順に表示させたいということであれば、特にグループ化は必要ないと思います。
SELECT * FROM T_RentalHistory WHERE ID = LIST.TEXT ORDER BY Rental_Date;
のようなクエリを別なリストのレコードソースとすればよいのではないでしょうか？

2.すべての製品の最新の貸出履歴のみを表示させたいのであれば、
SELECT ID,Name,Max(Rental_Date) AS 最新貸出日 FROM T_RentalHistory Group by ID, Name;
のような感じでできると思います。（最大を選んでいます）

いまいち質問を理解し切れていないので的外れな回答でしたらご容赦ください。　テーブルのデータ構造をある程度示して、何を表示させたいか詳しく補足いただけたらピンポイントな回答も出てくると思います。

補足

内容足らずな質問ですみません。

行いたいことは、製造番号別にまとめて、その製造番号の最新の履歴だけを表示させて貸出しの状況を把握したいのです。
データは１ユーザー１件のIDがふられます。

テーブルは、
ID・・・数値型
貸出し製造番号・・・テキスト型
故障製造番号・・・テキスト型
ユーザー名・・・テキスト型
受付日・・・日付型
貸出日・・・日付型
故障機到着日・・・日付型
修理完成日・・・日付型
修理機発送日・・・日付型
備考・・・メモ型

クエリは２段階の構想になっているます。
２段階のクエリを集計クエリを使って「故障製造番号」をグループ化にしています。
他のフィールドは「先頭」です。
そして新たに次の２フィールドを加えてあります。
●貸出し状況・・・IIF関数で状況を切り分けています。
●演算式・・・製造番号を並べ替える為の式が入っています。

以上のようになりますが、前よりは質問内容がわかりやすくなったでしょうか？

お手間をおかけして申し訳ありませんが、よろしくお願いします。

回答No.4

noname#140971

補足:　設計は現場、データ量の反映！

ID___有効__相手先___管理番号______製造番号___貸出日_________返却日_________備考
1____No_____鈴木様__2007080001___A-101_______2007/08/01__2007/08/03
2____Yes____中村様__2007080002___A-101_______2007/08/02
3____Yes____鈴木様__2007080003___A-101_______2007/08/03
4____Yes____田中様__2007080004___A-102_______2007/08/04

テーブル設計は、実に、現場作業の反映。
伝票処理システムとして構想すべきか？
はたまた、貸出帳簿管理システムとして構想すべきか？
これは、どちらが正解ということはないでしょう。
現場のニーズ次第です。
ですから、[管理番号]というアイデア以外は無視して下さい。
要は、ちゃんと、管理する番号を通しで振ったが良いということです。
ただし、年間の通し番号にすると、先月の漏れの追加等が困難になります。
日々の通し番号にすべきか否かは、データ量次第です。

回答No.3

noname#140971

<貸出返却履歴>
ID______________LONG________主キー
区分___________BYTE________1;貸出;2;返却
相手先________LONG________1
管理番号_____LONG________2007080001
製造番号_____LONG________1
年月日________DATE_________2007/08/10
備考___________CHAR(32)___追加分

ID___区分____相手先____管理番号_____製造番号____発生日_________備考
1____貸出_____鈴木様___2007080001__A-101_______2007/08/01
2____貸出_____中村様___2007080002__A-101_______2007/08/02
3____返却_____鈴木様___2007080001__A-101_______2007/08/03
4____貸出_____田中様___2007080003__A-102_______2007/08/04

まず、テーブル設計が明らかでないと誰も回答できんでしょう。

上述のテーブルを見ると、貸出返却は、一つのテーブルで管理されていることが判ります。
これは、いずれにしろ、貸出と返却とにテーブルを分けても[区分]でどうとでもなることを意味しています。

次に、貸出しと返却は、[製造番号]ではなく[管理番号]で管理されていることが判ります。
[製造番号]+[相手先]の組合せでも、不可能ではありません。
しかし、「どれが・・・」が問題にならないとも限りません。
また、かかる[管理番号]を付与して、これでもって発生順番を決める以外の妙手はないからです。
[ID]だって、完全に発生順位を特定するもんではありません。
もちろん、[発生日]+[ID]の組合せも発生順位を特定するもんではありません。

[管理番号]を yyyymmnnnn にしておけば、参照範囲も絞りやすいです。

Q、Accessでグループ化した中から最新の登録IDのみを抽出して表示するには？
A、この問い自体が設計の不味さを露呈しております。

回答No.2

kurodai2

>集計クエリでグループ化して、グループのうちの最新のＩＤのデータ全てを抽出したいのですが、うまくできず大変困っています。
>フィールドは約１０項目あります。
>・「製造番号」フィールドをグループ化
>・他のフィールドは「先頭」

製造番号でグループ化　他の項目は先頭（グループ化の必要は？）
最新のＩＤのすべてを抽出したい・・（最新の履歴？）

グループ化して、取り出そうとしていると言う説明が何度読み返しても、よくわかりませんでした。
もしかして、製造番号グループ化して取り出すのではなく、製造番号ごとの、最新の履歴を取り出したい。
が、目的ですかね？

それが目的だとすると
各製造番号毎に、最新のID（最大値のIDと判断します）を取り出す。

SELECT テーブル1.製造番号, Max(テーブル1.ID) AS 最新ID FROM テーブル1
GROUP BY テーブル1.製造番号

これで、製造番号ごとの最新のIDが識別できるので、履歴から一致するデータを取り出す。
となります。

これを、一括でクエリーを作成する場合の　SQL文を参考までに書きます。
select x.* from テーブル1 as x inner join (SELECT テーブル1.製造番号, Max(テーブル1.ID) AS 最新ID FROM テーブル1
GROUP BY テーブル1.製造番号) as t on (x.製造番号 = t.製造番号 and x.ID = t.最新ID)

（目的が違うようでしたら、読み捨ててください）

お礼

段階別に作成したクエリをLEFT JOINさせることによって望みのものを作ることができました。
ご回答いただきまして、ありがとうございました。

回答No.1

CHRONOS_0

>グループのうちの最新のＩＤのデータ全てを抽出したいのですが
こういう場合はサブクエリを使うかDminなどの関数を使った抽出条件を書きます

テーブルの情報が全くないのでどのようなものを取り出したいのか分かりませんが
製造番号と日付フィールドがあり製造番号ごとの最新レコードを取り出したいというのであれば

日付の抽出条件欄に

In (select max(日付) from テーブル名 as A where A.製造番号=テーブル名.製造番号)