パソコンから恋愛まで、みんなの知識、みんなで教えて!

OKWave

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

ACCESS2007最大値を得て別項目更新

ACCESS2007を使っています。
・顧客マスタ
・売上伝票(ある日、特定顧客に何らかの商品が売れた)
・売上明細(上記売上伝票の詳細。商品名、個数など)
(他に商品マスタなど)
で構成したものがあります。

ここで、「機械のメンテナンス実施」という商品があります。それが売り上がると、「売上明細」のメンテ日というフィールドにその日付が入るように設定しました。(更新クエリで)
◆ここからが今の私の課題です。
その日付は機械のメンテナンス実施ごとに入っているわけですが、その最大値(最終日)を、拾ってきて、顧客マスタのフィールドの「最終メンテ日」というフィールドに入れたいと思います。
これは、どうすればよいでしょうか。
(なお、それ以降は、その最終メンテ日から1年経った顧客に連絡するなどの部分を作る予定です)

・選択クエリ「集計」で、顧客IDで「グループ化」し、メンテ日で「最大値」を選択すれば、メンテ最終日は出ますが、その値を上記フィールドに入れる(更新していく)には、どうすればよいのでしょうか。あるいは全く違った方法があるのでしょうか?
よろしくお願いいたします。

投稿日時 - 2018-01-13 09:59:31

QNo.9417869

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(13)

ANo.13

>コードをいれる場合、どこのどの部分に入れるのでしょうか?
コーディング画面(VBE画面)に入るにはデザインモードから入りますが
1.[デザインリボン]→[コードの表示]
2.[ボックス]→[プロパティー]→[イベントタグ]→[イベントの選択]
の2通りありますが、今回は1.で入って下さい。

VBEについて少し

VBEの上端2つのプルダウン窓があります
 左が配置されたボックス、フォーム等
 右が左の配置物の動作(イベント)
VBAは
 Private sub ボックス名 イベント()
  金額=数量*単価
 END SUB
と動作名の無い
 Private sub 処理名()
  処理
 END SUB

投稿日時 - 2018-02-03 23:14:50

ANo.12

随分違った方向へ来てしまった気がしいて、最初から見直しました。

・顧客マスタ
・売上伝票(ある日、特定顧客に何らかの商品が売れた)
・売上明細(上記売上伝票の詳細。商品名、個数など)
(他に商品マスタなど)
で構成したものがあります。

ここで、「機械のメンテナンス実施」という商品があります。それが売り上がると、「売上明細」のメンテ日というフィールドにその日付が入るように設定しました。
-------------------------------------------------------------------------
以下のコードを実行すれば顧客マスタの[最終メンテ日]が更新されます。

Private Sub 最終メンテ日更新_Click()
DoCmd.RunSQL ("UPDATE 顧客マスタ INNER JOIN (売上伝票 INNER JOIN 売上明細 " _
& " ON 売上伝票.ID = 売上明細.売上伝票ID) ON 顧客マスタ.ID = 売上伝票.顧客ID set 最終メンテ日 = メンテ日 " _
& " WHERE ( 最終メンテ日 < メンテ日) and (((メンテ日) Is Not Null)) ;")
End Sub

このコードを必要な処理の前に組み込むと良いでしょう
--------------
Private Sub 最終メンテ日更新RTN()
DoCmd.RunSQL ("UPDATE 顧客マスタ INNER JOIN (売上伝票 INNER JOIN 売上明細 " _
& " ON 売上伝票.ID = 売上明細.売上伝票ID) ON 顧客マスタ.ID = 売上伝票.顧客ID set 最終メンテ日 = メンテ日 " _
& " WHERE ( 最終メンテ日 < メンテ日) and (((メンテ日) Is Not Null)) ;")
End Sub
----------------
Private Sub 最終メンテ日降順表_Click()
最終メンテ日更新RTN
DoCmd.OpenReport "顧客マスタ(最終メンテ日降順)", acViewPreview
End Sub
--------------
この処理は過去のデータにも通用します。
テーブルも少し変わりました、添付画像確認してください

投稿日時 - 2018-01-28 17:19:44

お礼

ありがとうございます。コードをいれる場合、どこのどの部分に入れるのでしょうか?基本的なところが分かっていないで、一応、教えていただけるとありがたいです。

投稿日時 - 2018-02-03 16:15:55

ANo.11

>・売上伝票のテーブルに売上金額を作るのは理想的だとは思いましたが、そのようにすることもできるのですね。売上明細での計算
その通りです。
売上伝票登録の過程で売上金額、メンテ最終日を更新します。
売上登録(添付図)で
黄色の部分が主フォーム(売上伝票)無地の部分がサブフォーム(売上明細)です。
売上明細の金額が1行確定の都度合計金額を更新します。
他のフォームの項目はForms![フォーム名]!項目名 としますが
サブフォームから主フォームの時はParentを使います。

