hatena の Microsoft Access 掲示板



[7221] 1レコードの情報を複数に分け、その後計算する方法 Name:gjun Date:2017/07/24(月) 17:58 [ 返信 ]
テーブル、クエリは下記の通りになります。

テーブル1 主キーは製品番号
製品番号、品名、出荷日

テーブル2 主キーは製品番号
製品番号、詳細番号1、詳細番号1の重量、詳細番号2、詳細番号2の重量、詳細番号3、詳細番号3の重量

テーブル3
詳細番号、日付、入庫重量、出庫重量、入出庫区分、詳細区分

クエリ入庫・・・詳細番号、日付、入庫重量


クエリ入出庫・・・クエリ入庫、テーブル1の製品番号、品名、テーブル2の詳細番号1〜3の重量
詳細番号で結合しています

残り重量=入庫重量ー出荷重量の計算ができるようにしたいです。また表を作成したいので、理想のようにしたいです。

詳細番号1〜3の重量が1レコードに表示されているのを分けて表示できるようにしたいです

(例)
製品番号:1500
品名:あいうえお
詳細番号:a101,a102,a103

クエリ入出庫の例
詳細番号 入庫日 重量 製品番号 品名  1の重量、2の重量、3の重量
a101 7/1 1200 No1500 あいうえお 600,300,250
a102 7/2 1500 No1500 あいうえお 600,300,250
a103 7/3 1300 No1500 あいうえお 600,300,250

理想
詳細番号 入庫日 重量 製品番号 品名  重量 残
a101 7/1 1200 No1500 あいうえお 600 600
a102 7/2 1500 No1500 あいうえお 300 1200
a103 7/3 1300 No1500 あいうえお 250 1050


お手数ですがご教示お願いします。

[7227] RE:1レコードの情報を複数に分け、その後計算する方法 Name:hatena Date:2017/07/25(火) 13:40
まず最初に提案したいのは、
現状のテーブル設計の変更、つまり正規化です。

テーブル2 を下記のように変更します。

製品番号
詳細番号
重量

として、製品番号と詳細番号で複数フィールド主キーにします。

データ例
製品番号 詳細番号 重量
No1500  a101   600
No1500  a102   300
No1500  a102   250

こうするなら、テーブル3もテーブルに2にまとめていいですね。

テーブル2
製品番号
詳細番号
重量
日付
出荷重量

というように。こうすればクエリでテーブル1とテーブル2を結合するだけで理想の結果になります。

あと、テーブルの設計変更でできない事情があるなら、おすすめはしませんが、
クエリで疑似正規化する方法になります。

テーブル2より下記のようなユニオンクエリを作成します。

SELECT 製品番号, 詳細番号1 AS 詳細番号, 詳細番号1の重量 AS 重量
FROM テーブル2
UNION ALL
SELECT 製品番号, 詳細番号2 AS 詳細番号, 詳細番号2の重量 AS 重量
FROM テーブル2
UNION ALL
SELECT 製品番号, 詳細番号3 AS 詳細番号, 詳細番号3の重量 AS 重量
FROM テーブル2;

これで、上で提案した正規化したテーブルと同じ出力結果になりますので、
クエリで、それぞれのテーブルを結合すれば理想の結果になります。

[7228] RE:1レコードの情報を複数に分け、その後計算する方法 Name:gjun Date:2017/07/25(火) 17:46
ご返信ありがとうございます。

テーブルは改変不可のため難しいです。

別の方法でなんとか表示させることができましたが、重複したデータがあるため、合計重量がおかしくなってしまいます。

クエリの結果は以下になります。

例) 
詳細番号 入庫日 入庫重量 製品番号  重量
1     6/1   2600  No1500  600
1     6/1   2600  No1501  300
2     6/1   3000  
3     6/1   2500  No1501  600
3     6/1   2500  No1502  350


重複していたら一番上のフィールドのみ表示することは出来ますでしょうか。

理想は下記になります
詳細番号 入庫日 入庫重量 製品番号  重量
1     6/1   2600  No1500  600
              No1501  300
2     6/1   3000  
3     6/1   2500  No1501  600
              No1502  350

レポートで重複しているものを非表示できるようですが、
入庫日が同じ日なので全部消えてしまいます。詳細番号が異なる場合は表示してほしいのですが、そういうことは出来ますでしょうか。
可能ならばクエリでやれたらと思いますが、難しいでしょうか。

別のレポートで
詳細番号の種類別の合計を表紙として作成しようと思っているのですが、

詳細セッションにある入庫重量の重複表示しない設定にしても重複した数も加算された合計値が表示されてしまうためです。

お手数ですが、ご教示お願いします。

[7229] RE:1レコードの情報を複数に分け、その後計算する方法 Name:hatena Date:2017/07/26(水) 01:31
> 理想は下記になります
> 詳細番号 入庫日 入庫重量 製品番号  重量
> 1     6/1   2600  No1500  600
>               No1501  300
> 2     6/1   3000  
> 3     6/1   2500  No1501  600
>               No1502  350
>
> レポートで重複しているものを非表示できるようですが、
> 入庫日が同じ日なので全部消えてしまいます。詳細番号が異なる場合は表示してほしいのですが、そういうことは出来ますでしょうか。
> 可能ならばクエリでやれたらと思いますが、難しいでしょうか。

