アクセスのクエリー作成

質問

月ごとの出庫データあり、いくら出庫したのでなく、例えば、1月、2月、3月出庫したら○、○、○というように表示をし、○が4つ連続で続いたら、その商品はリストアップする。といったようなクエリーの作成方法教えてください。

回答No.10

CHRONOS_0 · ベストアンサー

>4ヶ月連続クエリでテーブル作成したのをワークテーブルということですよね
そうです

>テーブル名というのは出庫テーブル？なのでしょうか？
そんな名前が付いてったっけ?大元のテーブルです

お礼

CHRONOS_0さん、本当に最初から丁寧なアドバイスありがとうございました。ようやく目的のものが出てきました。

回答No.9

CHRONOS_0

>この部分で、出庫テーブルには、厚生省コード, 薬品名, 日付以外のデータは
>ついていないほうがいいのでしょうか？
必要なのは厚生省コードだけなので他はなくてもかまいません
はずしても処理時間に差は出ないでしょうね

>4ヶ月連続クエリはかなりの時間がかかるようなかんじです。
非常に負荷のかかる処理です

>抽出条件欄になにもいれなければ、察するように、回数が1,3,4,とかいうように表示が数秒できますが、
ということならこのクエリをテーブル作成クエリに変えて
結果をワークテーブルとして固定してやればその後の処理が早くなるでしょうね

その場合は集計クエリを次のように変えます

TRANSFORM Sum(テーブル名.数量) AS 数量の合計
SELECT テーブル名.厚生省コード, テーブル名.薬品名
FROM テーブル名
WHERE (((テーブル名.厚生省コード) In (select 厚生省コード from ワークテーブル where 連続出庫回数="4")))
GROUP BY テーブル名.厚生省コード, テーブル名.薬品名
ORDER BY テーブル名.厚生省コード, DateSerial(Year([日付]),Month([日付]),1)
PIVOT DateSerial(Year([日付]),Month([日付]),1);

補足

＞結果をワークテーブル
というのは、4ヶ月連続クエリでテーブル作成したのをワークテーブルということですよね。集計クエリにでてくる、テーブル名というのは出庫テーブル？なのでしょうか？
すいません、ほとんど理解がおいついていかなくて。

回答No.8

CHRONOS_0

>クロス集計からそういうのを導くことができるということでしょうか？
できません
>全部目視でしていくのが大変なので、
ということだと
クロス集計から4ヶ月以上続く薬品だけを抽出という2段構えでの処理になりますね

一度薬品をグループに指定して月ごとの集計を行い
前回の回答のやり方で4ヶ月以上連続するもののみ抽出します

月ごとの集計
年月: DateSerial(Year([日付]),Month([日付]),1)
というフィールドを作りコード、薬品名、年月をグループに指定して
数値を集計します

SELECT 出庫テーブル.厚生省コード, 出庫テーブル.薬品名, DateSerial(Year([日付]),Month([日付]),1) AS 年月, Sum(出庫テーブル.数量) AS 数量の合計
FROM 出庫テーブル
GROUP BY 出庫テーブル.厚生省コード, 出庫テーブル.薬品名, DateSerial(Year([日付]),Month([日付]),1)
ORDER BY 出庫テーブル.厚生省コード, DateSerial(Year([日付]),Month([日付]),1);

このクエリから前回の解答のクエリを作ります

連続出庫回数: DCount("*","月集計クエリ","年月 between #" & DateAdd("m",-3,[年月]) & "# and #" & [年月] & "# and 薬品名='" & [薬品名] & "'")

このフィールドの抽出条件欄に
=4
で、4件以上連続している部分のあるものが抽出されます

このクエリを最初のテーブルから作ったクロス集計クエリの抽出条件に使用します

クロス集計クエリに直に条件を書くよりクロス集計からの抽出クエリに
条件を書くほうが処理が早いみたいです

SELECT クロス集計クエリ.*
FROM クロス集計クエリ
WHERE (((クロス集計クエリ.厚生省コード) In (SELECT 4ヶ月連続クエリ.厚生省コード
FROM 4ヶ月連続クエリ)));

補足

CHRONOS_0さん、(1)から丁寧にありがとうございました。
ちょっと補足で質問があります。
＞4件以上連続している部分のあるものが抽出されます。
この部分で、出庫テーブルには、厚生省コード, 薬品名, 日付以外のデータはついていないほうがいいのでしょうか？月集計クエリはある程度、数秒で表示しますが、4ヶ月連続クエリはかなりの時間がかかるようなかんじです。アクセスが固まってしまう感じ。PCはXPでペンチアム3Gの512MBなので、そんなに負担かかるとは思わないのですが。。。
抽出条件欄になにもいれなければ、察するように、回数が1,3,4,とかいうように表示が数秒できますが、=4をいれるとかなりの時間がかかる感じです。（かなりといってもどれくらいかわかりません、固まるかんじなので、強制で、アクセス落としています）

回答No.7

CHRONOS_0

>できればどの期間が、4ヶ月以上でているか表示できれば。と思っています。
こういうことがしたいということなら
単にクロス集計するだけで十分なのでは