Private Sub Form_DblClick(Cancel As Integer)
If 売上fg = True Then
If Parent!最終メンテ日 < Parent!日付 Then
Parent!最終メンテ日 = Parent!日付
End If
End If
End Sub
----------------------------------------------------------------------
売上金額の更新は重複していますが
Private Sub 合計_AfterUpdate()
Parent!売上金額 = 合計
End Sub

Private Sub 合計_Click()
Parent!売上金額 = 合計
End Sub

Private Sub 明細金額_Exit(Cancel As Integer)
Parent!売上金額 = 合計
End Sub

黄色の部分が主フォーム(売上伝票)無地の部分がサブフォーム(売上明細)です。

投稿日時 - 2018-01-23 15:03:51

ANo.10

前から気になっていたことですが、主キーはIDはオートナンバー型にして下さい。
こうすることで、主キーの管理はCPにお任せになり、主キーの運用管理から解放されます。試しにウィザード作成したフォームのIDを削除してもシステムに不都合は起こりません。

投稿日時 - 2018-01-21 00:32:29

ANo.9

自分はファイルを開いたとき最初に開くフォームをメニューと名付けて作成しています。
日付関連のボックスが沢山ありますね、左上の塊が基準日で規定値はDATE()
エクセルのTODAY()です。マウス操作で日付を変えるためのボタン群でこれと連動して、月初日等計算しています。
ファイルを開いたときに開くフォームを定義するには
「ファイル」→「オプション」→「現代のデータベース」→【フォームの表示のプルダウンから一つを選ぶ]→「OK」以上です。
お付き合い、ありがとうございました。

投稿日時 - 2018-01-21 00:08:46

ANo.8

「最終メンテ日」を顧客マスタに置くのは納得出来ません。
売上げた物件(売上明細)に対してにメンテ履歴を残すべきではないでしょうか

投稿日時 - 2018-01-20 23:28:17

お礼

メンテ日1ヶ月前にご案内をするシステムが作れれば、どのような形でもよいです

投稿日時 - 2018-01-22 22:30:25

ANo.7

お待たせしました

>その日付は機械のメンテナンス実施ごとに入っているわけですが、その最大値(最終日)を、拾ってきて、顧客マスタのフィールドの「最終メンテ日」というフィールドに入れたいと思います。
これは、どうすればよいでしょうか。

売上伝票と売上明細レコードソースのテーブルはリレーションシップで繋がっていますね
また売上伝票と顧客マスタもリレーションシップは確立していますね
売上伝票登録フォーム(売上明細登録フォームがサブ)に組み込む。
売上伝票登録フォームの中に顧客マスタもレコードソースにする
少し面倒なのはサブフォームにある商品により更新するかどうかが決まる。
このため商品マスタに分類フィールド(一般商品、要保守商品、保守、点検)を追加し
商品の分類が "保守” 時に最終メンテ日を更新する

Private Sub 商品ID_Exit(Cancel As Integer)
If 分類 = "保守" Then
If Parent!最終メンテ日 < Parent!日付 Then
Parent!最終メンテ日 = Parent!日付
End If
End If
End Sub

★他のフォームのボックスを参照するにはForms!他のフォーム名!ボックス名とするが
 サブフォームからはParent!ボックス名とする。
 主フォームからサブフォームは参照出来ない

クエリでもワイルドマーク(*)使えます。抽出条件設定の為フィールドに入れるとワイルおマークと重複するので、表示行のチェックを外す。

投稿日時 - 2018-01-20 22:59:58

ANo.6

「売上明細」のメンテ日というフィールドにその日付が入るように設定しました。(更新クエリで)
◆ここからが今の私の課題です。
その日付は機械のメンテナンス実施ごとに入っているわけですが、その最大値(最終日)を、拾ってきて、顧客マスタのフィールドの「最終メンテ日」
------------------------------------------------------------------------------------------------------------
確認ください
・売上伝票の登録フォームのレコードソースは売上伝票
・売上明細の登録フォームのレコードソースは売上明細
・売上伝票と売上明細のテーブルはリレーションで繋がっている(1:∞)
これを次のように、「メンテ日」を入力した都度「最終メンテ日」更新する
if [Parent]![最終メンテ日]<[メンテ日] then [Parent]![最終メンテ日]=[メンテ日]
・売上伝票の登録フォームのレコードソースは売上伝票と顧客マスタ
・売上明細の登録フォームのレコードソースは売上明細
・売上伝票と売上明細のテーブルはリレーションで繋がっている(1:∞)

投稿日時 - 2018-01-14 11:15:50

ANo.5

いかがですか
最終メンテ日の更新は次で解決します。
最終メンテ日更新のラベルボックスをフォームに配置して、クリックイベントで起動して下さい

Private Sub 最終メンテ日更新_Click()
DoCmd.RunSQL ("update 顧客マスタ set 最終メンテ日 = (date() - 365 * 10) where 最終メンテ日 is null;")
DoCmd.RunSQL ("update 顧客マスタ INNER JOIN 売上伝票 ON 顧客マスタ.ID = 売上伝票.顧客ID set 最終メンテ日=日付 WHERE (最終メンテ日<[日付]);")
End Sub

