Access2003を使って、複数台ある貸出機を製造番号毎にまとめ最新のレコードだけを一覧表に表示させるには？？？

質問

顧客所有の機械に不具合が生じた場合、その修理期間に顧客へ貸出す代替機の履歴をアクセス(Access2003)を使って管理しています。

ところが、次のような時はどのような方法を取ればよいのかわからなくて煮詰まっています。
よいアドバイスをいただきたいと思い投稿をさせていただきます。

次から代替機のことは貸出機と記します。

貸出機は２０台あります。
その２０台を複数のユーザーに貸し出しています。
台によっては５人に貸している機械もあれば、まったく貸出したことがない機械もあります。
それを貸出機の製造番号ごとにグループにまとめて、
そのうちの貸出日がもっとも新しいものを表示させたいのです。
レコード１件につき入力順によって貸出履歴IDを振っているので、
そのIDがグループの中で最新のものでもいいのです。
要は、製造番号ごとに貸出中なのか待機中なのか一目でわかればいいのです。

集計クエリで「貸出機製造番号」をグループ化にして、他の項目は先頭を使ってみましたが、この先頭と最後という集計方法はHDに記録した順番で変動してしまうので正しい情報を得ることが出来ませんでした。

関数やVBAなどを使ったら、解決されないでしょうか？

テーブルの構造は次のようになっています。
仮にテーブルAとします。
[フィールド名]・・・・・[データ型]
貸出機管理ID・・・・・・オートナンバー
ユーザNO・・・・・・・・・・テキスト型
貸出機製造番号・・・・・テキスト型
故障機製造番号・・・・・テキスト型
貸出日・・・・・・・・・・・・日付／時刻型
故障機受領日・・・・・・・日付／時刻型
修理発送日・・・・・・・・日付／時刻型
修理返却日・・・・・・・・日付／時刻型
故障機発送日・・・・・・・日付／時刻型
返却日・・・・・・・・・・・・日付／時刻型
担当者ID・・・・・・・・・・テキスト型
備考・・・・・・・・・・・・・・メモ型

上記のテーブルAに別のテーブルBからユーザ名を関連付けてクエリを作りたいです。
また、貸出日に日付があり返却日に日付がなければ"貸出中"、
貸出日と返却日の両方に日付が入っている、もしくは、まったく入ってなければ"待機中”という[現状]の表示をするフィールドをそのクエリ上に設けたいです。

そして、そのクエリを使って[貸出中]と[待機中]の一覧表が欲しいです。

なお、貸出機の製造番号は次のような形式になっています。
９９ＸＶＣ
００ＸＶＡ
０１ＸＶＢ
製造年の西暦の下２桁＋アルファベット３桁の組み合わせになっています。
１９００年代から２０００年代のものが混在しており、昇順に並べ替えたいので、並べ替える式のフィールドも設けています。

<クエリ>
フィールド名・・・・・・・・・テーブル
貸出機管理ID・・・・・・・・・テーブルA・・・　
ユーザNO・・・・・・・・・テーブルA
ユーザ名・・・・・・テーブルB
貸出機製造番号・・・・・・・・・テーブルA→製造番号毎にグループにまとめる
故障機製造番号・・・・・・・・・テーブルA
貸出日・・・・・・・・・テーブルA
故障機受領日・・・・・・・・・テーブルA
修理発送日・・・・・・・・・テーブルA
修理返却日・・・・・・・・・テーブルA
故障機発送日・・・・・・・・・テーブルA
返却日・・・・・・・・・テーブルA
担当者ID・・・・・・・・・テーブルA
備考・・・・・・・・・テーブルA
機械の現状・・・フィールドに条件式を入れる
貸出機製造番号を並替えるためのフィールド・・・並べ替えの条件式を入れる

このような構造なのですが、皆様だったらどのような方法を取られますか？

製造番号毎に最新のレコードだけを表示させる所以外は問題なく解決されています。