厚生省コード 薬品名 　　　　　　　　　　　　　　　　　　1月　2月　3月　4月　5月　6月　7月
2149032F1021 アーチスト錠１０ｍｇ 　　　　　　　　　　　168　168　77　273.5　42
2344002X1276 「重質」カマグＧ「ヒシヤマ」　　　　　7　392　466　105
2399710E1020 ＳＰトローチ明治　 　　　　　　　　　　　　　88
2649843S1039 ＭＳ温シップ「タイホウ」　　　　　　　　　　　　　　　　　　　　　　　　　　1500
2652701M2021 １０％サリチル酸ワセリン軟膏東豊　　 10
3122007F2039 ２５ｍｇアリナミンＦ糖衣錠　　　　　　　　　　　　　　　　　　　　　　　　　112

補足

クロス集計だと、たくさんの薬品がある場合、全部目視でしていくのが大変なので、該当薬品を、リストアップしたいのです。
例えば、1月から4月の薬品もあれば3月から6月の薬品もあるので。

クロス集計からそういうのを導くことができるということでしょうか？

回答No.6

CHRONOS_0

>うまく伝わりますでしょうか？
やりたいことはわかりましたが最初の質問とはずいぶんとかけ離れていますね
質問したときにはどうしたいかが決まっていなかったのでは・・・

>クロス集計をかけて、月ごとのテーブルを作ってからしないといけないものなのでしょうか？
月ごとの集計結果からでなきゃ連続しているかどうかは分からないですね
それに、クロス集計クエリでは連続は検出できません
普通の集計クエリを作ってからということになりますね

>連続で数量としてでれば、○○○○と表示して、
連続したものだけを抽出するのであれば
>○○○○
こんな表示は要らないのでは(全てに付くことになるだけですよ)

>酸化マグネシウムが
酸化マグネシウムって何?
今ままでの説明にそんなものでてきていませんよ

あと、テーブルにはすべての月が含まれているのでしょうか
（抜けはないの?)

このまま進めても、また追加でああしたい、こうしたいが出てきそうですね
サンプルのテーブルと欲しい結果をきちんとアップしていただけますか

補足

CHRONOS_0さんの指摘されたのはそのとおりかもしれません。
○という表示はいらないのかもしれません。
クロス集計で、数字がたくさんあるのをパッと見わかりやすくしようとおもってそう思ったのだろうと思います。
酸化マグネシウムは「重質」カマグＧのことでした、すみません略してしまいました。

★欲しい結果は、薬品が4ヶ月以上連続ででていればリストアップして、できればどの期間が、4ヶ月以上でているか表示できれば。と思っています。データとしては2007年1月からとっているので、まだ1年たってはいませんが、1年あったほうがういいのならば、去年のデータを引っ張ることも可能です。

テーブルをここに書くのはどの程度書けばいいのかわからなかったので
鯖にアップしてみました。
http://9nainai9.purplenova.net/b900kf0hlvtfa2li/Book1.xls

回答No.5

CHRONOS_0

やっとテーぶるが出て来ましたね
最初からこれを出していればもっと話は早く進んだのですよ

で、これがどうなれば○が4つ続いたことになるのですか?
人にものを頼もうと思ったら、どうして欲しいのかを相手にわかる様に伝えなきゃ

補足

クロス集計をかけて、月ごとのテーブルを作ってからしないといけないものなのでしょうか？

今のテーブルのままで、
酸化マグネシウムが1月、2月、3月、4月と連続で数量として
でれば、○○○○と表示して、リストアップしたいのです。

イメージとしては　横に月、縦に薬品名のような
表みたいな感じであらわしたいのですが。
うまく伝わりますでしょうか？

回答No.4

CHRONOS_0

>○は記号です。数量が1以上あれば記号の○という風に表示したいのです。
???
最初の質問は
>○が4つ連続で続いたら、その商品はリストアップする。
でしたよ、変わったのですか

1以上が4つ連続ということなら
連続出庫回数: DCount("*","数量","日付 between #" & DateAdd("m",-3,[日付]) & "# and #" & [日付] & "# and 数量>0" & " and 薬品名称='" & [薬品名称] & "'")

補足

CHRONOS_0さん、再度のレスありがとうございます。
○が4つ連続で続いたら、その商品はリストアップするそれで
あっています。記載方法が誤解されたらごめんなさい。
抽出条件に＝4をいれて
いろいろ考えてやってみたのですが、どうもうまくいきません
数量のクエリまたはテーブルがないという感じのメッセがきます。

データーとしては 日付,薬品名称,数量
2007/04/16,「重質」カマグＧ「ヒシヤマ」,21
2007/05/28,「重質」カマグＧ「ヒシヤマ」,28
2007/06/25,「重質」カマグＧ「ヒシヤマ」,28
2007/06/25,「重質」カマグＧ「ヒシヤマ」,14
2007/7/25 「重質」カマグＧ「ヒシヤマ」,21
2007/8/11 「重質」カマグＧ「ヒシヤマ」,21
2007/9/11 「重質」カマグＧ「ヒシヤマ」,14
....といったかんじなのですが。。。どこがいけないんでしょうか？