Docmd文は参考URLをご覧ください

参考URL:http://www.geocities.jp/cbc_vbnet/kisuhen/docmd.html#sul

投稿日時 - 2018-01-14 03:57:52

ANo.4

1-2.顧客マスタの最終メンテ日の初期設定<
 ※最終メンテ日は新規に追加した項目なので比較等でエラーになります

 ・作成リボン
 ・フォームデザイン
 ・データタグのレコードソースの右端クりック
 ・顧客マスタからテーブルの表示を閉じる
 ・顧客名をフィールドへドラッグ
 ・最終メンテ日をフィールドへドラッグ、抽出条件に「is null」
 ・クエリを閉じる

 ・プロパティの
   データタグの追加の許可 いいえ
   データタグの削除の許可 いいえ
 ・デザインリボンの既存のフィールドの追加より顧客名と最終メンテ日を明細欄にドラッグ
 ・明細欄の4つのボックスを囲んでアクティブにしてから配置リボンの表形式
 ・ホームヘッダ,詳細、フォームフッタの幅を小さく
 ・プロパティの書式タグの既定のビューで帳表フォーム
 ・プロパティの最終メンテ日のフォーカス取得イベントで

   Private Sub 最終メンテ日_GotFocus()
    最終メンテ日 = Date - 10 * 365 '約10年前に設定
   End Sub

 ・プロパティのその他タグの
   最終メンテ日のタブストップに「はい」
   その他のフィールドのタブストップに「いいえ」

このフォームを開き、エンタキーを押し続けると最終メンテ日が10年前に更新される

投稿日時 - 2018-01-13 23:32:32

ANo.3

>あるいは全く違った方法があるのでしょうか?

違った流れを提案させていただきます。
画像を複数個添付したいので3回に分けて説明させて頂きます

1.テーブルの見直し
2.顧客マスタの初期更新
 顧客マスタに追加した「最終メンテ日」を約10年前に更新する
3.顧客マスタの「最終メンテ日」を売上伝票の日付により更新する

1-1.テーブルの確認
★そちらのテーブルに以下の項目が抜けていればフィールドを追加してください

・顧客マスタ
 ID(主キー オートナンバー)
 顧客名
 住所
 最終メンテ日(日付型)

・商品マスタ
 ID(主キー オートナンバー)
 商品名

・売上伝票
 ID(主キー オートナンバー)
 日付(日付型 規定値 DATE())
 顧客ID (数値型 規定値 0 顧客マスタをルックアップ)
 売上金額(数値型 規定値 0)

・売上明細
 ID(主キー オートナンバー)
 売上伝票ID(数値型)
 商品ID(数値型 商品マスタをルックアップ)
 数量(数値型 規定値 0)
 明細金額 (数値型 規定値 0)
 メンテFg(yes/No 型)

※このシステムに限らず、
テーブルとリレーションシップはAccessの入り口です。
ここをきっちり抑えて下さい
「主キー、規定値、インデックス(重複の有無)ルックアップ
 、リレーションシップ
添付はどのテーブルも1:∞で結ばれていますね!これがポイントです。

投稿日時 - 2018-01-13 20:09:05

お礼

リアルに作っていただき誠にありがとうございます。
大体はそのようになっています。
・売上伝票のテーブルに売上金額を作るのは理想的だとは思いましたが、そのようにすることもできるのですね。売上明細での計算から、更新してテーブルに入れたりするのでしょうか(ここでの本題ではないのですが、単に興味で)

投稿日時 - 2018-01-22 22:15:04

ANo.2

最大値がメンテ途中の場合、どこで判断するのですか?
それとも、1機械1メンテなのでしょうか。

投稿日時 - 2018-01-13 13:44:08

補足

1機械1メンテ、です。

投稿日時 - 2018-01-15 07:49:32

お礼

量も大量ではなく、ほとんど途中のケースはないので、個別に識別でき、問題ないです。

投稿日時 - 2018-01-15 07:49:01

ANo.1

Q、あるいは全く違った方法があるのでしょうか?
A、幾つかあると思います。

1、そもそもフィールド[最終メンテ日]なんてのは用意しない。
2、仮に用意する場合には、SQL文実行関数にて参照する。
(例)DBLookup(SQL文)
 ADOを利用してSQL文の実行結果を返す関数を用意しておけば、フィールドの定義に上述のように書くだけ済みます。しかし、そもそもフィールド[最終メンテ日]を用意する必要があるかどうか疑問です。

投稿日時 - 2018-01-13 12:53:46

お礼

ADOについてはほとんど分かりませんが、DLookup(列名,レコードセット名[,条件])を使うのですね。ありがとうございます。
あとMaxの関数とか、顧客IDごとのグループ化とかからむと思いますが、これから勉強して検討します。SQLもまだよくわからないので。参考にさせていただきます。

投稿日時 - 2018-01-13 14:00:29