顧客所有の機械に不具合が生じた場合、その修理期間に顧客へ貸出す代替機の履歴をアクセス(Access2003)を使って管理しています。
ところが、次のような時はどのような方法を取ればよいのかわからなくて煮詰まっています。
よいアドバイスをいただきたいと思い投稿をさせていただきます。
次から代替機のことは貸出機と記します。
貸出機は20台あります。
その20台を複数のユーザーに貸し出しています。
台によっては5人に貸している機械もあれば、まったく貸出したことがない機械もあります。
それを貸出機の製造番号ごとにグループにまとめて、
そのうちの貸出日がもっとも新しいものを表示させたいのです。
レコード1件につき入力順によって貸出履歴IDを振っているので、
そのIDがグループの中で最新のものでもいいのです。
要は、製造番号ごとに貸出中なのか待機中なのか一目でわかればいいのです。
集計クエリで「貸出機製造番号」をグループ化にして、他の項目は先頭を使ってみましたが、この先頭と最後という集計方法はHDに記録した順番で変動してしまうので正しい情報を得ることが出来ませんでした。
関数やVBAなどを使ったら、解決されないでしょうか?
テーブルの構造は次のようになっています。
仮にテーブルAとします。
[フィールド名]・・・・・[データ型]
貸出機管理ID・・・・・・オートナンバー
ユーザNO・・・・・・・・・・テキスト型
貸出機製造番号・・・・・テキスト型
故障機製造番号・・・・・テキスト型
貸出日・・・・・・・・・・・・日付/時刻型
故障機受領日・・・・・・・日付/時刻型
修理発送日・・・・・・・・日付/時刻型
修理返却日・・・・・・・・日付/時刻型
故障機発送日・・・・・・・日付/時刻型
返却日・・・・・・・・・・・・日付/時刻型
担当者ID・・・・・・・・・・テキスト型
備考・・・・・・・・・・・・・・メモ型
上記のテーブルAに別のテーブルBからユーザ名を関連付けてクエリを作りたいです。
また、貸出日に日付があり返却日に日付がなければ"貸出中"、
貸出日と返却日の両方に日付が入っている、もしくは、まったく入ってなければ"待機中”という[現状]の表示をするフィールドをそのクエリ上に設けたいです。
そして、そのクエリを使って[貸出中]と[待機中]の一覧表が欲しいです。
なお、貸出機の製造番号は次のような形式になっています。
99XVC
00XVA
01XVB
製造年の西暦の下2桁+アルファベット3桁の組み合わせになっています。
1900年代から2000年代のものが混在しており、昇順に並べ替えたいので、並べ替える式のフィールドも設けています。
<クエリ>
フィールド名・・・・・・・・・テーブル
貸出機管理ID・・・・・・・・・テーブルA・・・
ユーザNO・・・・・・・・・テーブルA
ユーザ名・・・・・・テーブルB
貸出機製造番号・・・・・・・・・テーブルA→製造番号毎にグループにまとめる
故障機製造番号・・・・・・・・・テーブルA
貸出日・・・・・・・・・テーブルA
故障機受領日・・・・・・・・・テーブルA
修理発送日・・・・・・・・・テーブルA
修理返却日・・・・・・・・・テーブルA
故障機発送日・・・・・・・・・テーブルA
返却日・・・・・・・・・テーブルA
担当者ID・・・・・・・・・テーブルA
備考・・・・・・・・・テーブルA
機械の現状・・・フィールドに条件式を入れる
貸出機製造番号を並替えるためのフィールド・・・並べ替えの条件式を入れる
このような構造なのですが、皆様だったらどのような方法を取られますか?
製造番号毎に最新のレコードだけを表示させる所以外は問題なく解決されています。
この内容と似た質問を過去に何度か投稿させていただいています。
数名の方からいくつかの方法を教えていただき、試しているところですが、解決に至らず、より詳しい情報を載せて質問を再投稿させていただきます。
前の質問を締め切らない状態で、再投稿することは、お答えくださった回答者の方には大変、失礼なことをしており申し訳なく思います。
この場をお借りしてお詫びさせていただきます。
No.1
- 回答日時:
<tab1>
貸出機管理ID__ユーザーNO___貸出機製造番号___貸出日__________返却日
1__________________U101_____________99XVC_____________2007/08/10___2007/08/20
2__________________U102_____________99XVC_____________2007/08/20
3__________________U103_____________01XVB_____________2007/08/22
4__________________U104_____________01XVB_____________2007/08/28
5__________________U104_____________01XVB_____________
<クエリ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_____________99XVC_____________2007/08/10___2007/08/20___-1________01XVC
2__________________U102_____________99XVC_____________2007/08/20______________________0__________01XVC
3__________________U103_____________01XVB_____________2007/08/22______________________0__________99XVB
4__________________U104_____________01XVB_____________2007/08/28______________________0__________99XVB
5__________________U104_____________01XVB___________________________________________________-1________99XVB
相当に簡略化していますが、こういうことでしょうか?
[機械の現状]は、[待機中]でYes/Noにしています。
別に、'貸出中'、'待機中'と翻訳するのは手の問題だからよいでしょう。
さて、「製造番号毎に最新のレコードだけを表示させる」の意味が判りません。
並び順はさておいて、通常は、[待機中]が偽='貸出中'は全て表示する筈ですよね。
SELECT *
FROM クエリ1
WHERE 待機中=False;
貸出機管理ID__ユーザーNO___貸出機製造番号___貸出日__________返却日__________待機中__貸出機製造番号並び順
2__________________U102_____________99XVC_____________2007/08/20______________________0__________01XVC
3__________________U103_____________01XVB_____________2007/08/22______________________0__________99XVB
4__________________U104_____________01XVB_____________2007/08/28______________________0__________99XVB
どこが、どのように判らんのでしょうか?
No.2
- 回答日時:
仕様に矛盾があります
貸し出し簿から抽出しても
>まったく貸出したことがない機械もあります。
これは抽出できませんよ
貸し出し簿から分かるのは貸し出し中か貸し出したことがあるという情報だけです
>製造番号ごとに貸出中なのか待機中なのか一目でわかればいいのです。
これをやるには全貸出機のテーブルが必要です
その中から貸し出し中以外を抽出してやれば
待機中の一覧が得られますね
貸出機テーブルから作ったクエリの製造番号の抽出条件欄に
In (select 貸出機製造番号 from テーブルA where 返却日 is null)
No.3
- 回答日時:
前回とは別案の回答(質問が具体化されたので)
貸出機製造番号でグループ化し、貸出機管理IDの最大値を選ぶクエリを作ります。
(sqlは以下の通りです。 クエリの表示でSQLビューにして確認してください。)
このクエリを クエリAとします。
SELECT Max(テーブルA.貸出機管理ID) AS 貸出機管理IDの最大, テーブルA.貸出機製造番号
FROM テーブルA
GROUP BY テーブルA.貸出機製造番号;
このクエリとテーブルAを連結(left join)させたクエリを作ります。
sqlは以下の通りです。 出力フィールドや、ユーザー名等はご自分で調整なさってください。
SELECT テーブルA.貸出機管理ID, テーブルA.貸出機製造番号, テーブルA.貸出日, テーブルA.返却日
FROM クエリA LEFT JOIN テーブルA ON クエリA.貸出機管理IDの最大 = テーブルA.貸出機管理ID;
これでご希望のデータが取得できるはずです。
No2様のご指摘部分はその通りです。 テーブルに入っていないデータを抽出することはできません。
もうひとつ貸出機テーブルを作成する必要があります。
この回答への補足
ご回答ありがとうございます。
貸出機管理IDの最大値のみを抽出することが出来ました。
けれど、私が欲しいのやはり貸出を行ったことのない機械のレコードも含めてなのです。
元のテーブルにはそれらの製造番号はあります。
貸出機テーブルを作って、次にどのような作業を行えばいいと思われますか?
ご負担じゃなけば、もう少し教えてください。
フィールドの項目をたくさん指定して集計クエリを使っていたのが悪かったようです。教えていただいたように、必要な項目に絞り込んで、それをベースに数段階にわけてクエリを作ればよかったんですね。
手順が大切なこともわかりました。
ありがとうございました!
No.4
- 回答日時:
[イミディエイト]
? 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.5ベストアンサー
- 回答日時:
>元のテーブルにはそれらの製造番号はあります。
これを製造番号でグループ化したもの(すべての貸出機の製造番号をリストにしたもの)をテーブルBとします。(別にクエリBでもかまいません)そして2番目のクエリを以下のように作り変えてください。
SELECT テーブルB.貸出機製造番号, テーブルA.貸出機管理ID, テーブルA.貸出日, テーブルA.返却日
FROM テーブルB LEFT JOIN (クエリA LEFT JOIN テーブルA ON クエリA.貸出機管理IDの最大 = テーブルA.貸出機管理ID) ON テーブルB.貸出機製造番号 = クエリA.貸出機製造番号;
一回も貸し出したことがないものは貸出機管理ID以下がブランクで表示されるはずです。
テーブルのリレーションシップが理解できれば簡単な問題ですので、そのあたりを勉強されると良いと思います。
やっほー、できました!!
ようやく望みのものが出来ました。
作成手順、そしてテーブルのリレーションシップとクエリの結合の種類を理解していれば解決できた問題だったんですね。
2ヶ月近くも悩みつづけていたことから解き放されて、と~ってもうれしいです☆
これでようやくじっくりと他のことも勉強することができます。私にはまだまだ基本を学ぶことが大切なようなので、しっかり身に付けたいと思います。
何度も教えていただいて、ありがとうございました。
本当に助かりました。
今後ともよろしくお願いいたします!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(データベース) c言語の問題です。これを踏まえてコーディングしたいのでおしえていただきたいです。 3 2023/08/03 09:27
- Access(アクセス) Accessテーブルの結合で別々のテーブルのフィールドを組み合わせて値を出す方法について 2 2022/07/20 19:43
- その他(データベース) 更新クエリをリンクデータベーステーブルに実行し実行時エラー3362固有インデックスに重複する値が含ま 1 2022/09/21 11:44
- Oracle sqlで質問です。 Aテーブルの情報をBテーブルに更新かけたいです。 やりたいことは、Bテーブルの受 1 2023/05/17 11:17
- Oracle SQL update方法 2 2022/06/22 14:07
- Visual Basic(VBA) ExcelからAccessのテーブルに書き込む時に時間がかかる 1 2022/10/14 20:38
- Access(アクセス) Access IF文でテーブルに存在しない場合の処理について 2 2022/10/10 18:09
- Access(アクセス) アクセスの更新クエリでカレントレコードのみ更新したい 1 2022/06/02 23:32
- Wi-Fi・無線LAN PCWi-Fiの設定方法がわからなくて困っています。 4 2022/12/28 18:30
- Access(アクセス) ACSESS初心者です マンション管理をACCESSで出来ないかとチャレンジしています。 リレーショ 3 2022/10/08 11:45
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Accessでテーブル名やクエリ名...
-
ACCESSに同時アクセス(編集)を...
-
フォームの計算がテーブルに反...
-
ACCESS;フォーマットの...
-
アクセスで消し込みがしたい
-
Accessでvlookupみたいなことは...
-
ツリー構造をRDBで表現するには?
-
Accessの追加クエリで既存のテ...
-
ACCESSで同じテーブルに...
-
access テーブル内のレコード...
-
Accessクエリでの、LIKE条件
-
3つの表を1つに縦に連結する
-
時間の足し算
-
翌営業日までの日数をSQLで求め...
-
テーブル作成クエリで主キーを設定
-
アクセスのデータをエクセルに...
-
アクセスのテーブルをくっつけ...
-
SQL構文について教えてください
-
Accessでテーブルにパスワード...
-
MS Access からメールを送るには
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Accessでテーブル名やクエリ名...
-
ACCESSに同時アクセス(編集)を...
-
Accessでvlookupみたいなことは...
-
Accessでテーブルからテーブル...
-
Accessクエリでの、LIKE条件
-
Accessレコードの追加や変更が...
-
access テーブル内のレコード...
-
3つの表を1つに縦に連結する
-
SQLで日付を条件に削除したい
-
Accessの追加クエリで既存のテ...
-
SQLで条件指定結合をしたいがNU...
-
デザインビューで、連結式 を...
-
ツリー構造をRDBで表現するには?
-
ACCESSで指定されたテーブルか...
-
リンクテーブルを CopyObject ...
-
2つのテーブルを比較して一致し...
-
時間の足し算
-
ODBCで接続するとDBに変更/追加...
-
パススルークエリをテーブル作...
-
INSERT時にデータ登録とmaxの発...
おすすめ情報