この内容と似た質問を過去に何度か投稿させていただいています。
数名の方からいくつかの方法を教えていただき、試しているところですが、解決に至らず、より詳しい情報を載せて質問を再投稿させていただきます。
前の質問を締め切らない状態で、再投稿することは、お答えくださった回答者の方には大変、失礼なことをしており申し訳なく思います。
この場をお借りしてお詫びさせていただきます。

回答No.5

noname#60992 · ベストアンサー

>元のテーブルにはそれらの製造番号はあります。
これを製造番号でグループ化したもの（すべての貸出機の製造番号をリストにしたもの）をテーブルＢとします。（別にクエリＢでもかまいません）そして２番目のクエリを以下のように作り変えてください。

SELECT テーブルＢ.貸出機製造番号, テーブルＡ.貸出機管理ＩＤ, テーブルＡ.貸出日, テーブルＡ.返却日
FROM テーブルＢ LEFT JOIN (クエリＡ LEFT JOIN テーブルＡ ON クエリＡ.貸出機管理ＩＤの最大 = テーブルＡ.貸出機管理ＩＤ) ON テーブルＢ.貸出機製造番号 = クエリＡ.貸出機製造番号;

一回も貸し出したことがないものは貸出機管理ＩＤ以下がブランクで表示されるはずです。　

テーブルのリレーションシップが理解できれば簡単な問題ですので、そのあたりを勉強されると良いと思います。

お礼

やっほー、できました!!
ようやく望みのものが出来ました。
作成手順、そしてテーブルのリレーションシップとクエリの結合の種類を理解していれば解決できた問題だったんですね。
２ヶ月近くも悩みつづけていたことから解き放されて、と～ってもうれしいです☆
これでようやくじっくりと他のことも勉強することができます。私にはまだまだ基本を学ぶことが大切なようなので、しっかり身に付けたいと思います。
何度も教えていただいて、ありがとうございました。
本当に助かりました。
今後ともよろしくお願いいたします！

回答No.6

CHRONOS_0

>貸出機テーブルを作って、次にどのような作業を行えばいいと思われますか？

待機中を抽出するには貸出機テーブルから作ったクエリの貸出機製造番号の抽出条件欄に

Not In (select 貸出機製造番号 from テーブルA where 返却日 is null)

お礼

待機中のみを抽出するときは、教えていただいた式を使ってみます。
ありがとうございました。

回答No.4

noname#140971

[イミディエイト]
? XferKNumber("99AAA")
1999AAA
? XferKNumber("00BBB")
2000BBB
? XferKNumber("01CCC")
2001CCC

Public Function XferKNumber(ByVal KNumber As String) As String
　　Dim S As String

　　If Left(KNumber, 2) < "20" Then
　　　　S = "20"
　　Else
　　　　S = "19"
　　End If
　　XferKNumber = S & KNumber
End Function

機械の並び替え部分はミス！よって訂正！

お礼

度重なる投稿にも関わらず、ご親切にたくさんのアドバイスをいただいて、ありがとうございました。

回答No.3

noname#60992

前回とは別案の回答（質問が具体化されたので）

貸出機製造番号でグループ化し、貸出機管理ＩＤの最大値を選ぶクエリを作ります。
（sqlは以下の通りです。　クエリの表示でＳＱＬビューにして確認してください。）　
このクエリを　クエリＡとします。

SELECT Max(テーブルＡ.貸出機管理ＩＤ) AS 貸出機管理ＩＤの最大, テーブルＡ.貸出機製造番号
FROM テーブルＡ
GROUP BY テーブルＡ.貸出機製造番号;

このクエリとテーブルＡを連結（left join)させたクエリを作ります。
sqlは以下の通りです。　出力フィールドや、ユーザー名等はご自分で調整なさってください。

SELECT テーブルＡ.貸出機管理ＩＤ, テーブルＡ.貸出機製造番号, テーブルＡ.貸出日, テーブルＡ.返却日
FROM クエリＡ LEFT JOIN テーブルＡ ON クエリＡ.貸出機管理ＩＤの最大 = テーブルＡ.貸出機管理ＩＤ;