回答No.3

CHRONOS_0

>テーブルデータとしては、日付（1/1,1/2,,,）薬品名称（△△△,,,,)
数量(7,12,10,,,）です

>○が4つ連続で続いたら、
の○は数量なんでしょうか?

>日付（1/1,1/2,,,）
のデータ型は？

補足

○は記号です。数量が1以上あれば記号の○という風に表示したいのです。
日付の方はテキスト型です。

回答No.2

CHRONOS_0

元のテーブルの情報がないので回答しにくいのですが
仮に
[出庫]
月　商品　出庫

のようなものだとして

連続出庫回数:DCount("*","出庫","月 between #" & DateAdd("m",-3,[月]) & "# and #" & [月] & "# and 出庫='" & "○" & "' and 商品='" & [商品] & "'")

このフィールドの抽出条件欄に

=4

お礼

CHRONOS_0さん、回答ありがとうございました。
テーブルデータとしては、日付（1/1,1/2,,,）薬品名称（△△△,,,,)
数量(7,12,10,,,）ですので、連続出庫回数: DCount("*","数量","日付 between #" & DateAdd("m",-3,[日付]) & "# and #" & [日付] & "# and 数量='" & "○" & "' and 薬品名称='" & [薬品名称] & "'")としてみたのですが、、、まずいとこありますか？
構文がまだいまいち理解できてなくて、、すみません。

回答No.1

noname#140971

ややこしいクエリなど作成しようと思わないことです。
私は、プログラマではなく一介のデザイナですので、どうも、難しいやり方は好みません。
私ならば、出荷状況を調べて一時テーブルにでもリストアップします。
が、これでは回答になりませんので・・・。

<出庫履歴>
ID___日付___________商品マスター_ID__数量
1____2007/01/01__1_____________________10.1
2____2007/01/01__1_____________________10.1
3____2007/03/03__1_____________________10.1
4____2007/04/04__2_____________________20.1
5____2007/05/05__2_____________________20.1

[イミディエイトウインドウ]
? XferCircle("SELECT MONTH(日付) FROM 出庫履歴 WHERE 商品マスター_ID=1 GROUP BY MONTH(日付)")
○×○
? XferCircle("SELECT MONTH(日付) FROM 出庫履歴 WHERE 商品マスター_ID=2 GROUP BY MONTH(日付)")
○○

このように、関数を利用して、出庫履歴を○×とに翻訳することはさほど難しいことではないです。
また、次のように　Access のクエリには関数も組み込めるので出荷状況も○×で表示することも可能。

[クエリ１]
ID____日付 ___________商品マスター_ID___数量__出荷状況
1_____2007/01/01___1______________________10.1___○×○
2_____2007/01/01___1______________________10.1___○×○
3_____2007/03/03___1______________________10.1___○×○
4_____2007/04/04___2______________________20.1___○○
5_____2007/05/05___2______________________20.1___○○

このクエリを[SQL ビュー]で表示すると次のようです。

SELECT 出庫履歴.ID, 出庫履歴.日付, 出庫履歴.商品マスター_ID, 出庫履歴.数量,
XferCircle("SELECT MONTH(日付) FROM 出庫履歴 WHERE 商品マスター_ID=" & [商品マスター_ID] & " GROUP BY MONTH(日付)") AS 出荷状況, *
FROM 出庫履歴;

Public Function XferCircle(ByVal strQuerySQL As String) As String
On Error GoTo Err_XferCircle
　　Dim I　　　　As Integer
　　Dim J　　　　As Integer
　　Dim K　　　　As Integer
　　Dim R　　　　As Integer
　　Dim M　　　　As Integer
　　Dim N　　　　As Integer
　　Dim rst　　　As ADODB.Recordset
　　Dim fld　　　As ADODB.Field
　　Dim Datas　　As String
　　Dim nowMonth As Integer
　　Dim newMonth As Integer

　　Set rst = New ADODB.Recordset
　　' =================
　　'　Begin With: rst
　　' -----------------
　　With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　M = .RecordCount - 1
　　　　　　N = .Fields.Count - 1
　　　　　　If N = 0 Then
　　　　　　　　.MoveFirst
　　　　　　　　For R = 0 To M
　　　　　　　　　　newMonth = .Fields(0)
　　　　　　　　　　K = IIf(nowMonth <> 0, newMonth - nowMonth - 1, 0)
　　　　　　　　　　Datas = Datas & String$(K, "×") & "○"
　　　　　　　　　　nowMonth = newMonth
　　　　　　　　　　.MoveNext
　　　　　　　　Next R
　　　　　　Else
　　　　　　　　Datas = ""
　　　　　　End If
　　　　 End If
　　End With
　　' ---------------
　　'　End With: rst
　　' ===============
Exit_XferCircle:
　　XferCircle = Datas
　　Exit Function
Err_XferCircle:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(XferCircle)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_XferCircle
End Function

なお、日付の範囲指定の問題が残されているかと思います。

補足

Husky2007さん、丁寧な回答ありがとうございました。
まだ自分のレベル的にそこまで達していないのでまだ未知なのですが、
少しづつ、理解できるようにがんばってみます。