クエリでは難しいです。
レポートなら、
詳細番号でグループ化して、グループフッターを表示させれば、理想の表示になります。グループフッターが間にはいることで、重複データではなくなりますので。グループフッターの高さは0.002cmにします。


> 別のレポートで
> 詳細番号の種類別の合計を表紙として作成しようと思っているのですが、
>
> 詳細セッションにある入庫重量の重複表示しない設定にしても重複した数も加算された合計値が表示されてしまうためです。

テーブル構成が不明ですが、
詳細番号の種類別の合計を出力する集計クエリを作成しておいて、
それをレポートのレコードソースのクエリに追加すればいいかと。

[7230] RE:1レコードの情報を複数に分け、その後計算する方法 Name:gjun Date:2017/07/26(水) 15:40
ご返信ありがとうございます。

アドバイス通りにしましたがうまくいかないです

集計クエリを作成しましたが重複したものも加算されてしまいます。
レポートに関しては、詳細番号のほかに出庫、引当関連も重複してしまうところがあり、詳細番号と違って必ず1つではなく、2つ3つとあるので、グループ化にすることができないです。

テーブル、クエリは以下になります


集計クエリ
グループ化・・・種別番号のキー(数字)、種類(英語)
合計・・・前月重量、当月重量、出荷重量、残重量、当月残

重複した分も加算されたまま


レポートの元クエリ
種別番号のキー、種類、詳細番号、前月入庫日、前月重量、当月入庫日、当月重量、出荷日、出荷品名、出荷重量、残出庫、残重量の合計、引当番号、引当品名

テーブル1
製品番号、詳細番号、重量
テーブル2
詳細番号、種別番号のキー、種類、日付、重量、入出庫区分

テーブル3
製品番号、品名、出荷日

入庫クエリ
詳細番号、種別番号のキー、種類、日付、重量、入出庫区分

入庫_前月クエリ、当月クエリ
入庫_前月、当月クエリ

詳細番号残量出庫クエリ・・・グループ化し、残量の重量を合計(同じ日に出庫しているものがあるため)

入出庫クエリ・・・入庫_前月クエリ、当月クエリ、出荷済み、未出荷

入出庫管理クエリ・・・入出庫クエリ、詳細番号残量出庫クエリ

お手数おかけしますが、よろしくお願い致します。

[7231] RE:1レコードの情報を複数に分け、その後計算する方法 Name:hatena Date:2017/07/26(水) 16:40
なんか複雑ですね。

文章を読んで全体像を把握するはなかなか困難です。

どこにどのような集計を表示したいのかもさっぱりわかりません。

DSum関数で希望の集計結果を表示するようにすればどうでしょうか。

[7234] RE:1レコードの情報を複数に分け、その後計算する方法 Name:gjun Date:2017/07/26(水) 19:16
ご返信ありがとうございます。

結構複雑に作成しましたので、言葉を変えて簡易的に作成しました。

パスワード:keisan

レコードの数は少ないですが構造はほぼ一緒です。


入出庫管理クエリで1つの詳細番号に対して、複数の製品番号が割り当てられていると重複表示してしまいます。

フィールドの数が多く、複雑のため難しいでしょうか。

レポートの表は重複している部分は非表示にすればいいですが、肝心の合計重量が重複しているせいで、狂ってしまいます。

お手数ですが、よろしくお願いします。

[7235] RE:1レコードの情報を複数に分け、その後計算する方法 Name:gjun Date:2017/07/26(水) 20:02
添付したファイルで一部作成していないものがありましたので、追加で作成しました。

パスワードは同じになります。
よろしくお願いします。

pass:keisan

[7244] RE:1レコードの情報を複数に分け、その後計算する方法 Name:hatena MAIL Date:2017/07/28(金) 15:02
サンプルを見ましたが、
「重複データ非表示」のテキストボックスは一つもないですが、
どれの重複を排除しで集計したいのでしょうか。

そもそれ何をしたいレポートなのかもわからない。

[7246] RE:1レコードの情報を複数に分け、その後計算する方法 Name:gjun Date:2017/07/28(金) 19:04
ご返信ありがとうございます。

重複非表示の設定をしていなくてすみません。
重複したくないものは全項目ですが、エクセルのように未出荷名は表示させて、詳細番号は表示させないという方法はアクセスではできないことでしょうか。

サンプルのaccessデータで例えると

詳細番号、前月日、前月重量、出荷日、品名、重量
gh1512  17/6   2500    7/5 車 100
               7/6 鮭 300


理想としては詳細番号に対して同じ文字があれば表示しない形にできればと思います。
重複の非表示を設定すると、必要な情報も消えてしまうので、その機能は避けたいです。(同じ値だけど、表示したい場合もあるため)

利用目的は、材料の在庫管理をするためです。
どの詳細番号にどの製品が出荷したか、引き当てしたかを表で分かるようにしたいです。


そのため、正確な、前月重量、当月重量、出荷重量の値が知りたいのです。


今までエクセルで作成していたのですが、結構複雑な動きですので参考程度にアクセスのデータを見ながら、今まで通り、エクセルで管理するしかないでしょうか。


アクセスで色々な情報がある表を作るというのは向いていないのでしょうか。


  



無料レンタル掲示板・日記・ブログ Progoo