これでご希望のデータが取得できるはずです。
Ｎｏ２様のご指摘部分はその通りです。　テーブルに入っていないデータを抽出することはできません。
もうひとつ貸出機テーブルを作成する必要があります。

お礼

フィールドの項目をたくさん指定して集計クエリを使っていたのが悪かったようです。教えていただいたように、必要な項目に絞り込んで、それをベースに数段階にわけてクエリを作ればよかったんですね。
手順が大切なこともわかりました。
ありがとうございました！

補足

ご回答ありがとうございます。
貸出機管理IDの最大値のみを抽出することが出来ました。
けれど、私が欲しいのやはり貸出を行ったことのない機械のレコードも含めてなのです。
元のテーブルにはそれらの製造番号はあります。
貸出機テーブルを作って、次にどのような作業を行えばいいと思われますか？
ご負担じゃなけば、もう少し教えてください。

回答No.2

CHRONOS_0

仕様に矛盾があります

貸し出し簿から抽出しても
>まったく貸出したことがない機械もあります。
これは抽出できませんよ

貸し出し簿から分かるのは貸し出し中か貸し出したことがあるという情報だけです

>製造番号ごとに貸出中なのか待機中なのか一目でわかればいいのです。
これをやるには全貸出機のテーブルが必要です

その中から貸し出し中以外を抽出してやれば
待機中の一覧が得られますね

貸出機テーブルから作ったクエリの製造番号の抽出条件欄に

In (select 貸出機製造番号 from テーブルA where 返却日 is null)

回答No.1

noname#140971

<tab1>
貸出機管理ID__ユーザーNO___貸出機製造番号___貸出日__________返却日
1__________________U101_____________９９ＸＶＣ_____________2007/08/10___2007/08/20
2__________________U102_____________９９ＸＶＣ_____________2007/08/20
3__________________U103_____________０１ＸＶＢ_____________2007/08/22
4__________________U104_____________０１ＸＶＢ_____________2007/08/28
5__________________U104_____________０１ＸＶＢ_____________

<クエリ1>
SELECT 貸出機管理ID, ユーザーNO, 貸出機製造番号, 貸出機製造番号, 貸出日, 返却日, (Len([返却日] & "")>0 Or Len([貸出日] & "")=0) AS 待機中, format(100+Left(貸出機製造番号,2)*-1,"00") & Right(貸出機製造番号,3) AS 貸出機製造番号並び順
FROM tab1;

貸出機管理ID__ユーザーNO___貸出機製造番号___貸出日__________返却日__________待機中__貸出機製造番号並び順
1__________________U101_____________９９ＸＶＣ_____________2007/08/10___2007/08/20___-1________01XVC
2__________________U102_____________９９ＸＶＣ_____________2007/08/20______________________0__________01XVC
3__________________U103_____________０１ＸＶＢ_____________2007/08/22______________________0__________99XVB
4__________________U104_____________０１ＸＶＢ_____________2007/08/28______________________0__________99XVB
5__________________U104_____________０１ＸＶＢ___________________________________________________-1________99XVB

相当に簡略化していますが、こういうことでしょうか？
[機械の現状]は、[待機中]でYes/Noにしています。
別に、'貸出中'、'待機中'と翻訳するのは手の問題だからよいでしょう。

さて、「製造番号毎に最新のレコードだけを表示させる」の意味が判りません。
並び順はさておいて、通常は、[待機中]が偽='貸出中'は全て表示する筈ですよね。

SELECT *
FROM クエリ1
WHERE 待機中=False;

貸出機管理ID__ユーザーNO___貸出機製造番号___貸出日__________返却日__________待機中__貸出機製造番号並び順
2__________________U102_____________９９ＸＶＣ_____________2007/08/20______________________0__________01XVC
3__________________U103_____________０１ＸＶＢ_____________2007/08/22______________________0__________99XVB
4__________________U104_____________０１ＸＶＢ_____________2007/08/28______________________0__________99XVB

どこが、どのように判らんのでしょうか？